Re: Dynamically Binding variables

: My pl/sql procedure dynamically generates some sql of the form

: v_sql:= 'Select colA, colB from mytable WHERE colC MEMBER OF :ntt1';

: This is fired later as follows

: OPEN po_ref_Cursor FOR v_sql USING ntt1;

: A request has been made where by there may or may not be member of
: conditions on further conditons ie the sql might be

: v_sql:= 'Select colA, colB from mytable WHERE colC MEMBER OF :ntt1';
: or
: v_sql:= 'Select colA, colB from mytable WHERE colC MEMBER OF :ntt1
: and colD member of :ntt2';
: or
: v_sql:= 'Select colA, colB from mytable WHERE colD member of :ntt2';

: That is easy enough to generate however my problem comes when I come
: to call it.

: How do I dynamically open the ref cursorr to bind the relevant bind
: variables in the right order? Based on the above I need to dynamically
: generate and bind

I think that package DBMS_SQL is what you want.

Depending on how much variation there is in the code, you could also require the sql to contain the same bind variables, but code them in such a way that some will be effectively ignored. For example a statement that requires two bind variables but the first is not actually used...

        select * from table where ignore_this(:ntt1) = 1 and :ntt2 ...

create function ignore_this(p in typeX) return number is begin return 1; end;

$0.10 Received on Thu Apr 30 2009 - 12:39:35 CDT