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

OldProgrammer · Accepted Answer · 2020-03-04 16:40:58Z

3

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;

answered Mar 4, 2020 at 16:40

OldProgrammer

12.2k4 gold badges27 silver badges52 bronze badges

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

5 Comments

Thomas Strub Over a year ago

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

J Dor Over a year ago

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.

J Dor Over a year ago

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

Vincent Malgrat Over a year ago

@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)

J Dor Over a year ago

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 stackoverflow.com/questions/7493028/…

Ilia Maskov · Accepted Answer · 2020-03-04 16:44:46Z

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.

answered Mar 4, 2020 at 16:44

Ilia Maskov

1,91816 silver badges27 bronze badges

4 Comments

J Dor Over a year ago

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

J Dor Over a year ago

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

Ilia Maskov Over a year ago

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

J Dor Over a year ago

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 stackoverflow.com/questions/7493028/…

Bhargav ram · Accepted Answer · 2022-03-16 15:34:57Z

0

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;

answered Mar 16, 2022 at 15:34

Bhargav ram

312 silver badges6 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

5 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related