Solved

using dynamic SQL and CURCUR

Posted on 2012-04-12
4
294 Views
Last Modified: 2012-04-18
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
0
Comment
Question by:ronan_40060
  • 2
  • 2
4 Comments
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37836492
why do you need Approach 2??

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

what do you want to do??
0
 
LVL 9

Author Comment

by:ronan_40060
ID: 37836794
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
0
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 500 total points
ID: 37836886
you can go ahead with the second approach, as you had used bulk collects, it will have an optimal performance..
0
 
LVL 9

Author Comment

by:ronan_40060
ID: 37837573
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

0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle dataguard 5 46
Oracle collections 15 28
Need SQL Query to Find Foreign-keys Without Indexed Columns 4 25
setting local variables in a cursor block 3 20
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question