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

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. Received on Thu Apr 30 2009 - 10:37:04 CDT