using dynamic SQL and CURCUR

Im updating the table GLRS.TBL_GLRS_GT_PRC_FEED with the enriched data based on the OPICS_TYPE value usng two approaches

The other mapping tables are GLRS.TBL_GLRS_OPICS_RULES and GLRS.TBL_GLRS_OPICS_TYPE

CREATE TABLE GLRS.TBL_GLRS_OPICS_RULES
(
  TBL_GLRS_OPICS_RULES_ID	    NUMBER(38,0) not null,
  TBL_GLRS_OPICS_TYPE_ID		NUMBER(38,0) not null,
  TBL_GLRS_OPICS_PROD_ID		NUMBER(38,0) not null,  
  TBL_AGG_PRODUCT_LINE_ID		NUMBER(38,0) not null,
  TBL_GLRS_MAR_CONF_ID			NUMBER(38,0) not null,
  TBL_GLRS_PROD_LINE_TYPE_ID 	NUMBER(38,0) not null,
  TBL_GLRS_PROD_LINE_SUB_TYPE_ID NUMBER(38,0) not null,
  CREATED_BY                     VARCHAR2(20) not null,
  CREATED_DATETIME               TIMESTAMP(6) not null,
  UPDATED_BY                     VARCHAR2(20) not null,
  UPDATED_DATETIME               TIMESTAMP(6) not null,
  CONSTRAINT PKY_TBL_GLRS_OPICS_RULES_ID PRIMARY KEY(TBL_GLRS_OPICS_RULES_ID)
);
 
CREATE TABLE GLRS.TBL_GLRS_OPICS_TYPE
(
  TBL_GLRS_OPICS_TYPE_ID	    NUMBER(38,0) not null,
  OPICS_TYPE              			VARCHAR2(2) not null,
  TYPE_DESCRIPTION          	VARCHAR2(15) not null,
  CREATED_BY                     VARCHAR2(20) not null,
  CREATED_DATETIME               TIMESTAMP(6) not null,
  UPDATED_BY                     VARCHAR2(20) not null,
  UPDATED_DATETIME               TIMESTAMP(6) not null,
  CONSTRAINT PKY_TBL_GLRS_OPICS_TYPE_ID PRIMARY KEY(TBL_GLRS_OPICS_TYPE_ID)
  
);

Open in new window





The two approaches are as below
-- Approach 1
/*Qry to select MAR based on rules. This will be included as inner select qry in the below base qry.*/
 V_MAR_QRY := ' (SELECT CF.TBL_GLRS_MU_ID FROM GLRS.TBL_GLRS_INTERIM_MAR_CONF CF WHERE CF.TBL_GLRS_MAR_CONF_ID = PR.TBL_GLRS_MAR_CONF_ID) TBL_GLRS_MU_ID ';
     
   	   /*Base query for selecting rules from the OPICS rules table.*/
       V_QUERY := ' SELECT TBL_AGG_PRODUCT_LINE_ID,TBL_GLRS_PROD_LINE_TYPE_ID,TBL_GLRS_PROD_LINE_SUB_TYPE_ID,' ||
             V_MAR_QRY ||
             ' FROM GLRS.TBL_GLRS_OPICS_RULES PR WHERE PR.TBL_GLRS_OPICS_TYPE_ID = :1 ';
      
     FOR V_DATA IN (SELECT T.OPICS_TYPE FROM GLRS.TBL_GLRS_GT_OPICS_FEED T)
     LOOP
     BEGIN
     
     /*Retrieve opics_type id */
     
      SELECT C.TBL_GLRS_OPICS_TYPE_ID
        INTO V_OPICS_TYPE_ID
        FROM GLRS.TBL_GLRS_OPICS_TYPE C
       WHERE C.OPICS_TYPE = V_DATA.OPICS_TYPE;
       
        IF (V_OPICS_TYPE_ID IS NOT NULL) THEN
        /*Retreive MAR and Product line id on the basis of Pool Code*/
        EXECUTE IMMEDIATE V_QUERY
          INTO V_PROD_LINE_ID, V_PROD_LINE_TYPE_ID, V_PROD_LINE_SUB_TYPE_ID, V_MU_ID
          USING V_OPICS_TYPE_ID;
      
        /*Update MAR and Product Line ID against each record.*/
        UPDATE GLRS.TBL_GLRS_GT_PRC_FEED F
           SET F.TBL_AGG_PRODUCT_LINE_ID        = V_PROD_LINE_ID,
               F.TBL_GLRS_PROD_LINE_TYPE_ID     = V_PROD_LINE_TYPE_ID,
               F.TBL_GLRS_PROD_LINE_SUB_TYPE_ID = V_PROD_LINE_SUB_TYPE_ID,
               F.TBL_GLRS_MU_ID                 = V_MU_ID
         WHERE F.OPICS_TYPE = V_DATA.OPICS_TYPE;
       END IF;
      
       EXCEPTION
      WHEN OTHERS THEN
        NULL;  
     
     END ;
     
     END LOOP;

Open in new window

   
    ---------------------

-- Approach 2  
   
 
  	 CURSOR c_get_opics_prodtype
		    IS
		       SELECT b.opics_type, b.prod FROM GLRS.TBL_GLRS_GT_OPICS_FEED b;
		        	  
	 TYPE c_get_opics_prodtype_type IS TABLE OF c_get_opics_prodtype%rowtype;
     opics_prodtype c_get_opics_prodtype_type; 
     
     
     OPEN c_get_opics_prodtype;
   LOOP 
   		FETCH c_get_opics_prodtype BULK COLLECT INTO opics_prodtype limit 200;
        FOR i in 1..opics_prodtype.count 
        
         /*Retreive MAR and Product line id on the basis of Pool Code*/
        
         SELECT PR.TBL_AGG_PRODUCT_LINE_ID,
         		PR.TBL_GLRS_PROD_LINE_TYPE_ID,
         		PR.TBL_GLRS_PROD_LINE_SUB_TYPE_ID,
         		(SELECT CF.TBL_GLRS_MU_ID
            	FROM GLRS.TBL_GLRS_INTERIM_MAR_CONF CF
           		WHERE CF.TBL_GLRS_MAR_CONF_ID = PR.TBL_GLRS_MAR_CONF_ID) TBL_GLRS_MU_ID
         		INTO V_PROD_LINE_ID,
         			 V_PROD_LINE_TYPE_ID,
         			  V_PROD_LINE_SUB_TYPE_ID,
         			  V_MU_ID,
         		FROM GLRS.TBL_GLRS_INTERIM_MAR_CONF CF , GLRS.TBL_GLRS_OPICS_RULES PR
         		WHERE PR.TBL_GLRS_MAR_CONF_ID = CF.TBL_GLRS_MAR_CONF_ID AND 
         		PR.TBL_GLRS_OPICS_TYPE_ID IN (SELECT C.TBL_GLRS_OPICS_TYPE_ID
        									        FROM GLRS.TBL_GLRS_OPICS_TYPE C
       												WHERE C.OPICS_TYPE IN ('BY','RG','FI') ; 
        
        LOOP 
        	IF opics_prodtype(i).OPICS_TYPE = 'BY' THEN
        	
        	   UPDATE GLRS.TBL_GLRS_GT_OPICS_FEED G 
   				  SET G.TBL_AGG_PRODUCT_LINE_ID        = V_PROD_LINE_ID,
               		  G.TBL_GLRS_PROD_LINE_TYPE_ID     = V_PROD_LINE_TYPE_ID,
               		  G.TBL_GLRS_PROD_LINE_SUB_TYPE_ID = V_PROD_LINE_SUB_TYPE_ID,
               		  G.TBL_GLRS_MU_ID                 = V_MU_ID  
               		  
        	
        	ELSIF ((opics_prodtype(i).OPICS_TYPE = 'RG'  AND TRIM(opics_prodtype(i).PROD) = "DEPO" )OR (opics_prodtype(i).OPICS_TYPE = 'FI' AND TRIM(opics_prodtype(i).PROD) = "DEPO") AND (M_DATE = Null or M_DATE = V_AS_OF_DATE + 1) )THEN
        	
        		 UPDATE GLRS.TBL_GLRS_GT_OPICS_FEED G 
   				  SET G.TBL_AGG_PRODUCT_LINE_ID        = V_PROD_LINE_ID,
               		  G.TBL_GLRS_PROD_LINE_TYPE_ID     = V_PROD_LINE_TYPE_ID,
               		  G.TBL_GLRS_PROD_LINE_SUB_TYPE_ID = V_PROD_LINE_SUB_TYPE_ID,
               		  G.TBL_GLRS_MU_ID                 = V_MU_ID   
        	
        	END IF;
        	
        END LOOP;
        EXIT WHEN c_get_opics_prodtype%NOTFOUND;
   
   END LOOP;
   CLOSE c_get_opics_prodtype;

Open in new window

 
   
   Please let me know if approach
LVL 9
ronan_40060Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wasim Akram ShaikCommented:
why do you need Approach 2??

why are you using Update STatement twice in approach 2??

what do you want to do??
ronan_40060Author Commented:
In the 1st  Approach    
     
I would want to avoid repeated cursors like select to get  V_OPICS_TYPE_ID may be this can be done using a join in a cursor that gets V_DATA

In the Approach2
 1st update is for opics_type as BY and second for RG as I have used a cursors , I have used block selects, inserts and updates for performance
Wasim Akram ShaikCommented:
you can go ahead with the second approach, as you had used bulk collects, it will have an optimal performance..

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ronan_40060Author Commented:
for opics_prodtype(i).OPICS_TYPE = 'BY' then values populated for V_PROD_LINE_ID,V_PROD_LINE_TYPE_ID,V_PROD_LINE_SUB_TYPE_ID and V_MU_ID
are different than that populated for opics_prodtype(i).OPICS_TYPE = 'RG' thats why two updates
and here is my modified code using FORALL
 CURSOR c_get_opics_prodtype
		    IS
		       SELECT b.opics_type, b.prod FROM GLRS.TBL_GLRS_GT_OPICS_FEED b;
		        	  
	 TYPE c_get_opics_prodtype_type IS TABLE OF c_get_opics_prodtype%rowtype;
     opics_prodtype c_get_opics_prodtype_type; 
     
      /*Retreive MAR and Product line id on the basis of TYPE*/
        
         SELECT PR.TBL_AGG_PRODUCT_LINE_ID,
         		PR.TBL_GLRS_PROD_LINE_TYPE_ID,
         		PR.TBL_GLRS_PROD_LINE_SUB_TYPE_ID,
         		(SELECT CF.TBL_GLRS_MU_ID
            	FROM GLRS.TBL_GLRS_INTERIM_MAR_CONF CF
           		WHERE CF.TBL_GLRS_MAR_CONF_ID = PR.TBL_GLRS_MAR_CONF_ID) TBL_GLRS_MU_ID
         		INTO V_PROD_LINE_ID,
         			 V_PROD_LINE_TYPE_ID,
         			  V_PROD_LINE_SUB_TYPE_ID,
         			  V_MU_ID,
         		FROM GLRS.TBL_GLRS_INTERIM_MAR_CONF CF , GLRS.TBL_GLRS_OPICS_RULES PR
         		WHERE PR.TBL_GLRS_MAR_CONF_ID = CF.TBL_GLRS_MAR_CONF_ID  
         		 		; 
         		
     
     
     OPEN c_get_opics_prodtype;
   LOOP 
   		FETCH c_get_opics_prodtype BULK COLLECT INTO opics_prodtype limit 200;
        FORALL indx IN opics_prodtype.FIRST..opics_prodtype.LAST 
        
        LOOP 
        	IF opics_prodtype(i).OPICS_TYPE = 'BY' THEN
        	
        	   UPDATE GLRS.TBL_GLRS_GT_OPICS_FEED G 
   				  SET G.TBL_AGG_PRODUCT_LINE_ID        = V_PROD_LINE_ID,
               		  G.TBL_GLRS_PROD_LINE_TYPE_ID     = V_PROD_LINE_TYPE_ID,
               		  G.TBL_GLRS_PROD_LINE_SUB_TYPE_ID = V_PROD_LINE_SUB_TYPE_ID,
               		  G.TBL_GLRS_MU_ID                 = V_MU_ID  
               		  
        	
        	ELSIF ((opics_prodtype(i).OPICS_TYPE = 'RG'  AND TRIM(opics_prodtype(i).PROD) = 'DEPO' )OR (opics_prodtype(i).OPICS_TYPE = 'FI' AND TRIM(opics_prodtype(i).PROD) = 'DEPO') AND (M_DATE = Null or M_DATE = V_AS_OF_DATE + 1) )THEN
        	
        		 UPDATE GLRS.TBL_GLRS_GT_OPICS_FEED G 
   				  SET G.TBL_AGG_PRODUCT_LINE_ID        = V_PROD_LINE_ID,
               		  G.TBL_GLRS_PROD_LINE_TYPE_ID     = V_PROD_LINE_TYPE_ID,
               		  G.TBL_GLRS_PROD_LINE_SUB_TYPE_ID = V_PROD_LINE_SUB_TYPE_ID,
               		  G.TBL_GLRS_MU_ID                 = V_MU_ID   
        	
        	END IF;
        	
        END LOOP;
        EXIT WHEN c_get_opics_prodtype%NOTFOUND;
   
   END LOOP;
   CLOSE c_get_opics_prodtype;

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.