Srikanth A Srikanth A - 27 days ago 10
SQL Question

Dynamic number of bind variables in dynamic sql - Open Cursor

In My stored procedure, I have a query which is dynamic - the number of conditions in where clause varies depending on the input parameter.

in params - x, y, z

searchsql := 'select select1, select2, select3 from tableA where 1 = 1 and ';

if(x is not null) then
searchSql := searchSql || PKG_COMMON.GET_SQL_BINDTXTFLD(x,'select1','a');
-- above package will return AND upper(select1) like upper(:a)
cursorParams := cursorParams || ':' || x || ',';
end if;

if(y is not null) then
searchSql := searchSql || PKG_COMMON.GET_SQL_BINDTXTFLD(y,'select2','b');
-- above package will return AND upper(select2) like upper(:b)
cursorParams := cursorParams || ':' || y || ',';
end if;

--I am trimming the last comma of the cursor param

SELECT SUBSTR(cursorParams, 1, INSTR(cursorParams , ',', -1)-1)
INTO cursorParams FROM dual;

open resultCursor for searchSql using cursorParams


Now, i have this above cursor which needs to be opened using params passed, however in this case the number of params depends on how is sql is formed. So i am dynamically forming the bind variables using cursorParams variable

But the values are not binding, but set only to the first param

how to bind properly, i already tried execute immediate option

Answer Source

What's wrong with:

select select1, select2, select3 
from tableA 
where (:a is null
  OR upper(select1) like upper(:a))
and (:b is null
  OR upper(select2) like upper(:b))
and (:c is null
  OR upper(select3) like upper(:c));

(Assuming that you have bind peeking disabled)