Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Using for Execute Immediate (10g R2 - Windows Server 2003)
Dynamic Using for Execute Immediate [message #406553] Thu, 04 June 2009 10:29 Go to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Is is possible to built a dynamic Using statement for an Execute Immediate statement?

Something like this:


procedure test (purchase_order_id varchar2,
                vendor_id         varchar2,
                category_id       varchar2) is

dynamicSQL            varchar2(1000);
dynamicSQLWhereClause varchar2(1000) default 'Where ';

begin

dynamicSQL := 'select purchase_order
                from purchase
                  where company = xxx';

if purchase_order_id is not null
  then
    dynamicSQLWhereClause := dynamicSQLWhereClause||' and purchase_order = :a ';
end if;
   
if vendor_id is not null
  then
    dynamicSQLWhereClause := dynamicSQLWhereClause||' and vendor = :b ';
end if;

if category_id is not null
  then
    dynamicSQLWhereClause := dynamicSQLWhereClause||' and category = :c ';
end if;

execute immediate dynamicSQL||dynamicSQLWhereClause 
  bulk collect into POSearch 
    using 
      if purchase_order_id is not null
        then
          purchase_order_id
      end if;

      if vendor_id is not null
        vendor_id
      end if;

     if category_id is not null
       then
        category_id
     end if;
end;



So, if you follow me, the Using clause might be something like this:

* using purchase_order_id
* using vendor_id, category_id
* using purchase_order_id, vendor_id, category_id
* using category_id
...etc
Re: Dynamic Using for Execute Immediate [message #406556 is a reply to message #406553] Thu, 04 June 2009 10:52 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
You have an elegant non-scalable solution.
An alternative will require many more characters in procedure, but will scale much better.
By using CASE & "hard coded" SQL (for each situation), the result set will be the same as yours.

[Updated on: Thu, 04 June 2009 10:52]

Report message to a moderator

Re: Dynamic Using for Execute Immediate [message #406562 is a reply to message #406553] Thu, 04 June 2009 11:43 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Are you talking about something like this:


select purchase_order
  from purchase
   where company = xxx and
         a.purchase_order_id = 
         case
           when PurchaseOrderId is not null
             then
               PurchaseOrderId
             else
               a.purchase_order_id
         end;



That's only for one predicate. I wonder how I do the others.
Re: Dynamic Using for Execute Immediate [message #406573 is a reply to message #406553] Thu, 04 June 2009 12:12 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
If I understand this situation correct, I see three fields, purchase_order_id, vendor_id, & category_id; which may or may not be null.
So I see 8 different combinations of these fields (2**3 or [2 cubed] = 8 ).
The combinations range between all 3 being NULL to all 3 being NOT NULL.

I was not envisioning using CASE within the SELECT, but having CASE determining which of 8 hard coded SELECT statements actually get invoked.

Does this make any sense?

[Updated on: Thu, 04 June 2009 12:12]

Report message to a moderator

Re: Dynamic Using for Execute Immediate [message #406574 is a reply to message #406553] Thu, 04 June 2009 12:19 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Ok, I understand what you are trying to do. Unfortunately, the three input parameters was used for simplicity. I just tried to give a simple example of what I was trying to do. The Using clause could contain a combination of up to 18 different bind arguments.
Re: Dynamic Using for Execute Immediate [message #406581 is a reply to message #406553] Thu, 04 June 2009 13:25 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Anyone know of a way to build a dynamic USING clause on the fly?
Re: Dynamic Using for Execute Immediate [message #406583 is a reply to message #406574] Thu, 04 June 2009 13:29 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, I would do (and have done it in similar cases) it like:

select purchase_order
  from purchase
   where company = xxx 
     and ( ( :a is null ) or (a.purchase_order_id = :a ) )
     and ( ( :b is null ) or (a.vendor_id         = :b ) )
     and ( ( :c is null ) or (a.category_id       = :c ) )


and then just pass all the variables. That way you could possibly forget about the dynamic SQL completely.

[Updated on: Thu, 04 June 2009 13:36]

Report message to a moderator

Re: Dynamic Using for Execute Immediate [message #406586 is a reply to message #406553] Thu, 04 June 2009 14:30 Go to previous message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
ThomasG,

I like your solution way better than mine!
Previous Topic: Oracle Trigger Error
Next Topic: & in insert statement
Goto Forum:
  


Current Time: Mon Dec 05 19:15:19 CST 2016

Total time taken to generate the page: 0.33116 seconds