OraFAQ Forum: SQL & PL/SQL » PLSQL - providing values to Bind variables appearing multiple times in USING clause

Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL - providing values to Bind variables appearing multiple times in USING clause (Oracle 11g, Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PLSQL - providing values to Bind variables appearing multiple times in USING clause [message #642289]

Sun, 06 September 2015 20:18

danand77
Messages: 2
Registered: September 2015

Junior Member

My Oracle database version is 11g.

I have defined a SYS_REFCURSOR with multiple bind variables and the same bind variable is accessed multiple times in the dynamic query which is stored into a variable named l_query.

My issue is, Oracle is expecting to repeat the values for the bind variables after the USING clause.

Eg.
Cursor Query is:
l_Query:=
SELECT column1,column2 FROM table1, table2, table3
WHERE field=:value1
and field=:value2
and field in (subquery using :value1)

OPEN l_ref_cursor FOR l_query USING value1,value2;
The above statement leads to an ORA-error ORA-01008: not all variables bound.

if the call is changed as under, then it works:
OPEN l_ref_cursor FOR l_query USING value1,value2,value1;

But, I want to achieve this without repeating the values for the bind variables in the USING clause. Any way to acheive this?

Report message to a moderator

Re: PLSQL - providing values to Bind variables appearing multiple times in USING clause [message #642290 is a reply to message #642289]

Sun, 06 September 2015 20:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Any way to acheive this?
No.
How is Oracle to know what values go where?

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

>SELECT column1,column2 FROM table1, table2, table3 WHERE field=:value1 and field=:value2 and field in (subquery using :value1);
Why are you coding Cartesian Product?

Report message to a moderator

Re: PLSQL - providing values to Bind variables appearing multiple times in USING clause [message #642291 is a reply to message #642290]

Sun, 06 September 2015 22:42

danand77
Messages: 2
Registered: September 2015

Junior Member

I want to make the call " OPEN l_ref_cursor FOR l_query USING value1,value2;" to be generic one and provide option for other developers to pass a customised "Query" and reuse my call. But, while re-using, the same values may be repeated several times in the query, values for which would be passed in the USING clause.

The requirement is to make the call " OPEN l_ref_cursor FOR l_query USING value1,value2;" to be generic one and provide option for other developers to pass a customised "Query" and reuse my call. But, while re-using, the same values may be repeated several times in the query, values for which would be passed in the USING clause. Is there a way to achieve this?

[Updated on: Sun, 06 September 2015 22:47]

Report message to a moderator

Re: PLSQL - providing values to Bind variables appearing multiple times in USING clause [message #642292 is a reply to message #642291]

Sun, 06 September 2015 22:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

danand77 wrote on Sun, 06 September 2015 20:42

I want to make the call " OPEN l_ref_cursor FOR l_query USING value1,value2;" to be generic one and provide option for other developers to pass a customised "Query" and reuse my call. But, while re-using, the same values may be repeated several times in the query, values for which would be passed in the USING clause. Is there a way to achieve this?

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

post post test case along with expect & desired results.

Report message to a moderator