Solved

using dynamic SQL and CURCUR

Posted on 2012-04-12
4
301 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

630 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