Repeating the same bind variable multiple times when using the OPEN...FOR dynamic SQL structure in Oracle PL/SQL

user_6853810 0
Viewed 2136

This is a follow on question to Vincent Malgrat's answer to this question. I can't find the correct syntax to use when you need to use the same bind variable multiple times when using OPEN...FOR dynamic SQL. You can see the syntax for EXECUTE IMMEDIATE here (see "Using Duplicate Placeholders with Dynamic SQL") … but not for OPEN...FOR. Does the syntax differ with duplicate placeholders when using OPEN...FOR? I'm using Oracle 12c. This is in a PL/SQL package not an anonymous block.

For example, this example from Oracle's own documentation works fine:

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   emp_cv   EmpCurTyp;
   emp_rec  emp%ROWTYPE;
   sql_stmt VARCHAR2(200);
   my_job   VARCHAR2(15) := 'CLERK';
BEGIN
   sql_stmt := 'SELECT * FROM emp WHERE job = :j';
   OPEN emp_cv FOR sql_stmt USING my_job;
   LOOP
      FETCH emp_cv INTO emp_rec;
      EXIT WHEN emp_cv%NOTFOUND;
      -- process record
   END LOOP;
   CLOSE emp_cv;
END;
/

But if you need to reference the :j bind variable more than once, how do you do it in a case like this where :j is referenced twice?

sql_stmt := 'SELECT * FROM emp WHERE (job = :j AND name = :n) OR (job = :j AND age = :a)' ;

I have tried

OPEN emp_cv FOR sql_stmt USING my_job, my_name, my_age;

and 

OPEN emp_cv FOR sql_stmt USING my_job, my_name, my_age, my_job;

and in both cases it gives this error:

ORA-01008: not all variables bound
dynamic-sql plsql oracle
3 Answers

You need to include the parameter twice in the USING clause:

 OPEN emp_cv FOR sql_stmt USING my_job, my_job;

Here's your example, but simplified:

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   emp_cv   EmpCurTyp;
   emp_rec  varchar2(10);
   sql_stmt VARCHAR2(200);
   my_job   VARCHAR2(15) := 'X';
BEGIN

   OPEN emp_cv FOR 'select * from dual where dummy = :j or dummy = :j' 
    USING my_job, my_job;
   LOOP
      FETCH emp_cv INTO emp_rec;
      EXIT WHEN emp_cv%NOTFOUND;
   END LOOP;
   CLOSE emp_cv;
END;
user_17455440
user_7453420

We can try this instead of passing param_val N times the parameter is required in the query For eg: I will modify the query like this

sql_stmt := 'SELECT * FROM emp, (select :j j_type from dual) temp WHERE (job = temp.j_type AND name = :n) OR (job = temp.j_type AND age = :a)';

And pass my_job value only once as shown below:

OPEN emp_cv FOR sql_stmt USING my_job;

In this way we could pass all such parameters in a single select query joined to the main table. For N parameters, this is how the query looks like:

(select :param1 param1, :param2 param2, ....., :paramN paramN  from dual) temp

OPEN cursor FOR sql_stmt USING param1,param2, ...., paramN;
user_39708950
Related