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

Question

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

Olethea

Answer 1

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;

Lampedusa

Only when using an anonymous block repeated placeholder are possible: docs.oracle.com/database/121/LNPLS/dynamic.htm#LNPLS01108 – Stressful

Thanks for the answer. I have updated my question as it wasn't clear that I am not in an anonymous block, but rather in a PL/SQL package. – Olethea

Also, I did actually try that and it gives me ORA-01008: not all variables bound – Olethea

@JDor: I copy/pasted this example on livesql.oracle.com and I didn't get an ORA-01008. Either as an anonymous block or inside a procedure (db version 19c) – Shaynashayne

I think as usual I over-complicated things here. My own actual code was far too complex to use in the question, as it builds up a huge SQL string. After reading your comment, I stripped my own code down to the bare minimum, and it worked as you say by repeating the binds. When I added back in the full code, the problem returned. I think it may be a combination of comments in the code and the way I'm building the string. Bizarre. See other people with similar problems in the answers to this question #7493528 – Olethea

Answer 2

Repeated placeholders topic is well described here https://docs.oracle.com/database/121/LNPLS/dynamic.htm#LNPLS631 . In a word, in USING clause parameters are treated by positions, so you can use variable several times in a query but for each occurrence a separate value must be provided in USING statement.

Searchlight

Thanks but as I said in my question, that documentation has the syntax for EXECUTE IMMEDIATE but not for OPEN...FOR, which appears to be different – Olethea

I did actually try that and it gives me ORA-01008: not all variables bound – Olethea

@JDor could you please reproduce that behavior in Oracle Live Sql? because i can’t – Searchlight

Thanks. I actually can't reproduce the problem as my actual code is an enormous legacy procedure that conditionally concatenates a huge string containing the dynamic SQL.I think have fallen victim to the "comments" issue described by people here #7493528 – Olethea

Answer 3

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;

Television