Creating a dynamic SQL statement with a dynamic number of bind variables ?

I would like to check if it is possible to create a dynamic SQL statement in a stored procedure by using a dynamic number of bind variables.

For example, the stored procedure will receive several input parameters and it will create a select statement but the exact conditions in the WHERE clause will depend on the input parameters.

This can be done by having the VALUES of the parameters embedded in the generated statement. For example:

create function get_result (par1 number, par2 varchar2, ...) ...
begin
  sql_stmt varchar2(1000) := 'select col1, col2 from some_table where 1=1 ';

  if (par1 > 0) then
    sql_stmt = sql_stml || ' and col1 = ' || par1;   end if;

  if (par2 IS NOT NULL) then
    sql_stmt = sql_stmt || ' and col2 = ' || '''' || par2 || '''';   end if;

  open a_cursor for sql_stmt

  return a_cursor;

end;

I would like to check is something similar can happen by using bind variables and the USING clause of the OPEN cursor command. Is it possible that the list of the variables in the USING clause can be created dynamically ? My impression is that it's not possible. And in order to use bind variables in the example above, we have to create several OPEN cursor statements for every combibation of the input parameters and then choose one of them (based on what parameters are set).   

But the number of these statements can grow exponentially on the number of input parameters ? Do you know if there is any other approach for doing something similar ?

Thank you.

DL. Received on Fri Nov 30 2001 - 15:11:13 CST