Re: How do I bind variables to dynamic SQL?

May be, it is simpler this time to rewrite the condition as

instr(object_list || ',', to_char(a.object_id)||',') <> 0?

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] Received on Thu Sep 02 1999 - 11:57:49 CDT