Solved

using dynamic SQL and CURCUR

Posted on 2012-04-12
4
279 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now