CREATE OR REPLACE PROCEDURE select_item (

t_column VARCHAR2,

t_name VARCHAR2

) AUTHID DEFINER

IS

temp VARCHAR2(30);

BEGIN

temp := t_column; -- For error message if next SELECT fails

-- Fails if table t_name does not have column t_column:

SELECT COLUMN_NAME INTO temp

FROM USER_TAB_COLS

WHERE TABLE_NAME = UPPER(t_name)

AND COLUMN_NAME = UPPER(t_column);

temp := t_name; -- For error message if next SELECT fails

-- Fails if there is no table named t_name:

SELECT OBJECT_NAME INTO temp

FROM USER_OBJECTS

WHERE OBJECT_NAME = UPPER(t_name)

AND OBJECT_TYPE = 'TABLE';

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE ('No Data found for SELECT on ' || temp);

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE ('Unexpected error');

RAISE;

END;

/

Invoke procedure (there is a DEPARTMENTS table, but it does not have a LAST_NAME column):

BEGIN

select_item('departments', 'last_name');

END;

/

Result:

No Data found for SELECT on departments

Invoke procedure (there is no EMP table):

BEGIN

select_item('emp', 'last_name');

END;

/

Result:

No Data found for SELECT on emp