Re: Dynamically Binding variables
From: joel garry <joel-garry_at_home.com>
Date: Thu, 30 Apr 2009 12:15:44 -0700 (PDT)
Message-ID: <bbddd9ea-3d25-4b5d-be2a-8cabc2e06982_at_d19g2000prh.googlegroups.com>
On Apr 30, 8:37 am, Kevin S <Sear..._at_googlemail.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
>
> 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.
Date: Thu, 30 Apr 2009 12:15:44 -0700 (PDT)
Message-ID: <bbddd9ea-3d25-4b5d-be2a-8cabc2e06982_at_d19g2000prh.googlegroups.com>
On Apr 30, 8:37 am, Kevin S <Sear..._at_googlemail.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
>
> 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.
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/f?p=100:11:0::::P11_QUESTION_ID:110612348061#1473944200346605046
(just an interesting example I happened upon, not necessarily an
answer to your question, but note the cast, with and "by the way").
jg
-- _at_home.com is bogus. Proper authentication could be a way to detect and remove spam... http://catless.ncl.ac.uk/Risks/25.65.html#subj8Received on Thu Apr 30 2009 - 14:15:44 CDT