Re: How do I bind variables to dynamic SQL?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 02 Sep 1999 11:13:51 GMT
Message-ID: <37cf56ca.53227777@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:

ERROR at line 1100:
ORA-01795: maximum number of expressions in a list is 254

or in Oracle8i, release 8.1:

ERROR at line 1100:
ORA-01795: maximum number of expressions in a list is 1000

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> 
tkyte_at_8.0> 
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 /

Procedure created.

tkyte_at_8.0>
tkyte_at_8.0> exec dynquery( '7566, 7654, 7698, 7782, 7788' ) 7566
JONES
7654
MARTIN
7698
BLAKE
7782
CLARK
7788
SCOTT PL/SQL procedure successfully completed.

tkyte_at_8.0> exec dynquery( '7654, 7782, 7788' ) 7654
MARTIN
7782
CLARK
7788
SCOTT PL/SQL procedure successfully completed.

You need to decide on a MAX COLS and go from there.

--
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 - 06:13:51 CDT