Dynamic where clause using "execute immediate USING" clause

Question

I need to dynamically add the where clause parameters.

  GETRESULTSET:='SELECT PARENT_PARTYID,PARTY_ID,PRODUCT_ID,PRODUCT_STATUS,BILLING_TYPE,CRMTIMESTAMP,UPDATEDTIMESTAMP,UPDATEDBY FROM     ENT_MAP_CUST_PRODUCT WHERE PARENT_PARTYID=:PARENT_PARTYID';

  IF(PRODUCT_ID IS NOT NULL) THEN
    GETRESULTSET := GETRESULTSET || ' AND  PRODUCT_ID=:PRODUCT_ID ';
  END IF; 

  IF(PRODUCT_STATUS IS NOT NULL) THEN
    GETRESULTSET := GETRESULTSET || ' AND PRODUCT_STATUS=:PRODUCT_STATUS ';
  END IF; 

    IF(BILLING_TYPE IS NOT NULL) THEN
    GETRESULTSET := GETRESULTSET || ' AND BILLING_TYPE=:BILLING_TYPE ';
  END IF;

Now I need to bulk collect into collection type:

execute immediate GETRESULTSET  bulk collect into V_PARTY_TBL USING <<>> ?

Now at runtime I do not know the where clause formed, so how to handle that in USING clause of execute immeditate statement.

Rene · Accepted Answer · 2017-12-11 09:12:24Z

2

Add a single where clause that takes all three parameters:

and (PRODUCT_ID IS NULL or PRODUCT_ID=:PRODUCT_ID )
and (PRODUCT_STATUS IS NULL or PRODUCT_STATUS=:PRODUCT_STATUS)
and (BILLING_TYPE IS NULL or BILLING_TYPE=:BILLING_TYPE)

Then

execute immediate your_query 
   into your_result
  using p_product_id, 
        p_product_status, 
        p_billing_type;

answered Dec 11, 2017 at 9:12

Rene

10.6k5 gold badges37 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

darshan kamat Over a year ago

this may solve the purpose ,but is it the best way to do this ?

Rene Over a year ago

With only the little piece of code you provided it's hard to tell.

Sanders the Softwarer · Accepted Answer · 2017-12-11 09:22:31Z

0

The simpliest way if to add permanent set of parameters:

if product_id is not null
  then sql := sql || ' and product_id = :product_id';
  else sql := sql || ' and :product_id is null';
end if;

But generally your task shows that you prefer a wrong way. You're trying to 'all-on-procedures' approach and faces one of it's nasty disadvantages.

answered Dec 11, 2017 at 9:22

Sanders the Softwarer

2,5061 gold badge15 silver badges29 bronze badges

1 Comment

darshan kamat Over a year ago

what is the disadvantage?

Collectives™ on Stack Overflow

Dynamic where clause using "execute immediate USING" clause

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related