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.
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?
Message was edited by: 1001735