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

You could just reference every possible bind variable whether it is used or not. Maybe add something like and decode(null,null,:1,:2,:3,...) is null to the end of the query. Then just list every bind parameter in the using clause. Remember that binds are associated by position and matched by name though (i.e. the parameters in your using clause must be in the same order and have the same names as your 'fake decode' statement in the dynamic query).

Richard

DL wrote:
>
> 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:55:28 CST