| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: How do I bind variables to dynamic SQL?
On Mon, 6 Sep 1999 11:52:00 +0100, you wrote:
>
>have a look at my web-site; one item describes
>binding variables in PL/SQL blocks using dbms_sql,
>another item (for users of oracle 8) describes using
>object types to pass a list of values as a single parameter.
>
>You SQL then does:
> where colX in (cast varray bind variable into subquery)
>or
> where colX not in (cast varray bind variable into subquery)
>
>
>--
>
>Jonathan Lewis
>Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
>dperez_at_juno_nospam.com wrote in message
><37cf22b7.3496227_at_news.uswest.net>...
>>Um, Thomas, I think you may have missed the most important point in my
>original
>>post:
>>
>> The string of comma-delimited integers MUST remain intact.
>> Parsing it, and creating a varying number of
>> predicates would NOT solve the problem.
>>
Yes it will. As I said (my most important point in my post)
The only way you can do this and support bind variables is to PARSE it (but not create a varying number of predicates) and set some upper limit on the total number of IN list values (which is OK, since we have a hard limit anyway:
If you want bind variables -- you will parse, no ifs ands ors about it -- you will parse -- the integer list MUST be broken out into its individual entries -- it has to be.
Since you never specified "not in" in the original request -- yes, we have to rework this a little. But the fact is -- it can easily be done.
X NOT IN ( a, b, c )
is ALMOST the same as:
NOT EXISTS ( select null from dual where X in ( a, b, c ) )
This difference between the two is that if any of A, B or C is NULL the first predicate never returns TRUE or FALSE (and thats your problem with the original solution) but the second one does (it lets A, B, or C be null and still returns records -- which is the desired effect)
So, we can use a postive IN list (which allows for the extra nulls) and negate it. A modified version of a solution is thus:
tkyte_at_8.0> create or replace procedure dynquery( p_in_list in varchar2,
2 p_use_not in boolean default FALSE )
3 is
4 l_tmp_list varchar2(4096) default p_in_list || ',';
5 l_n number;
6
6 l_theCursor integer default dbms_sql.open_cursor;
7 l_columnValue varchar2(2000);
8 l_status integer;
9 l_query varchar2(1000)
10 default 'select empno, ename
11 from emp
12 where ';
13 l_descTbl dbms_sql.desc_tab;
14 l_colCnt number;
15 l_sep char(1) default ' ';
16
16 MAX_COLS number default 7;
17 begin
18 if ( p_use_not )
19 then
20 l_query := l_query || ' not exists ( select null from dual
where EMPNO in (';
21 else
22 l_query := l_query || 'empno in (';
23 end if;
24
24 for i in 1 .. MAX_COLS loop
25 l_query := l_query || l_sep || ':i' || i;
26 l_sep := ',';
27 end loop;
28 if ( p_use_not ) then
29 l_query := l_query || '))';
30 else
31 l_query := l_query || ')';
32 end if;
33
33 dbms_output.put_line( l_query );
34 dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
35
35 for i in 1 .. MAX_COLS loop
36 l_n := instr( l_tmp_list, ',' );
37 -- this ends up binding NULL eventually after
38 -- we exhaust the in list
39
39 dbms_sql.bind_variable( l_theCursor, ':i' || i,
40 to_number(substr( l_tmp_list, 1, l_n-1 )) );
41 l_tmp_list := substr( l_tmp_list, l_n+1 );
42 end loop;
43
43 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
44
44 for i in 1 .. l_colCnt loop
45 dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
46 end loop;
47
47 l_status := dbms_sql.execute(l_theCursor);
48
48 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
49 dbms_sql.column_value( l_theCursor, 1, l_columnValue );
50 dbms_output.put_line( l_columnValue );
51 dbms_sql.column_value( l_theCursor, 2, l_columnValue );
52 dbms_output.put_line( l_columnValue );
53 end loop;
Procedure created.
tkyte_at_8.0>
tkyte_at_8.0> exec dynquery( '7566, 7654, 7698, 7782, 7788' )
select empno, ename
from emp
where empno
in ( :i1,:i2,:i3,:i4,:i5,:i6,:i7)
tkyte_at_8.0> exec dynquery( '7566, 7654, 7698, 7782, 7788', TRUE ) select empno, ename
from emp
where not
exists ( select null from dual where EMPNO in ( :i1,:i2,:i3,:i4,:i5,:i6,:i7))
7369
If you use the trick at Jonathan's web site, that'll work as well (but again, you'll be parsing that list of numbers).
>>
>>Parsing the values and retrieiving them one at a time works fine... Been
>there,
>>done that.......... BUT, in the actual program there are ACTUALLY TWO
>>QUERIES...........
>>
>>1: select *... WHERE ... IN (set...)
>>
>>2: select *... WHERE ... NOT IN (set...)
>>
>>Parsing the string into some unknown series of individual values then
>putting
>>each in the select statement would seem not to work well when doing a "not
>>in"....... Thus, the reason for the original statement "THE STRING OF
>>COMMA-DELIMITED INTEGERS MUST REMAIN INTACT."
>>
if we get the question -- not a direction to give a particular (impossible) solution we'll be able to answer them much better.
Your requirement:
NO PARSING plus the desired feature:
USE BIND VARIABLES is simply not possible to achieve. If you had asked instead "given a comma separated list of numbers -- i would like to have a query i could use to let me to an IN using bind variables on that list as well as a query that lets me do NOT IN on that list" -- we could have given you the solutions much faster.
>>I'll give the instr a shot. It may have such serious performance
>implications
>>its not usable, but its worth a shot.
>>
beware the instr:
tkyte_at_8.0> variable object_list varchar2(25) tkyte_at_8.0> tkyte_at_8.0> exec :object_list := '10, 11, 12, 13, 14, 15'
PL/SQL procedure successfully completed.
tkyte_at_8.0>
tkyte_at_8.0> select user_id
2 from all_users a where
3 instr( :object_list|| ',', to_char( a.user_id) || ',') <> 0
4 /
USER_ID
0
5
(neither 5 nor 0 was in the in list)
it's tricky to use the instr (gotta send in a string with delimiters around each element else you'll get the wrong answer as above). Its not very efficient if the thing being searched on can be used in the index.
I'd go with the <not> in list for performance and meaning...
>
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Sep 06 1999 - 12:52:35 CDT
 |
 |