Hi All,

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

either
OPEN po_ref_Cursor FOR v_sql USING ntt1;
or
OPEN po_ref_Cursor FOR v_sql USING ntt1, ntt2;
or
OPEN po_ref_Cursor FOR v_sql USING ntt2;

Is it possible to do this or do I have to hard code loads of if
statements to cover all options?

The alternative is to revert to the IN condition which is possible but
seems a bit of a backword step.

thanks.

Kevin S (SearleK (AT) googlemail (DOT) com) wrote:
: Hi All,

: 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

On Apr 30, 8:37*am, Kevin S <Sear... (AT) googlemail (DOT) com> wrote:
Also search for:
variable in-list
on asktom.oracle.com
or google
+variable +in +list site:asktom.oracle.com

Note that those discussions can go on for years and some interesting
nuggets are buried deep, like
http://asktom.oracle.com/pls/asktom/...44200346605046
(just an interesting example I happened upon, not necessarily an
answer to your question, but note the cast, with and "by the way").

jg
--
@home.com is bogus.
Proper authentication could be a way to detect and remove spam...
http://catless.ncl.ac.uk/Risks/25.65.html#subj8