Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Creating a dynamic SQL statement with a dynamic number of bind variables ?

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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1 Dec 2001 02:51:42 -0800
Message-ID: <9uacnu0ahf@drn.newsguy.com>


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 Corp 
Received on Sat Dec 01 2001 - 04:51:42 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US