Dynamic sql with bind variable

Question

/
create or replace procedure search_proc(p_string varchar2,p_table varchar2,p_col varchar2,search_result OUT sys_refcursor)
is
SQL_QRY VARCHAR2(2000);
BEGIN
SQL_QRY:='SELECT EMPNO,:1 FROM :2';
--DBMS_OUTPUT.PUT_LINE('SQL:'||SQL_QRY);
OPEN SEARCH_RESULT FOR SQL_QRY  USING p_col,p_table;
END;

/

VARIABLE REFC REFCURSOR;
EXEC SEARCH_PROC('TEST','EMP','ENAME',:REFC);
PRINT REFC;

/

I am trying to return empno and employee name using a procedure which contains dynamically built SQL query .The query is built using bind variables.but getting the following error.May be something is wrong with the way i am calling the procedure ORA-06512: at line 1 00903. 00000 - "invalid table name"

Answer 1

You can't use bind variables to take the place of identifiers, such as table names or column names. Those things must be known at the time the statement is parsed, which occurs before bind variables are bound to values. (Part of the whole purpose of using bind variables is to be able to parse a statement once then execute it with variable values.)

In this case, the solution is simple, since you are already putting the query string into a variable.

BEGIN
SQL_QRY:='SELECT EMPNO,' || p_col || ' FROM ' || p_table;
--DBMS_OUTPUT.PUT_LINE('SQL:'||SQL_QRY);
OPEN SEARCH_RESULT FOR SQL_QRY;