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

Aravindh wrote:
> Hi Daniel
>
> This is my old procedure :-
> 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 .
>

> 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

Sorry but I do not have time to read and debug your code as this is finals week. But I would suggest you add the LIMIT clause and tune it.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)