Re: How do I bind variables to dynamic SQL?

A copy of this was sent to "Alexander I. Doroshko" <aid_at_grant.kharkov.ua> (if that email address didn't require changing) On 2 Sep 1999 16:57:49 GMT, you wrote:

>May be, it is simpler this time to rewrite the condition as
>
>instr(object_list || ',', to_char(a.object_id)||',') <> 0?
>

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

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 in list for performance and meaning...

>Then you need even no dbms_sql, and the simplicity can take over
>the possible loss of query productivity.
>--
> Alexander I.Doroshko, aid_at_grant.kharkov.ua
> * Bank "Grant", Kharkov, Ukraine
>
>Thomas Kyte <tkyte_at_us.oracle.com> wrote in article
><37cf56ca.53227777_at_newshost.us.oracle.com>...
>: A copy of this was sent to dperez_at_juno_nospam.com
>: (if that email address didn't require changing)
>: On Thu, 02 Sep 1999 01:43:47 GMT, you wrote:
>:
>: >I have an anonymous block that reads from a table.
>: >
>: >I need to bind 2 variables. One is a single integer value for the
>app_id. This
>: >
>: >works FINE. The other is a series of integers of undetermined
>length, each of
>: >which relates to a single record... In standard sql the query would
>look like:
>: >
>: >select * from xxx where col1 in (123,456,789);
>: >
>: >In dynamic sql this WORKS FINE when the code looks like:
>: >
>: >DBMS_SQL.PARSE(Fingerprint_Cursor,
>: >'SELECT Object_ID, Object_Type, Name, File_Size, Path, Status,
>Finger_print ' ||
>: >
>: > 'FROM Ec_App_Sets A, Ec_Files B ' ||
>: > 'WHERE A.APP_ID = :app_id ' ||
>: > ' AND A.OBJECT_ID in (' || object_list || ' )'
>: > , DBMS_SQL.NATIVE);
>: >
>: >When object_list is a varchar2 loaded with '123,456,789'
>: >
>: >Again, this worked ABSOLUTELY PERFECTLY. Except that since the
>variable isn't
>: >bound, and object list changes every time the routine is run, it
>caused hundreds
>: >of queries to get created......... SO, the question, HOW DO I BIND
>A SET OF
>: >VALUES THAT CAN BE USED IN AN "IN" PREDICATE? The string of
>comma-delimited
>: >integers MUST remain intact. Parsing it, and creating a varying
>number of
>: >predicates would NOT solve the problem. Thus far, I can bind a
>SINGLE integer
>: >and use it in the "IN" but when I do the same with a string as above
>it APPEARS
>: >that SQL treats it as a SINGLE value (the SAME WAY the static SQL
>does) instead
>: >of recognizing it as a series. Ideas?
>:
>:
>: You are trying to convince us that the query:
>:
>: select * from T where x in ( 1 )
>:
>: and
>:
>: select * from T where x in ( 1, 2, 3 )
>:
>: are the same. they are not. You cannot bind a set of values that
>can be used
>: in a IN predicate.
>:
>:
>: 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:
>[skipped]
>:
>: So, the routine can look like this:
>:
>:
>: tkyte_at_8.0> create table emp as select * from scott.emp;
>:
>: Table created.
>:
>: tkyte_at_8.0> create or replace procedure dynquery( p_in_list in
>varchar2 )
>: 2 is
>: 3 l_tmp_list varchar2(4096) default p_in_list || ',';
>: 4 l_n number;
>: 5
>: 5 l_theCursor integer default dbms_sql.open_cursor;
>: 6 l_columnValue varchar2(2000);
>: 7 l_status integer;
>: 8 l_query varchar2(1000)
>: 9 default 'select empno, ename
>: 10 from emp
>: 11 where empno in (';
>: 12 l_descTbl dbms_sql.desc_tab;
>: 13 l_colCnt number;
>: 14 l_sep char(1) default ' ';
>: 15
>: 15 MAX_COLS number default 7;
>: 16 begin
>: 17 for i in 1 .. MAX_COLS loop
>: 18 l_query := l_query || l_sep || ':i' || i;
>: 19 l_sep := ',';
>: 20 end loop;
>: 21 l_query := l_query || ')';
>: 22
>: 22 dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
>: 23
>: 23 for i in 1 .. MAX_COLS loop
>: 24 l_n := instr( l_tmp_list, ',' );
>: 25 -- this ends up binding NULL eventually after
>: 26 -- we exhaust the in list
>: 27 dbms_sql.bind_variable( l_theCursor, ':i' || i,
>: 28 to_number(substr( l_tmp_list, 1, l_n-1 )) );
>: 29 l_tmp_list := substr( l_tmp_list, l_n+1 );
>: 30 end loop;
>: 31
>: 31 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl
>);
>: 32
>: 32 for i in 1 .. l_colCnt loop
>: 33 dbms_sql.define_column( l_theCursor, i, l_columnValue,
>2000 );
>: 34 end loop;
>: 35
>: 35 l_status := dbms_sql.execute(l_theCursor);
>: 36
>: 36 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
>: 37 dbms_sql.column_value( l_theCursor, 1, l_columnValue );
>: 38 dbms_output.put_line( l_columnValue );
>: 39 dbms_sql.column_value( l_theCursor, 2, l_columnValue );
>: 40 dbms_output.put_line( l_columnValue );
>: 41 end loop;
>: 42 end ;
>: 43 /
>[skipped]

--
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 Thu Sep 02 1999 - 13:49:15 CDT