Re: BULK COLLECT - Can it be implemented for a few rows .

From: Aravindh <knaravindh81_at_gmail.com>
Date: 6 Dec 2005 09:30:01 -0800
Message-ID: <1133890200.999990.183310_at_g49g2000cwa.googlegroups.com>

Hi Daniel

This is my old procedure :-
[Quoted] ORACLE 9i is the version number of oracle we are using.

This old proc was the one implemented using a lot of CURSORS . Though it was very efficient the data base reads was very high as told by the STATS PACK REPORT . So we designed a NEW PROC using BULK COLLECT . CREATE OR REPLACE PROCEDURE JP_CASE_HISTORY_INC_OLD AS VTRANSACTION_FLAG VARCHAR2(2);
VASSIGNED_TO VARCHAR2(50);
VASSIGNED_TO_PG VARCHAR2(50);
VSTATUS VARCHAR(50);
VASSIGNED_TO_OLD VARCHAR2(50);
VASSIGNED_TO_PG_OLD VARCHAR2(50);
VSTATUS_OLD VARCHAR(50);
VASSIGNED_TO_FLAG VARCHAR2(2);
VASSIGNED_TO_PG_FLAG VARCHAR2(2);
VSTATUS_FLAG VARCHAR(2);
VCTR NUMBER(2); CURSOR CUR1(LASTRUN DATE) IS SELECT DISTINCT CASE_ID FROM PS_RC_ACTION_HIST WHERE
ROW_LASTMANT_DTTM > LASTRUN ; CURSOR CUR2(VCASE_ID NUMBER,LASTRUN DATE) IS SELECT DISTINCT ROW_LASTMANT_DTTM,ROW_LASTMANT_OPRID FROM PS_RC_ACTION_HIST WHERE
CASE_ID = VCASE_ID
AND
EVENTNAME IN
('AssignedToChanged','AssignedToProviderGroup','AssignedToProviderGroupChanged','JP_AssignedToProviderGroupAdd','Status
Changed')
AND
ROW_LASTMANT_DTTM > LASTRUN
ORDER BY ROW_LASTMANT_DTTM ,ROW_LASTMANT_OPRID ; CURSOR CUR3(VCASE_ID NUMBER,VROW_LASTMANT_DTTM DATE,VROW_LASTMANT_OPRID VARCHAR2)
IS SELECT EVENTNAME, NEW_VALUE,OLD_VALUE,DESCR100 FROM PS_RC_ACTION_HIST
WHERE CASE_ID = VCASE_ID AND ROW_LASTMANT_DTTM = VROW_LASTMANT_DTTM AND ROW_LASTMANT_OPRID = VROW_LASTMANT_OPRID AND EVENTNAME IN
('AssignedToChanged','AssignedToProviderGroup','AssignedToProviderGroupChanged','JP_AssignedToProviderGroupAdd','Status
Changed');

CURSOR CUR4(NCASE_ID NUMBER) IS SELECT
A.ASSIGNED_TO_OLD,A.ASSIGNED_TO_NEW,A.ASSIGNED_TO_PG_OLD, A.ASSIGNED_TO_PG_NEW,A.STATUS_OLD, A.STATUS_NEW, A.ROW_LASTMANT_DTTM AS "LDTTM"
FROM JP_HISTORY_TBL_OLD A WHERE A.CASE_ID = NCASE_ID AND A.ROW_LASTMANT_DTTM =
(SELECT MAX(B.ROW_LASTMANT_DTTM) FROM JP_HISTORY_TBL_OLD B WHERE
A.CASE_ID = B.CASE_ID); CURSOR CUR5 IS SELECT
DECODE(TRIM(MAX(ROW_LASTMANT_DTTM)),Null,to_date('01-Jan-1900 00:00:00','dd-Mon-YYYY HH24:MI:SS'), MAX(ROW_LASTMANT_DTTM)) AS "DTTM" FROM JP_HISTORY_TBL_OLD; CURSOR CUR6(NCASE_ID NUMBER, VROW_LASTMANT_DTTM DATE) IS SELECT /*+ FIRST_ROWS */ ROUND(( DECODE(TRIM((SELECT MIN(B.ROW_LASTMANT_DTTM)FROM JP_HISTORY_TBL_OLD B WHERE A.CASE_ID = B.CASE_ID
AND B.ROW_LASTMANT_DTTM > A.ROW_LASTMANT_DTTM)),NULL,SYSDATE,(SELECT MIN(B.ROW_LASTMANT_DTTM)FROM
JP_HISTORY_TBL_OLD B WHERE A.CASE_ID = B.CASE_ID AND B.ROW_LASTMANT_DTTM > A.ROW_LASTMANT_DTTM)) - A.ROW_LASTMANT_DTTM ) *(24 *60),2)
AS "DURATION" FROM JP_HISTORY_TBL_OLD A WHERE A.CASE_ID = NCASE_ID AND A.ROW_LASTMANT_DTTM = VROW_LASTMANT_DTTM; CURSOR CUR7 IS SELECT /*+ FIRST_ROWS */ CASE_ID, ROW_LASTMANT_DTTM FROM JP_HISTORY_TBL_OLD WHERE DURATION = 0 ORDER BY CASE_ID, ROW_LASTMANT_DTTM; CURSOR CUR8(NCASE_ID NUMBER) IS SELECT /*+ FIRST_ROWS */ JP_ORIG_PRVDR_GRP, PERSON_ID FROM PS_RC_CASE RC, PSOPRALIAS OP WHERE RC.ROW_ADDED_OPRID = OP.OPRID AND OP.OPRALIASTYPE = 'PER' AND RC.CASE_ID = NCASE_ID;

CUR8_VAR CUR8%ROWTYPE;
CUR4_VAL CUR4%ROWTYPE;
CUR5_VAL CUR5%ROWTYPE;

BEGIN INSERT INTO JP_REFRESH_ACTION VALUES('REPDB',SYSDATE); COMMIT; OPEN CUR5;
FETCH CUR5 INTO CUR5_VAL;
--DBMS_OUTPUT.PUT_LINE(CUR5_VAL.DTTM);
FOR I IN CUR1(CUR5_VAL.DTTM) LOOP
OPEN CUR4(I.CASE_ID);
FETCH CUR4 INTO CUR4_VAL; IF CUR4%NOTFOUND THEN OPEN CUR8(I.CASE_ID);
FETCH CUR8 INTO CUR8_VAR; VCTR :=0;
VASSIGNED_TO_OLD := CUR8_VAR.PERSON_ID;
VASSIGNED_TO_PG_OLD := CUR8_VAR.JP_ORIG_PRVDR_GRP; VSTATUS_OLD := ' ';
VASSIGNED_TO := ' ';
VASSIGNED_TO_PG := ' ';
VSTATUS := ' ';
VTRANSACTION_FLAG := 'A';
CLOSE CUR8;
ELSE VCTR :=1;
VASSIGNED_TO_OLD := CUR4_VAL.ASSIGNED_TO_OLD; VASSIGNED_TO_PG_OLD := CUR4_VAL.ASSIGNED_TO_PG_OLD; VSTATUS_OLD := CUR4_VAL.STATUS_OLD;
VASSIGNED_TO := CUR4_VAL.ASSIGNED_TO_NEW; VASSIGNED_TO_PG := CUR4_VAL.ASSIGNED_TO_PG_NEW ; VSTATUS := CUR4_VAL.STATUS_NEW;
VTRANSACTION_FLAG := 'C'; UPDATE JP_HISTORY_TBL_OLD SET DURATION = 0 WHERE CASE_ID = I.CASE_ID AND ROW_LASTMANT_DTTM = CUR4_VAL.LDTTM;
COMMIT; END IF;
close CUR4;

FOR J IN CUR2(I.CASE_ID,CUR5_VAL.DTTM) LOOP VASSIGNED_TO_FLAG := 'N';
VASSIGNED_TO_PG_FLAG := 'N';
VSTATUS_FLAG := 'N'; FOR K IN CUR3(I.CASE_ID,J.ROW_LASTMANT_DTTM,J.ROW_LASTMANT_OPRID) LOOP --dbms_output.put_line(k.eventname);
IF VCTR = 0 THEN
VCTR := 1;
VASSIGNED_TO_FLAG := 'Y';
VASSIGNED_TO_PG_FLAG := 'Y';
VSTATUS_FLAG := 'Y';
END IF; IF ( K.EVENTNAME = 'AssignedToChanged' AND UPPER(K.DESCR100) NOT LIKE '%WORK%') THEN
VASSIGNED_TO_FLAG := 'Y';
VASSIGNED_TO := K.NEW_VALUE;
IF VTRANSACTION_FLAG <> 'A' THEN
VASSIGNED_TO_OLD := K.OLD_VALUE;
END IF;
END IF;
IF ((K.EVENTNAME = 'AssignedToProviderGroup' OR K.EVENTNAME = 'AssignedToProviderGroupChanged' OR K.EVENTNAME = 'JP_AssignedToProviderGroupAdd')
AND UPPER(K.DESCR100) NOT LIKE '%WORK%' ) THEN
VASSIGNED_TO_PG_FLAG := 'Y';
VASSIGNED_TO_PG := K.NEW_VALUE;
IF VTRANSACTION_FLAG <> 'A' THEN
VASSIGNED_TO_PG_OLD := K.OLD_VALUE;
END IF;
END IF;
IF K.EVENTNAME = 'Status Changed' THEN
VSTATUS_FLAG := 'Y';
VSTATUS := K.NEW_VALUE ;
VSTATUS_OLD := K.OLD_VALUE;
END IF; END LOOP ; IF (VASSIGNED_TO_FLAG ='Y' OR VASSIGNED_TO_PG_FLAG = 'Y' OR VSTATUS_FLAG = 'Y') THEN INSERT INTO JP_HISTORY_TBL_OLD
VALUES(I.CASE_ID,J.ROW_LASTMANT_OPRID,J.ROW_LASTMANT_DTTM,VASSIGNED_TO_OLD ,VASSIGNED_TO,VASSIGNED_TO_PG_OLD ,VASSIGNED_TO_PG, VSTATUS_OLD,VSTATUS,VASSIGNED_TO_FLAG,VASSIGNED_TO_PG_FLAG,VSTATUS_FLAG,VTRANSACTION_FLAG,0); VTRANSACTION_FLAG := 'C'; END IF; END LOOP;
COMMIT;
END LOOP;
CLOSE CUR5; FOR K IN CUR7 LOOP

 	 FOR L IN CUR6(K.CASE_ID, K.ROW_LASTMANT_DTTM) LOOP
 	 	 UPDATE /*+ APPEND */ JP_HISTORY_TBL_OLD NOLOGGING SET DURATION =
L.DURATION WHERE CASE_ID  = K.CASE_ID
 	 	 AND ROW_LASTMANT_DTTM = K.ROW_LASTMANT_DTTM;
 	 END LOOP;

END LOOP; COMMIT; UPDATE JP_HISTORY_TBL_OLD
SET ASSIGNED_TO_PG_OLD = ASSIGNED_TO_PG_NEW WHERE ASSIGNED_TO_PG_FLAG = 'N'; UPDATE JP_HISTORY_TBL_OLD
SET ASSIGNED_TO_OLD = ASSIGNED_TO_NEW
WHERE ASSIGNED_TO_FLAG = 'N'; UPDATE JP_HISTORY_TBL_OLD
SET STATUS_OLD = STATUS_NEW
WHERE STATUS_FLAG = 'N'; UPDATE JP_HISTORY_TBL_OLD
SET ASSIGNED_TO_PG_OLD = ' '
WHERE ASSIGNED_TO_PG_OLD IS NULL ; UPDATE JP_HISTORY_TBL_OLD
SET ASSIGNED_TO_PG_NEW = ' '
WHERE ASSIGNED_TO_PG_NEW IS NULL ; UPDATE JP_HISTORY_TBL_OLD
SET ASSIGNED_TO_NEW = ' '
WHERE ASSIGNED_TO_NEW IS NULL ; UPDATE JP_HISTORY_TBL_OLD
SET ASSIGNED_TO_OLD = ' '
WHERE ASSIGNED_TO_OLD IS NULL ; UPDATE JP_HISTORY_TBL_OLD
SET STATUS_NEW = ' '
WHERE STATUS_NEW IS NULL ; UPDATE JP_HISTORY_TBL_OLD
SET STATUS_OLD = ' '
WHERE STATUS_OLD IS NULL ; COMMIT; INSERT INTO JP_REFRESH_ACTION VALUES('REPDB',SYSDATE); COMMIT; END;
/

[Quoted] The new procedure using BULK COLLECT does the same functionality

CREATE OR REPLACE PROCEDURE JP_CASE_HISTORY_INCREMENTAL AS TYPE CASEIDTYP IS TABLE OF SYSADM.PS_RC_ACTION_HIST.CASE_ID%TYPE; ORG_CASEIDVAL CASEIDTYP;
TYPE MANTDTTMTYP IS TABLE OF
SYSADM.PS_RC_ACTION_HIST.ROW_LASTMANT_DTTM%TYPE; TYPE EVNTTYP IS TABLE OF SYSADM.PS_RC_ACTION_HIST.EVENTNAME%TYPE; TYPE OLDVALTYP IS TABLE OF SYSADM.PS_RC_ACTION_HIST.OLD_VALUE%TYPE; TYPE NEWVALTYP IS TABLE OF SYSADM.PS_RC_ACTION_HIST.NEW_VALUE%TYPE; TYPE MANTOPRIDTYP IS TABLE OF
SYSADM.PS_RC_ACTION_HIST.ROW_LASTMANT_OPRID%TYPE; TYPE HISTORY_TBL IS TABLE OF SYSADM.JP_HISTORY_TBL%ROWTYPE; TYPE HISTORY_CASE_ID IS TABLE OF SYSADM.JP_HISTORY_TBL.CASE_ID%TYPE INDEX BY BINARY_INTEGER;
TYPE HISTORY_DATE IS TABLE OF DATE INDEX BY BINARY_INTEGER; TYPE HISTORY_DUR IS TABLE OF SYSADM.JP_HISTORY_TBL.DURATION%TYPE INDEX BY BINARY_INTEGER;

HISTORY_SEL HISTORY_TBL :=HISTORY_TBL() ;
HISTORY_LAST_ROW HISTORY_TBL:=HISTORY_TBL() ;
HISTORY_PREV_ROW HISTORY_TBL:=HISTORY_TBL() ;
HISTORY_DATE_ROW HISTORY_DATE;
HISTORY_CASE_ID_ROW HISTORY_CASE_ID;
UPD_HIS_CASE_ID_ROW HISTORY_CASE_ID;
UPD_HIS_DUR HISTORY_DUR;
UPD_HIS_DATE_ROW HISTORY_DATE;

CLO_DTTM DATE;

ORG_CREPG OLDVALTYP;
ORG_CREATOR OLDVALTYP;
ACT_CASEIDVAL CASEIDTYP;
ACT_MANTDTTMVAL MANTDTTMTYP;
ACT_EVNTVAL EVNTTYP;
ACT_OLDVAL OLDVALTYP;
ACT_NEWVAL NEWVALTYP;
ACT_OPRIDVAL MANTOPRIDTYP;

PREV_CASEID NUMBER(15); PREV_DATE DATE;
CURR_DATE DATE;
MAX_DATE DATE;
VASSIGNED_TO VARCHAR2(50);
VASSIGNED_TO_PG VARCHAR2(50);
VSTATUS VARCHAR(50);
VASSIGNED_TO_OLD VARCHAR2(50);
VASSIGNED_TO_PG_OLD VARCHAR2(50);
VSTATUS_OLD VARCHAR(50);
VASSIGNED_TO_FLAG VARCHAR2(2);
VASSIGNED_TO_PG_FLAG VARCHAR2(2);
VSTATUS_FLAG VARCHAR(2);
CURROW NUMBER(5);
CREROW NUMBER(10);
ASSIGN_FLAG BOOLEAN; BULK_ERRORS EXCEPTION;
PRAGMA EXCEPTION_INIT(BULK_ERRORS,-24381); ERRORS_COUNT NUMBER; BEGIN CURR_DATE := SYSDATE; SELECT
DECODE(TRIM(MAX(ROW_LASTMANT_DTTM)),NULL,TO_DATE('01-Jan-1900 00:00:00','dd-Mon-YYYY HH24:MI:SS'),
MAX(ROW_LASTMANT_DTTM)) INTO MAX_DATE
FROM SYSADM.JP_HISTORY_TBL; SELECT CASE_ID , ROW_LASTMANT_DTTM ,EVENTNAME,OLD_VALUE, NEW_VALUE , ROW_LASTMANT_OPRID
BULK COLLECT INTO

ACT_CASEIDVAL ,
ACT_MANTDTTMVAL,
ACT_EVNTVAL,
ACT_OLDVAL,
ACT_NEWVAL,
ACT_OPRIDVAL

FROM SYSADM.PS_RC_ACTION_HIST WHERE EVENTNAME IN
('AssignedToChanged','AssignedToProviderGroup','AssignedToProviderGroupChanged','JP_AssignedToProviderGroupAdd',
'Status Changed') AND UPPER(DESCR100) NOT LIKE '%WORK%' AND ROW_LASTMANT_DTTM > MAX_DATE AND
ROW_LASTMANT_DTTM < CURR_DATE
ORDER BY CASE_ID, ROW_LASTMANT_DTTM, EVENTNAME; SELECT A.CASE_ID, A.ROW_LASTMANT_DTTM
BULK COLLECT INTO HISTORY_CASE_ID_ROW, HISTORY_DATE_ROW FROM
SYSADM.JP_HISTORY_TBL A WHERE
A.ROW_LASTMANT_DTTM = (SELECT MAX(A1.ROW_LASTMANT_DTTM) FROM SYSADM.JP_HISTORY_TBL A1
WHERE A.CASE_ID = A1.CASE_ID)
AND
A.STATUS_NEW NOT IN ('Cancelled','Closed');

SELECT RC.CASE_ID, RC.JP_ORIG_PRVDR_GRP, OP.PERSON_ID BULK COLLECT INTO ORG_CASEIDVAL,ORG_CREPG,ORG_CREATOR FROM SYSADM.PS_RC_CASE RC, SYSADM.PSOPRALIAS OP WHERE RC.ROW_ADDED_OPRID = OP.OPRID AND OP.OPRALIASTYPE = 'PER' AND RC.ROW_ADDED_DTTM > MAX_DATE
AND RC.ROW_ADDED_DTTM < CURR_DATE ORDER BY RC.CASE_ID;

SELECT

A.CASE_ID,A.ROW_LASTMANT_OPRID,A.ROW_LASTMANT_DTTM,A.ASSIGNED_TO_OLD,A.ASSIGNED_TO_NEW,
A.ASSIGNED_TO_PG_OLD,A.ASSIGNED_TO_PG_NEW,A.STATUS_OLD,A.STATUS_NEW,A.ASSIGNED_TO_FLAG,
A.ASSIGNED_TO_PG_FLAG,A.STATUS_FLAG,A.TRANSACTION_FLAG,A.DURATION,A.SYNC_DTTM

BULK COLLECT INTO HISTORY_PREV_ROW
FROM SYSADM.JP_HISTORY_TBL A
WHERE
A.CASE_ID IN (SELECT CASE_ID
FROM SYSADM.PS_RC_ACTION_HIST WHERE
EVENTNAME IN
('AssignedToChanged','AssignedToProviderGroup','AssignedToProviderGroupChanged','JP_AssignedToProviderGroupAdd','Status
Changed')
AND UPPER(DESCR100) NOT LIKE '%WORK%' AND ROW_LASTMANT_DTTM > MAX_DATE AND
ROW_LASTMANT_DTTM < CURR_DATE)
AND
A.ROW_LASTMANT_DTTM =
(SELECT MAX(B.ROW_LASTMANT_DTTM) FROM SYSADM.JP_HISTORY_TBL B
WHERE A.CASE_ID = B.CASE_ID)
ORDER BY A.CASE_ID; FORALL I IN 1 .. HISTORY_CASE_ID_ROW.COUNT SAVE EXCEPTIONS UPDATE SYSADM.JP_HISTORY_TBL SET SYNC_DTTM=CURR_DATE WHERE CASE_ID = HISTORY_CASE_ID_ROW(I) AND
ROW_LASTMANT_DTTM = HISTORY_DATE_ROW(I); COMMIT; PREV_CASEID :=0;
PREV_DATE := NULL;
CURROW :=1;
CREROW :=1; FOR I IN 1 .. ACT_CASEIDVAL.COUNT LOOP IF PREV_CASEID <> ACT_CASEIDVAL(I) OR PREV_DATE <> ACT_MANTDTTMVAL(I) THEN
ASSIGN_FLAG:= TRUE;
HISTORY_SEL.EXTEND;
END IF; IF PREV_CASEID <> ACT_CASEIDVAL(I) THEN

IF HISTORY_PREV_ROW.LAST >= CURROW AND HISTORY_PREV_ROW(CURROW).CASE_ID = ACT_CASEIDVAL(I) THEN

  	       		HISTORY_SEL(HISTORY_SEL.LAST).TRANSACTION_FLAG :='C';
		    	VASSIGNED_TO := HISTORY_PREV_ROW(CURROW).ASSIGNED_TO_NEW;
		    	VASSIGNED_TO_PG := HISTORY_PREV_ROW(CURROW).ASSIGNED_TO_PG_NEW;
		        VSTATUS := HISTORY_PREV_ROW(CURROW).STATUS_NEW;
		    	VASSIGNED_TO_OLD := HISTORY_PREV_ROW(CURROW).ASSIGNED_TO_OLD;
		    	VASSIGNED_TO_PG_OLD :=
HISTORY_PREV_ROW(CURROW).ASSIGNED_TO_PG_OLD;
		    	VSTATUS_OLD := HISTORY_PREV_ROW(CURROW).STATUS_OLD;
		    	VASSIGNED_TO_FLAG := 'N';
			    VASSIGNED_TO_PG_FLAG := 'N';
	    		VSTATUS_FLAG := 'N';
				VSTATUS_OLD := VSTATUS;
				VASSIGNED_TO_OLD := VASSIGNED_TO;
				VASSIGNED_TO_PG_OLD :=VASSIGNED_TO_PG;
     			CURROW := CURROW+1;
         	ELSE
		    HISTORY_SEL(HISTORY_SEL.LAST).TRANSACTION_FLAG :='A';
		    VASSIGNED_TO_FLAG := 'Y';
		    VASSIGNED_TO_PG_FLAG := 'Y';
		    VSTATUS_FLAG := 'Y';
		    VASSIGNED_TO := ' ';
		    VASSIGNED_TO_PG := ' ';
			<<DATA_ISSUE>>
		    IF ORG_CASEIDVAL(CREROW) = ACT_CASEIDVAL(I) THEN
		        VASSIGNED_TO_OLD := ORG_CREATOR(CREROW);
		        VASSIGNED_TO_PG_OLD := ORG_CREPG(CREROW);
     			CREROW := CREROW +1;
		    ELSE
			    IF ORG_CASEIDVAL(CREROW) < ACT_CASEIDVAL(I) THEN
				   CREROW := CREROW+1;
				   IF CREROW <= ORG_CASEIDVAL.LAST THEN
				   	  GOTO DATA_ISSUE;
				   END IF;
				END IF;
		      	VASSIGNED_TO_OLD := ' ';
		        VASSIGNED_TO_PG_OLD := ' ';
		    END IF;
		    VSTATUS_OLD := ' ';
                END IF;

ELSE
IF PREV_DATE <> ACT_MANTDTTMVAL(I) THEN

		HISTORY_SEL(HISTORY_SEL.LAST).TRANSACTION_FLAG :='C';
		VASSIGNED_TO_FLAG := 'N';
		VASSIGNED_TO_PG_FLAG := 'N';
		VSTATUS_FLAG := 'N';
		VSTATUS_OLD := VSTATUS;
		VASSIGNED_TO_OLD := VASSIGNED_TO;
		VASSIGNED_TO_PG_OLD :=VASSIGNED_TO_PG;

END IF;
END IF; IF ACT_EVNTVAL(I) ='AssignedToChanged' THEN

   ASSIGN_FLAG:= FALSE;
IF HISTORY_SEL(HISTORY_SEL.LAST).TRANSACTION_FLAG='A' THEN    IF TRIM(ACT_NEWVAL(I)) IS NULL THEN
   VASSIGNED_TO := ' ';
   ELSE
   VASSIGNED_TO :=ACT_NEWVAL(I);
   END IF;
   VASSIGNED_TO_FLAG := 'Y';
ELSE
   IF TRIM(ACT_NEWVAL(I)) IS NULL THEN
   VASSIGNED_TO :=' ';
   ELSE
   VASSIGNED_TO :=ACT_NEWVAL(I);
   END IF;
   VASSIGNED_TO_FLAG := 'Y';    IF TRIM(ACT_OLDVAL(I)) IS NULL THEN
   VASSIGNED_TO_OLD := ' ';
   ELSE
   VASSIGNED_TO_OLD := ACT_OLDVAL(I);
   END IF;
END IF;
END IF; IF ACT_EVNTVAL(I)='AssignedToProviderGroup' OR ACT_EVNTVAL(I) ='AssignedToProviderGroupChanged' OR ACT_EVNTVAL(I) ='JP_AssignedToProviderGroupAdd' THEN IF HISTORY_SEL(HISTORY_SEL.LAST).TRANSACTION_FLAG='A' THEN    VASSIGNED_TO_PG :=ACT_NEWVAL(I);
   VASSIGNED_TO_PG_FLAG := 'Y';
ELSE    IF TRIM(ACT_NEWVAL(I)) IS NULL THEN
   VASSIGNED_TO_PG :=' ';
   ELSE
   VASSIGNED_TO_PG :=ACT_NEWVAL(I);
   END IF;    VASSIGNED_TO_PG_FLAG := 'Y';    IF TRIM(ACT_OLDVAL(I)) IS NULL THEN
   VASSIGNED_TO_PG_OLD := ' ';
   ELSE
   VASSIGNED_TO_PG_OLD := ACT_OLDVAL(I);    END IF;
END IF;
END IF; IF ACT_EVNTVAL(I) ='Status Changed' THEN IF HISTORY_SEL(HISTORY_SEL.LAST).TRANSACTION_FLAG='A' THEN    IF ACT_NEWVAL(I)='Draft' THEN

             HISTORY_SEL(HISTORY_SEL.LAST).TRANSACTION_FLAG:='D';    END IF;    IF TRIM(ACT_NEWVAL(I)) IS NULL THEN
   VSTATUS := ' ';
   ELSE
   VSTATUS :=ACT_NEWVAL(I);
   END IF;    VSTATUS_FLAG := 'Y';
ELSE
   IF ACT_OLDVAL(I)='Draft' THEN

             HISTORY_SEL(HISTORY_SEL.LAST).TRANSACTION_FLAG:='A';    END IF;    IF TRIM(ACT_NEWVAL(I)) IS NULL THEN
   VSTATUS := ' ';
   ELSE
   VSTATUS :=ACT_NEWVAL(I);
   END IF;
   VSTATUS_FLAG := 'Y';    IF TRIM(ACT_OLDVAL(I)) IS NULL THEN
   VSTATUS_OLD := ' ';
   ELSE
   VSTATUS_OLD := ACT_OLDVAL(I);
   END IF;
END IF;
END IF;

HISTORY_SEL(HISTORY_SEL.LAST).CASE_ID := ACT_CASEIDVAL(I);
HISTORY_SEL(HISTORY_SEL.LAST).ROW_LASTMANT_DTTM :=ACT_MANTDTTMVAL(I);
HISTORY_SEL(HISTORY_SEL.LAST).ROW_LASTMANT_OPRID :=ACT_OPRIDVAL(I);
HISTORY_SEL(HISTORY_SEL.LAST).ASSIGNED_TO_NEW:= VASSIGNED_TO;
HISTORY_SEL(HISTORY_SEL.LAST).ASSIGNED_TO_OLD:= VASSIGNED_TO_OLD;
HISTORY_SEL(HISTORY_SEL.LAST).ASSIGNED_TO_PG_NEW:=VASSIGNED_TO_PG;
HISTORY_SEL(HISTORY_SEL.LAST).ASSIGNED_TO_PG_OLD:=VASSIGNED_TO_PG_OLD;
HISTORY_SEL(HISTORY_SEL.LAST).STATUS_NEW:= VSTATUS ;
HISTORY_SEL(HISTORY_SEL.LAST).STATUS_OLD:=VSTATUS_OLD;

HISTORY_SEL(HISTORY_SEL.LAST).ASSIGNED_TO_FLAG:=VASSIGNED_TO_FLAG ; HISTORY_SEL(HISTORY_SEL.LAST).ASSIGNED_TO_PG_FLAG:=VASSIGNED_TO_PG_FLAG ;

HISTORY_SEL(HISTORY_SEL.LAST).STATUS_FLAG:=VSTATUS_FLAG ;
HISTORY_SEL(HISTORY_SEL.LAST).DURATION := 0;
HISTORY_SEL(HISTORY_SEL.LAST).SYNC_DTTM := CURR_DATE;

PREV_CASEID :=ACT_CASEIDVAL(I);
PREV_DATE := ACT_MANTDTTMVAL(I); END LOOP; FORALL I IN 1 .. HISTORY_SEL.COUNT
SAVE EXCEPTIONS
INSERT INTO SYSADM.JP_HISTORY_TBL
VALUES HISTORY_SEL(I); COMMIT; SELECT CASE_ID, ROW_LASTMANT_DTTM
BULK COLLECT INTO UPD_HIS_CASE_ID_ROW,UPD_HIS_DATE_ROW FROM SYSADM.JP_HISTORY_TBL
WHERE SYNC_DTTM=CURR_DATE
ORDER BY CASE_ID,ROW_LASTMANT_DTTM; FOR I IN 1 .. (UPD_HIS_CASE_ID_ROW.COUNT) LOOP
IF ( I < UPD_HIS_CASE_ID_ROW.COUNT AND UPD_HIS_CASE_ID_ROW(I) = UPD_HIS_CASE_ID_ROW(I+1) ) THEN
UPD_HIS_DUR(I) := ROUND((UPD_HIS_DATE_ROW(I+1) - UPD_HIS_DATE_ROW(I)) *24*60,2);
ELSE
UPD_HIS_DUR(I) := ROUND((CURR_DATE - UPD_HIS_DATE_ROW(I)) *24*60,2); END IF;
END LOOP; FORALL I IN 1 .. UPD_HIS_DUR.COUNT SAVE EXCEPTIONS UPDATE SYSADM.JP_HISTORY_TBL SET DURATION=UPD_HIS_DUR(I), SYNC_DTTM = CURR_DATE
WHERE CASE_ID = UPD_HIS_CASE_ID_ROW(I) AND ROW_LASTMANT_DTTM = UPD_HIS_DATE_ROW(I); COMMIT; UPDATE SYSADM.JP_HISTORY_TBL SET DURATION=0, SYNC_DTTM = CURR_DATE WHERE
STATUS_NEW IN ('Closed','Cancelled') AND DURATION <> 0;

COMMIT; INSERT INTO JP_REFRESH_ACTION VALUES('REPDB',SYSDATE); COMMIT; EXCEPTION WHEN OTHERS THEN
ERRORS_COUNT :=SQL%BULK_EXCEPTIONS.COUNT; DBMS_OUTPUT.PUT_LINE ('Errors : '||ERRORS_COUNT);

FOR INDX IN 1 .. ERRORS_COUNT LOOP
DBMS_OUTPUT.PUT_LINE(SQLERRM(SQL%BULK_EXCEPTIONS(INDX).ERROR_CODE)); END LOOP; END;
/

Though this BULK COLLECT Is running fast in the DEV environment it is running on a hap hazard basis in the Quality Acceptance region . Do we need to specifically allocate and deallocate memore for BULK COLLECT ? Can you please advise on this Received on Tue Dec 06 2005 - 18:30:01 CET

This message: [ Message body ]
Next message: DA Morgan: "Re: BULK COLLECT - Can it be implemented for a few rows ."
Previous message: karen2000: "Re: Procedure not populating data"
In reply to: DA Morgan: "Re: BULK COLLECT - Can it be implemented for a few rows ."
Next in thread: DA Morgan: "Re: BULK COLLECT - Can it be implemented for a few rows ."
Reply: DA Morgan: "Re: BULK COLLECT - Can it be implemented for a few rows ."

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message