Cursor works sometimes 1

[llmclaughlin]

The below cursor works fine if I give it a date range of week to purge. When I give it a month of data to do it seems to hang up. Is there a way to streamline this?

PROCEDURE PurgeData(P_Date1 VARCHAR2,
P_Date2 VARCHAR2,
P_Cnt OUT INTEGER) IS



CURSOR L_Interlocks IS
SELECT *
FROM INTERLOCKS
WHERE TRUNC(DATE_RUN) >= TO_DATE(P_Date1,'MM/DD/YYYY')
AND TRUNC(DATE_RUN) <= TO_DATE(P_Date2,'MM/DD/YYYY')
ORDER BY INTERLOCK_ID;

CURSOR L_Results(C_ID IN NUMBER) IS
SELECT *
FROM TEST_RESULTS
WHERE INTERLOCK_ID = C_ID;

CURSOR L_Details(C_ResultID IN NUMBER) IS
SELECT *
FROM TEST_RESULTS_DETAILS
WHERE RESULT_ID = C_ResultID;

L_Cnt INTEGER := 0;

BEGIN


FOR R_Interlock IN L_Interlocks LOOP

INSERT INTO PURGED_INTERLOCKS (
INTERLOCK_ID, SERIAL_NUMBER, MODEL_NUMBER,
DATE_RUN, ORDER_ID, INTERLOCK_STATUS,
CCV_INTERLOCK_STATUS, TEST_NAME, PROCESSED)
VALUES (R_Interlock.Interlock_Id ,R_Interlock.Serial_Number ,R_Interlock.Model_Number ,R_Interlock.Date_Run
,R_Interlock.Order_Id ,R_Interlock.Interlock_Status ,R_Interlock.CCV_Interlock_Status
,R_Interlock.Test_Name ,R_Interlock.Processed);


FOR R_Results IN L_Results(R_Interlock.Interlock_Id) LOOP

INSERT INTO PURGED_TEST_RESULTS (
RESULT_ID, INTERLOCK_ID, PROGRAM_DESCRIPTION,
FAILURE_MESSAGE, OPERATOR, TEST_NUM,
STATION_ID, STATION_TYPE, DATE_TESTED,
TEST_STATUS, CYCLE_TIME)
VALUES (R_Results.Result_Id ,R_Interlock.Interlock_Id ,R_Results.Program_Description ,R_Results.Failure_Message
,R_Results.Operator ,R_Results.Test_Num ,R_Results.Station_Id
,R_Results.Station_Type ,R_Results.Date_Tested ,R_Results.Test_Status
,R_Results.Cycle_Time);

FOR R_Details IN L_Details(R_Results.Result_Id) LOOP

INSERT INTO PURGED_TEST_RESULTS_DETAILS (
DETAILS_ID, RESULT_ID, RESULT_NAME,
RESULT_VALUE, UNIT_MEASURE)
VALUES (R_Details.Details_id ,R_Results.Result_Id ,R_Details.Result_Name ,R_Details.Result_Value
,R_Details.Unit_Measure);

END LOOP;

DELETE FROM TEST_RESULTS
WHERE INTERLOCK_ID = R_Interlock.Interlock_Id;

DELETE FROM TEST_RESULTS_DETAILS
WHERE RESULT_ID = R_Results.Result_Id;

END LOOP;

DELETE FROM INTERLOCKS
WHERE INTERLOCK_ID = R_Interlock.Interlock_Id;

L_Cnt := L_Cnt + 1;

END LOOP;

P_Cnt := L_Cnt;

END PurgeData;

 

[LKBrwnDBA]

You could try using 'BULK COLLECT'/'FORALL' to process larger "chunks" of data.

Also try to avoid the loop within a loop within a loop!


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[llmclaughlin]

Changed to Bulk collect now need to know how do I get specific data for other tables?

PROCEDURE PurgeData(P_Date1 VARCHAR2,
P_Date2 VARCHAR2,
P_Cnt OUT INTEGER) IS


TYPE TInterlockTable IS TABLE OF INTERLOCKS%ROWTYPE;
TYPE TResultsTable IS TABLE OF TEST_RESULTS%ROWTYPE;
TYPE TDetailsTable IS TABLE OF TEST_RESULTS_DETAILS%ROWTYPE;

InterlockTable$ TInterlockTable;
ResultsTable$ TResultsTable;
DetailsTable$ TDetailsTable;

L_Cnt INTEGER := 0;
L_Max INTEGER := 0;
L_Date1 DATE;
L_Date2 DATE;

BEGIN

L_Date1 := TO_DATE(P_Date1,'MM/DD/YYYY HH24:MI:SS');
L_Date2 := TO_DATE(P_Date2,'MM/DD/YYYY HH24:MI:SS');

SELECT * BULK COLLECT INTO InterlockTable$
FROM INTERLOCKS
WHERE TRUNC(DATE_RUN) >= TO_DATE(P_Date1,'MM/DD/YYYY')
AND TRUNC(DATE_RUN) <= TO_DATE(P_Date2,'MM/DD/YYYY');

FORALL x IN InterlockTable$.First..InterlockTable$.Last
INSERT INTO PURGED_INTERLOCKS VALUES InterlockTable$(x);

--The above part works
--I need to know the interlock_id of all the rows the above bulk collect retrieved
--to use in a where clause for the next table

SELECT * BULK COLLECT INTO ResultsTable$
FROM TEST_RESULTS
WHERE INTERLOCK_ID IN ?????



SELECT NVL(MAX(PURGE_ID),1)
INTO L_Max
FROM PURGED_DATES;

INSERT INTO PURGED_DATES
VALUES(L_Max, L_Date1, L_Date2);

COMMIT;

P_Cnt := L_Cnt;

END PurgeData;

Louie

 

[LKBrwnDBA]

Don't know if it will work, but I would try:

-- First change this:
TYPE TInterlockTable IS TABLE OF INTERLOCKS%ROWTYPE
     [red]INDEX BY PLS_INTEGER;[/red]
TYPE TResultsTable IS TABLE OF TEST_RESULTS%ROWTYPE
     [red]INDEX BY PLS_INTEGER;[/red]
TYPE TDetailsTable IS TABLE OF TEST_RESULTS_DETAILS%ROWTYPE
     [red]INDEX BY PLS_INTEGER;[/red]
-- Add:[red]
X PLS_INTEGER;              
I PLS_INTEGER;[/red]
-- Etc --
 
FORALL i IN InterlockTable$.First..InterlockTable$.Last 
SELECT * INTO ResultsTable$(i)
         FROM TEST_RESULTS
        WHERE INTERLOCK_ID = InterlockTable$(i).Interlock_Id;

-- Etc --

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[llmclaughlin]

That got me closer, but still not getting the records on the second table.

Louie

 

[LKBrwnDBA]

OK, something like this, just add the DELETE's:

-- Etc --
        FOR i IN InterlockTable$.First..InterlockTable$.Last 
                SELECT *
                  INTO resultstable$ (i)
                  FROM test_results
                 WHERE interlock_id = interlocktable$ (i).interlock_id;

        FOR i IN ResultsTable$.First..ResultsTable$.Last 
                INSERT INTO purged_test_results
                     VALUES resultstable$ (i);

        FOR i IN ResultsTable$.First..ResultsTable$.Last 
                SELECT *
                  INTO detailstable$ (i)
                  FROM test_results_detail
                 WHERE result_id = resultstable$ (i).result_id;

        FOR i IN ResultsTable$.First..ResultsTable$.Last 
                INSERT INTO purged_test_results_details
                     VALUES detailstable$ (i);
-- Etc --

PS: There may be a still more efficient way by replacing the:
FORALL..SELECT followed by FORALL..INSERT with
FORALL..INSERT INTO .. SELECT.. construct.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Oooops, typo:

Where it says [red]FOR[/red] it must be [red]FORALL[/red]


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb