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"

Dave Costa · Accepted Answer · 2014-11-20 18:20:08Z

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;

answered Nov 20, 2014 at 18:20

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic sql with bind variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related