Home » SQL & PL/SQL » SQL & PL/SQL » converting FOR to FORALL
converting FOR to FORALL [message #250473] Tue, 10 July 2007 03:26 Go to next message
yog_23
Messages: 79
Registered: March 2007
Member
How do I convert the following to FORALL...

Because there is so much transaction involved, couldn't figure out whats the easiest way to convert The procedure name with keyword **create** is an insert and *update** is an update...

CREATE OR REPLACE PROCEDURE Sp_Process  
(
      pdtCurrentPaymentDate 	IN 	TIMESTAMP,
      pdtRunDate 				IN 	TIMESTAMP
)
IS  
BEGIN

	/****************************************************************************************
	* Declare variables 
	****************************************************************************************/
    DECLARE 
	  
	    CURSOR int_cur
        IS 
        SELECT
			RECORD_NO		        ,   
			INT_SRCC_HICN        	,
			INT_CONTRACT_NO			,
			INT_PBP_ID			 	,
			INT_SEGMENT_NO		    ,
			INT_RUN_DATE		 	,
			INT_SBSDY_EFF_DT		,
			INT_SBSDY_TERM_DT		,
			INT_PARTD_SBSDY_PERC	,
			INT_LIS_COPAY_LVL		,
			INT_ENR_EFF_DT			,
			INT_ENR_TERM_DT			,
			INT_PRTC_PREM			,
			INT_PRTD_PREM			,
			INT_LATE_PENALTY		,
			INT_SRCA_LICS_SBSDY		,
			INT_LIS_PENALTY			,
			SRCA_LATE_WAIV_AMT		,
			SRCA_TOTAL_PREM       	,
			SRCA_HCFA_EFF_DT		,
			SRCA_HCFA_TERM_DT		,
			SRCA_PARTD_SBSDY		,
			SRCA_COPAY_CAT			,
			SRCA_LICS_SBSDY			,
			SRCB_EFF_DT				,
			SRCB_TERM_DT			,
			SRCB_RT_AREA			,
			ORIGIN					,
			LOVR_BYPASS				,
			IGNORE_SRCA				,
			IGNORE_SRCB				,
			ERRORS_SRCA				,
			ERRORS_SRCB				,
			ACTION_SRCA				,
			ACTION_SRCB				,
			SRCC_CK					,
			SRCD_CK					,
			JOB_RUN_DATE		
        FROM 
         	INTF.INT_STG 
        WHERE  
   			DUPLICATE_FLAG = 0;

		lnRecordNo              NUMBER  ; 	
		lchSRCASRCCHicn          VARCHAR2(12)  ; 
		lchSRCASRCCCurrHicn      VARCHAR2(12)  ; 
		lchSRCASRCCPrevHicn      VARCHAR2(12)  ; 
		lchSRCAContractNo		VARCHAR2(5) ;   
		lchSRCAPbpId				VARCHAR2(4) ;   
		lchSRCASegmentNo			VARCHAR2(3) ;   
		ldtSRCARunDate			TIMESTAMP(3) ;  
		ldtSRCASbsdyEffDt		TIMESTAMP(3)  ; 
		ldtSRCASbsdyTermDt		TIMESTAMP(3) ;  
		lchSRCAPartdSbsdyPerc	VARCHAR2(3) ;   
		lchSRCALisCopayLvl		VARCHAR2(1) ;   
		ldtSRCAEnrEffDt			TIMESTAMP(3) ;  		
		ldtSRCAEnrTermDt			TIMESTAMP(3) ;  
		lnSRCAPrtcPrem			NUMBER(18,4) ;  
		lnSRCAPrtdPrem			NUMBER(18,4) ;  
		lnSRCALatePenalty		NUMBER(18,4) ;  
		lnSRCASRCALicsSbsdy		NUMBER(18,4) ;  
		lnSRCALisPenalty			NUMBER(18,4) ;  
		lnSRCALateWaivAmt		NUMBER(18,4) ;  
		lnSRCATotalPrem         NUMBER(18,4) ;	
		ldtSRCAHcfaEffDt		TIMESTAMP(3) ;	
		ldtSRCAHcfaTermDt		TIMESTAMP(3) ;	
		lchSRCAPartdSbsdy		VARCHAR2(3) ;	
		lchSRCACopayCat			VARCHAR2(1) ;	
		lnSRCALicsSbsdy			NUMBER(18,4) ;	
		ldtSRCZSRCAHcfaEffDt		TIMESTAMP(3) ;	
		ldtSRCZSRCAHcfaTermDt	TIMESTAMP(3) ;	
		lchSRCZSRCAPartdSbsdy	VARCHAR2(3) ;	
		lchSRCZSRCACopayCat		VARCHAR2(1) ;	
		lnSRCZSRCALicsSbsdy		NUMBER(18,4) ;	
		ldtSRCBEffDt			TIMESTAMP(3) ;	
		ldtSRCBTermDt			TIMESTAMP(3) ;	
		lchSRCBRtArea			VARCHAR2(3) ;	
		ldtSRCZSRCBEffDt			TIMESTAMP(3) ;	
		ldtSRCZSRCBTermDt		TIMESTAMP(3) ;	
		lchSRCZSRCBRtArea		VARCHAR2(3) ;	
		lnOrigin				NUMBER(1,0) ;	
		lnLovrBypass			NUMBER(1,0) ;	
		lnIgnoreSRCA			NUMBER(1,0) ;	
		lnIgnoreSRCB			NUMBER(1,0) ;	
		lnErrorsSRCA			NUMBER(10,0) ;	
		lnErrorsSRCB			NUMBER(10,0) ;	
		lchActionSRCA			VARCHAR2(2) ;	
		lchActionSRCB			VARCHAR2(2) ;	
		lnSRCCCk				NUMBER(10,0) ;	
		lnSRCDCk				NUMBER(10,0) ;	
		ldtJobRunDate			TIMESTAMP(3) ;	
		
		boolDuplicate           BOOLEAN ; -- flag for duplicate records  
		boolEligible            BOOLEAN ; -- flag for eligibility   
		boolCreateSRCA			BOOLEAN ;
		boolCreateSRCB			BOOLEAN ;
	
		lnGrgrCk   	  			NUMBER(10,0) ;
		lchSRCDId   	  		VARCHAR2(9)  ;
		lchGrgrId       		VARCHAR2(8)  ;
		lchSRCDMctrSts      	VARCHAR2(4)  ;  
		
		lnExceptionCode     	INTEGER ;		

		--ROWTYPE FOR QUERY OUTPUT
	    TYPE int_tab_typ IS TABLE OF int_cur%ROWTYPE;
	    int_tab int_tab_typ;		
		
	BEGIN	 /**Main Block Begins**/	

		--DBMS_OUTPUT.PUT_LINE('START : ' || TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));	

		/*************************************************
		** OPEN Cursor                                  **
		*************************************************/	
	
	    OPEN int_cur;

		/*************************************************
		** MAIN Cursor LOOP BEGINS						**
		*************************************************/	
		
	    LOOP


			/*************************************************
			** BULK COLLECT                                 **
			*************************************************/	
		
	        FETCH int_cur BULK COLLECT INTO int_tab LIMIT 10000;

		    /****************************************************************************************
		    * Initialize variables 
		    ****************************************************************************************/	    	
		    
			ldtJobRunDate			:= pdtRunDate;    
			lnOrigin				:= 0;	

			/*************************************************
			** LOOP throigh bult collect fetch BEGINS       **
			*************************************************/				
		
			    /****************************************************************************************
			    * Loop through the cur_lics_update CURSOR - BEGINS
			    ****************************************************************************************/	
	        	FOR i IN 1 .. int_tab.COUNT LOOP
	   			
			    
				    	--Fetch into variables 	
					boolEligible		:= FALSE;
					lnRecordNo			:= int_tab(i).RECORD_NO;
				    lchSRCASRCCCurrHicn	:= int_tab(i).INT_SRCC_HICN;  
					lchSRCASRCCHicn      := int_tab(i).INT_SRCC_HICN           	; 
					lchSRCAContractNo	:= int_tab(i).INT_CONTRACT_NO		;   
					lchSRCAPbpId			:= int_tab(i).INT_PBP_ID			;   
					lchSRCASegmentNo		:= int_tab(i).INT_SEGMENT_NO		;   
					ldtSRCARunDate		:= int_tab(i).INT_RUN_DATE			;  
					ldtSRCASbsdyEffDt	:= int_tab(i).INT_SBSDY_EFF_DT		; 
					ldtSRCASbsdyTermDt	:= NVL(int_tab(i).INT_SBSDY_TERM_DT,TO_TIMESTAMP('12/31/9999','MM/DD/YYYY'))	; --12/31/9999 if null 
					--ldtSRCASbsdyTermDt	:= int_tab(i).INT_SBSDY_TERM_DT		;
					lchSRCAPartdSbsdyPerc	:= NVL(int_tab(i).INT_PARTD_SBSDY_PERC,'000')	;   
					lchSRCALisCopayLvl	:= int_tab(i).INT_LIS_COPAY_LVL		;   
					ldtSRCAEnrEffDt		:= int_tab(i).INT_ENR_EFF_DT		;  
					ldtSRCAEnrTermDt		:= int_tab(i).INT_ENR_TERM_DT		;  
					lnSRCAPrtcPrem		:= int_tab(i).INT_PRTC_PREM		;  
					lnSRCAPrtdPrem		:= int_tab(i).INT_PRTD_PREM		;  
					lnSRCALatePenalty	:= int_tab(i).INT_LATE_PENALTY		;  
					lnSRCASRCALicsSbsdy	:= int_tab(i).INT_SRCA_LICS_SBSDY		;  
					lnSRCALisPenalty		:= int_tab(i).INT_LIS_PENALTY		;  
					lnSRCALateWaivAmt	:= int_tab(i).SRCA_LATE_WAIV_AMT		;  
					lnSRCATotalPrem     := int_tab(i).SRCA_TOTAL_PREM        	;
					lnSRCCCk			:= 0;
					lnSRCDCk			:= 0;
					lnGrgrCk   	  		:= 0;
					lchSRCDId   	  	:= '' ;
					lchGrgrId       	:= '';
					lchSRCDMctrSts      := '' ;  
					lnExceptionCode     := 0 ;		
			    	boolDuplicate		:= FALSE;
					boolEligible		:= FALSE;
		
					/**Reset variables**/
					ldtSRCAHcfaEffDt	:=	NULL;
					ldtSRCAHcfaTermDt	:=	NULL;
					lchSRCAPartdSbsdy	:=	NULL;
					lchSRCACopayCat		:=	NULL;
					lnSRCALicsSbsdy		:=	NULL;
					ldtSRCBEffDt		:=	NULL;
					ldtSRCBTermDt		:=	NULL;
					lchSRCBRtArea		:=	NULL;
					lnLovrBypass		:=	NULL;
					lnIgnoreSRCA		:=	NULL;
					lnIgnoreSRCB		:=	NULL;
					lnErrorsSRCA		:=	NULL;
					lnErrorsSRCB		:=	NULL;
					lchActionSRCA		:=	NULL;
					lchActionSRCB		:=	NULL;
					lnLovrBypass		:=  NULL;
					
				    /****************************************************************************************
				    * Eligibility Check 
				    ****************************************************************************************/			
					BEGIN 

						--DBMS_OUTPUT.PUT_LINE('ELIG BEGINS : ' || TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));	

					    --Eligibility check  
					    INTF.Sp_Get_Elig (lchSRCASRCCCurrHicn, 
													 pdtCurrentPaymentDate, 
													 lnSRCCCk, 
													 lnSRCDCk, 
													 lnGrgrCk, 
													 lchSRCDId, 
													 lchGrgrId, 
													 lchSRCDMctrSts,
													 lnExceptionCode, 
													 lnErrorsSRCA,
													 lnErrorsSRCB,
													 lchActionSRCA,
													 lchActionSRCB, 
													 boolEligible ); 
			
						--DBMS_OUTPUT.PUT_LINE('ELIG ENDS : ' || TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));	

						 			    
						IF (lchSRCDMctrSts = 'LOVR') THEN		
						   lnLovrBypass			:= 1;	
						   lchActionSRCA		:= 'I';
						   lchActionSRCB		:= 'I';			
						ELSE
						   lnLovrBypass			:= 0;
						END IF;
						
					END;
				
				    /****************************************************************************************
				    * Business Logic Begins
					* Compare and Validate SRCA and SRCB  only if eligible and not a duplicate 
				    ****************************************************************************************/			
					
					IF (boolEligible) THEN
		  			BEGIN

						--DBMS_OUTPUT.PUT_LINE('SRCA BEGINS : ' || TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));	
					    
						/**Validate SRCA data **/
						INTF.Sp_Get_SRCA (
								lchSRCASRCCCurrHicn      ,
								pdtCurrentPaymentDate 	,
								lnSRCCCk    			,
								lnSRCDCk   				,
								lnGrgrCk   				,
								ldtSRCASbsdyEffDt   		,
								ldtSRCASbsdyTermDt		, 
								lchSRCAPartdSbsdyPerc 	, 
								lchSRCALisCopayLvl		, 
								lnSRCASRCALicsSbsdy		, 
								lnExceptionCode  		,
								boolCreateSRCA			,
								ldtSRCAHcfaEffDt		,
								ldtSRCAHcfaTermDt		,	
								lchSRCAPartdSbsdy		,	
								lchSRCACopayCat			,	
								lnSRCALicsSbsdy			,
								ldtSRCZSRCAHcfaEffDt		,
								ldtSRCZSRCAHcfaTermDt	,	
								lchSRCZSRCAPartdSbsdy	,	
								lchSRCZSRCACopayCat		,	
								lnSRCZSRCALicsSbsdy		,
								lnIgnoreSRCA			,
								lnErrorsSRCA			, 
								lchActionSRCA );  
							
						--DBMS_OUTPUT.PUT_LINE('SRCA ENDS : ' || TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));	

						--DBMS_OUTPUT.PUT_LINE('SRCB BEGINS : ' || TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));	
						
						/*Validate SRCB date **/
						INTF.Sp_Get_SRCB (
								lchSRCASRCCCurrHicn      ,
								pdtCurrentPaymentDate 	,
								lnSRCCCk    			,
								lnSRCDCk   				,
								lnGrgrCk   				,
								ldtSRCASbsdyEffDt   		,
								ldtSRCASbsdyTermDt		, 
								lchSRCAPartdSbsdyPerc 	, 
								lchSRCALisCopayLvl		, 
								lnSRCASRCALicsSbsdy		, 
								lnExceptionCode  		,
								boolCreateSRCB			,
								ldtSRCBEffDt			,
								ldtSRCBTermDt			,
								lchSRCBRtArea			,
								ldtSRCZSRCBEffDt			,
								ldtSRCZSRCBTermDt		,
								lchSRCZSRCBRtArea		,
								lnIgnoreSRCB			,
								lnErrorsSRCB			, 
								lchActionSRCB );

						--DBMS_OUTPUT.PUT_LINE('SRCB ENDS : ' || TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));	
		
						/**Set Ignore flag for LOVR**/
						IF (lnLovrBypass = 1) THEN
						   	 lchActionSRCB := 'I';
							 lchActionSRCA := 'I';
						END IF;		
										
					    /****************************************************************************************
					    * CREATE SRCZ for valid entries - BEGINS
					    ****************************************************************************************/							
						
						--If not duplicate	(boolDuplicate)
						--If eligible       (boolEligible)
						--If not LOVR is setup (lnLovrBypass = 0)
						--If not orphan rows (both booolCreateSRCA and SRCB is true )
						--Create SRCZ records 						
					 	
						IF (boolCreateSRCB AND boolCreateSRCA) THEN
						IF (lnLovrBypass = 0) THEN
						   
						--DBMS_OUTPUT.PUT_LINE('SRCZ BEGINS : ' || TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));	

						    /**Create SRCD**/
							INTF.Sp_Create_SRCD 
							(
								lnSRCCCk   ,
								lchSRCDId  ,
								lchGrgrId  ,
								lnExceptionCode
							) ;
		
							/**Create SRCC**/
							INTF.Sp_Create_SRCC 
							(
								lnSRCCCk ,
								lnExceptionCode
							) ;
		
							 /**Create SRCA**/ 
							INTF.Sp_Create_SRCA 
							(
								lnSRCCCk   		  	   ,
								ldtSRCZSRCAHcfaEffDt	   ,	
								ldtSRCZSRCAHcfaTermDt   ,
								lchSRCZSRCAPartdSbsdy   ,
								lchSRCZSRCACopayCat	   , 
								lnSRCZSRCALicsSbsdy	   ,
								lchSRCASRCCCurrHicn	   , 
								lnExceptionCode  	  
							) ;					
		
							/**Create SRCB**/
							INTF.Sp_Create_SRCB 
							(
								lnSRCCCk   		  	  ,
								ldtSRCZSRCBEffDt		  , 
								ldtSRCZSRCBTermDt	  ,	
								lchSRCZSRCBRtArea	  ,
								lnExceptionCode  	  
							) ;
											
						--DBMS_OUTPUT.PUT_LINE('SRCZ ENDS : ' || TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));	
						
						END IF;	
						END IF; 
		
					    /****************************************************************************************
					    * CREATE SRCZ for valid entries - ENDS
					    ****************************************************************************************/							
						  
					END;
					END IF;
					
		
					/****************************************************************************************
				    * Business Logic Ends
				    ****************************************************************************************/				
					
				    /****************************************************************************************
				    * Update Staging table FOR ALL RECORDS 
				    ****************************************************************************************/							
					--DBMS_OUTPUT.PUT_LINE('UPDATE BEGINS : ' || TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));	

					BEGIN
						INTF.Sp_Update_Lics 
						(
							ldtSRCAHcfaEffDt   ,   
							ldtSRCAHcfaTermDt  ,   
							lchSRCAPartdSbsdy  ,   
							lchSRCACopayCat    ,   
							lnSRCALicsSbsdy    ,   
							ldtSRCBEffDt       ,
							ldtSRCBTermDt      ,
							lchSRCBRtArea      ,
							lnOrigin           ,
							lnLovrBypass       ,
							lnIgnoreSRCA       ,
							lnIgnoreSRCB       ,
							lnErrorsSRCA       ,
							lnErrorsSRCB       ,
							lchActionSRCA      ,
							lchActionSRCB      ,
							lnSRCCCk           ,
							lnSRCDCk           ,
							ldtJobRunDate      ,
							lnRecordNo
						);
					END;		 			

					--DBMS_OUTPUT.PUT_LINE('UPDATE ENDS : ' || TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));	
			
		        END LOOP;
				/*************************************************
				** LOOP throigh bult collect fetch ENDS         **
				*************************************************/
	
				/*************************************************
				** Commit every 1000 records					**
				*************************************************/				
				COMMIT;
		
	        EXIT WHEN int_cur%NOTFOUND;
			
	    END LOOP;
		
		/*************************************************
		** MAIN Cursor LOOP ENDS 						**
		*************************************************/		

		/*************************************************
		** Commit 										**
		*************************************************/				
		COMMIT;

		--DBMS_OUTPUT.PUT_LINE('End Main Loop');	
		
		/*************************************************
		** CLOSE Cursor 		 						**
		*************************************************/	
		
		CLOSE int_cur;
		--DBMS_OUTPUT.PUT_LINE('Process completed successfully');	
		
			   
	END;	 /**Main Block Ends**/
       
END Sp_Process ;
/



Thank you
Yog
Re: converting FOR to FORALL [message #250477 is a reply to message #250473] Tue, 10 July 2007 03:50 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Can you simplify your code please. One thing I'd do is pass the table to the insert/update procedures them handle the bulk operation. I also fail to see the need for all the anonymous blocks you have. But first things first: simplify your code a little bit so we can easily see the flow.

MHE
Re: converting FOR to FORALL [message #250488 is a reply to message #250473] Tue, 10 July 2007 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And break the line at most 80 characters.
Use the "Preview Message" button before submitting your post.

Regards
Michel
Re: converting FOR to FORALL [message #250622 is a reply to message #250473] Tue, 10 July 2007 12:30 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
Thank you Michel and Maher..

Simplified code is below: Thank you...

CREATE OR REPLACE PROCEDURE Sp_Process  
(
      pdtCurrentPaymentDate 	IN 	TIMESTAMP,
      pdtRunDate 				IN 	TIMESTAMP
)
IS  
BEGIN

    DECLARE 
	  
	CURSOR int_cur
        IS 
        SELECT
		RECORD_NO		        ,   
		INT_SRCC_HICN        		,
		INT_CONTRACT_NO			,
		INT_PBP_ID			,
		INT_SEGMENT_NO		    	,
		INT_RUN_DATE		 	,
		INT_SBSDY_EFF_DT		,
		INT_SBSDY_TERM_DT		,
		INT_PARTD_SBSDY_PERC		,
		INT_LIS_COPAY_LVL		,		,
		SRCB_RT_AREA			,
		ORIGIN				,
		LOVR_BYPASS			,
		IGNORE_SRCA			,
		IGNORE_SRCB			,
		ERRORS_SRCA			,
		ERRORS_SRCB			,
		ACTION_SRCA			,
		ACTION_SRCB			,
		SRCC_CK				,
		SRCD_CK				,
		JOB_RUN_DATE		
        FROM 
         	INTF.INT_STG 
        WHERE  
   		DUPLICATE_FLAG = 0;

		lnRecordNo              NUMBER  ; 	
		lchSRCASRCCHicn         VARCHAR2(12)  ; 
		lchSRCASRCCCurrHicn     VARCHAR2(12)  ; 
		lchSRCASRCCPrevHicn     VARCHAR2(12)  ; 
		boolDuplicate           BOOLEAN ; 
		boolEligible            BOOLEAN ; 
		boolCreateSRCA		BOOLEAN ;
		boolCreateSRCB		BOOLEAN ;
	
		lnGrgrCk   	  	NUMBER(10,0) ;
		lchSRCDId   	  	VARCHAR2(9)  ;
		lchGrgrId       	VARCHAR2(8)  ;
		lchSRCDMctrSts      	VARCHAR2(4)  ;  
		
		lnExceptionCode     	INTEGER ;		

		TYPE int_tab_typ IS TABLE OF int_cur%ROWTYPE;
		int_tab int_tab_typ;		
		
	BEGIN	 /**Main Block Begins**/	

	
	    OPEN int_cur;

		
	    LOOP

		
	        FETCH int_cur BULK COLLECT INTO int_tab LIMIT 10000;
		    
			ldtJobRunDate			:= pdtRunDate;    
			lnOrigin			:= 0;	

	        	FOR i IN 1 .. int_tab.COUNT LOOP
	   			
				boolEligible		:= FALSE;
				lnRecordNo		:= int_tab(i).RECORD_NO;
				lchSRCASRCCCurrHicn	:= int_tab(i).INT_SRCC_HICN;  
				lchSRCASRCCHicn         := int_tab(i).INT_SRCC_HICN           	; 
				lchSRCAContractNo	:= int_tab(i).INT_CONTRACT_NO		;   
				lchSRCAPbpId		:= int_tab(i).INT_PBP_ID		;   
				lchSRCASegmentNo	:= int_tab(i).INT_SEGMENT_NO		;   
				lchSRCDId   	  	:= '' ;
				lchGrgrId       	:= '';
				lchSRCDMctrSts      	:= '' ;  
				lnExceptionCode     	:= 0 ;		
				boolDuplicate		:= FALSE;
				boolEligible		:= FALSE;


			    /*************************************************************
			    * Get all releted info  
			    * Some are output parameters which receive some value used by 
			    * procedures called below
			    **************************************************************/			

			    INTF.Sp_Get_Elig (lchSRCASRCCCurrHicn, 
					 pdtCurrentPaymentDate, 
					 lnSRCCCk, 
					 lnSRCDCk, 
					 lnErrorsSRCB,
					 lchActionSRCA,
					 lchActionSRCB, 
					 boolEligible ); 




			    /**************************************************************
			    * Business Logic Begins
			    * Compare and Validate SRCA and SRCB  only if 
			    * eligible and not a duplicate 
			    ***************************************************************/			

				IF (boolEligible) THEN
				BEGIN

					INTF.Sp_Get_SRCA (
							ldtSRCZSRCAHcfaEffDt		,
							ldtSRCZSRCAHcfaTermDt	,	
							lchSRCZSRCAPartdSbsdy	,	
							lchSRCZSRCACopayCat		,	
							lnSRCZSRCALicsSbsdy		,
							lnIgnoreSRCA			,
							lnErrorsSRCA			, 
							lchActionSRCA );  


					/*Validate SRCB date **/

					IF (boolCreateSRCB AND boolCreateSRCA) THEN
					IF (lnLovrBypass = 0) THEN

					    /**Create SRCD**/
						INTF.Sp_Create_SRCD 
						(
							lnSRCCCk   ,
							lchSRCDId  ,
							lchGrgrId  ,
							lnExceptionCode
						) ;

						/**Create SRCC**/

						 /**Create SRCA**/ 

						/**Create SRCB**/


					END IF;	
					END IF; 


				END;
				END IF;



				BEGIN
					INTF.Sp_Update_Lics 
					(
						ldtSRCAHcfaEffDt   ,   
						ldtSRCAHcfaTermDt  ,   
						lchSRCAPartdSbsdy  ,   
						lchSRCACopayCat    ,   
						lnSRCALicsSbsdy    ,   
						ldtSRCBEffDt       ,
						ldtSRCBTermDt      ,
						lchSRCBRtArea      ,
						lnOrigin           ,
						lnLovrBypass       ,
						lnIgnoreSRCA       ,
						lnIgnoreSRCB       ,
						lnErrorsSRCA       ,
						lnErrorsSRCB       ,
						lchActionSRCA      ,
						lchActionSRCB      ,
						lnSRCCCk           ,
						lnSRCDCk           ,
						ldtJobRunDate      ,
						lnRecordNo
					);
				END;		 			

			
		        END LOOP;
			
			COMMIT;
		
	        EXIT WHEN int_cur%NOTFOUND;
			
	    END LOOP;
		
	
	    COMMIT;

		
	    CLOSE int_cur;
		
			   
	END;	 /**Main Block Ends**/
       
END Mbh_Sp_Process_Lics ;
/

Re: converting FOR to FORALL [message #250726 is a reply to message #250622] Wed, 11 July 2007 01:46 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I don't know what those create/update procedure do, but I would try to write them so that they take the pl/sql table as input. If they are simple procedures you could use a FORALL bulk loop.

I would also try whether I could gather the related data along with my main cursor. You could -perhaps- write a join query gathering all necessary data so you eliminate the need to go and fetch again for each record of your cursor.

MHE
Re: converting FOR to FORALL [message #250733 is a reply to message #250726] Wed, 11 July 2007 02:03 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
I think u need to simply write forall instead of for that will do the thing u need
Re: converting FOR to FORALL [message #250735 is a reply to message #250733] Wed, 11 July 2007 02:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
darshanmeel wrote on Wed, 11 July 2007 09:03
I think u need to simply write forall instead of for that will do the thing u need

I think not.

Forall is NOT a loop construct as FOR is. It can only be used in conjunction with insert, update or delete.
Re: converting FOR to FORALL [message #250737 is a reply to message #250733] Wed, 11 July 2007 02:10 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
darshanmeel wrote on Wed, 11 July 2007 09:03
I think u need to simply write forall instead of for that will do the thing u need

I think you need to simply write 'you' instead of 'u'. Wink

MHE
Previous Topic: create type
Next Topic: LOBS TO VARCHAR2
Goto Forum:
  


Current Time: Thu Dec 08 08:50:17 CST 2016

Total time taken to generate the page: 0.09338 seconds