Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Creating a dynamic SQL statement with a dynamic number of bind variables ?
In article <4bc2752b.0111301311.5d488dac_at_posting.google.com>,
dimitris_at_cs.umb.edu says...
>
>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).
>
see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1288401763279
for a method of doing this.
>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.
-- Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Sat Dec 01 2001 - 04:51:42 CST