We have 60 Procedures, divided them into 3 batches(100,200,200) , I am calling the 3 batches parallelly and procedure inside each batch will run sequentially.
Also, maintaining audit log tables, one to store summary at batch level, with start and end times, another at procedure level for each batch to store start and end times.
When i kill any session related to procedure inside a batch due to some reason, it shouldn't come out of the batch, it has to execute the next procedure within the batch.
I have written the below code to handle this requirement, but when i kill the session of a procedure it is coming out of the batch.
Could anyone please let me know how to handle this requirement in the best possible way. I tried multiple options but not able to continue execution other procedures in the batch when i killed one session.
Required Tables Insert/Create Scripts:
Table which has all the procedures that need to be executed along with batch id (aka list no) and order no.
CREATE TABLE "ETL_LIST"
( "LIST_NO" NUMBER NOT NULL ENABLE,
"ORDER_NO" NUMBER NOT NULL ENABLE,
"ETL_NAME" VARCHAR2(61 BYTE) NOT NULL ENABLE,
"PARAMS" VARCHAR2(2000 BYTE),
"ACTIVE_YN" VARCHAR2(1 BYTE) DEFAULT 'Y' NOT NULL ENABLE
);
Insert into ETL_LIST (LIST_NO,ORDER_NO,ETL_NAME,PARAMS,ACTIVE_YN) values (100,1,'SP1',null,'Y');
Insert into ETL_LIST (LIST_NO,ORDER_NO,ETL_NAME,PARAMS,ACTIVE_YN) values (100,2,'SP2',null,'Y');
Insert into ETL_LIST (LIST_NO,ORDER_NO,ETL_NAME,PARAMS,ACTIVE_YN) values (200,1,'SP3',null,'Y');
Insert into ETL_LIST (LIST_NO,ORDER_NO,ETL_NAME,PARAMS,ACTIVE_YN) values (200,2,'SP4',null,'Y');
Insert into ETL_LIST (LIST_NO,ORDER_NO,ETL_NAME,PARAMS,ACTIVE_YN) values (300,1,'SP5',null,'Y');
Insert into ETL_LIST (LIST_NO,ORDER_NO,ETL_NAME,PARAMS,ACTIVE_YN) values (300,2,'SP6',null,'Y');
commit;
Audit Log Tables Scripts:
Table to store batch start and end time
CREATE TABLE "RUN_PROCESS"
( "RUN_NO" NUMBER NOT NULL ENABLE,
"LIST_NO" NUMBER NOT NULL ENABLE,
"CREATE_DTS" TIMESTAMP (6),
"START_DTS" TIMESTAMP (6),
"END_DTS" TIMESTAMP (6),
"RUN_DURATION" NUMBER,
"RUN_STATUS" VARCHAR2(10 BYTE),
"RUN_RESULT" VARCHAR2(1 BYTE)
);
Table to store individual procedures within batch start and end time
CREATE TABLE "ETL_PROCESS"
( "RUN_NO" NUMBER NOT NULL ENABLE,
"ORDER_NO" NUMBER NOT NULL ENABLE,
"ETL_NAME" VARCHAR2(61 BYTE) NOT NULL ENABLE,
"START_DTS" TIMESTAMP (6),
"END_DTS" TIMESTAMP (6),
"ETL_DURATION" NUMBER,
"ETL_STATUS" VARCHAR2(10 BYTE) NOT NULL ENABLE,
"ETL_RESULT" VARCHAR2(1 BYTE),
"RECORDS_LOADED" NUMBER
) ;
Audit Procedure: To Log Batch & Procedure Start and end Times
create or replace PROCEDURE BI_SCH_AUDIT_LOG
(P_EXEC_TYPE IN VARCHAR2,
P_RUN_ID IN NUMBER,
P_LIST_NO IN NUMBER,
P_ORDER_NO IN NUMBER,
P_ETL_NAME IN VARCHAR2,
P_CREATE_DTS IN DATE)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
P_EXEC_IND VARCHAR2(10);
P_ERROR VARCHAR2(32767);
V_START_DTS DATE;
V_CREATE_DTS DATE;
BEGIN
P_EXEC_IND:=P_EXEC_TYPE;
IF P_EXEC_IND ='RS' THEN
INSERT INTO RUN_PROCESS (RUN_NO,
LIST_NO,
CREATE_DTS,
START_DTS,
RUN_STATUS)
VALUES (
P_RUN_ID,
P_LIST_NO,
SYSDATE,
SYSDATE,
'RUNNING'
);
COMMIT;
END IF;
IF P_EXEC_IND ='LS' THEN
INSERT INTO ETL_PROCESS (
RUN_NO,
ORDER_NO,
ETL_NAME,
START_DTS,
ETL_STATUS)
VALUES (
P_RUN_ID,
P_ORDER_NO,
P_ETL_NAME,
SYSDATE,
'RUNNING'
);
COMMIT;
END IF;
IF P_EXEC_IND ='LC' THEN
BEGIN
SELECT START_DTS INTO V_START_DTS
FROM ETL_PROCESS
WHERE RUN_NO = P_RUN_ID AND ORDER_NO = P_ORDER_NO;
END;
UPDATE ETL_PROCESS
SET
END_DTS = SYSDATE,
ETL_DURATION = ROUND(SYSDATE-V_START_DTS,2),
ETL_STATUS = 'COMPLETE',
ETL_RESULT = 'S'
WHERE RUN_NO = P_RUN_ID AND ORDER_NO = P_ORDER_NO;
COMMIT;
END IF;
IF P_EXEC_IND ='RC' THEN
BEGIN
SELECT CREATE_DTS INTO V_CREATE_DTS
FROM RUN_PROCESS
WHERE RUN_NO = P_RUN_ID AND LIST_NO = P_LIST_NO;
END;
UPDATE RUN_PROCESS
SET
END_DTS = SYSDATE,
RUN_DURATION = ROUND(SYSDATE-V_CREATE_DTS,2),
RUN_STATUS = 'COMPLETE',
RUN_RESULT = 'S'
WHERE RUN_NO = P_RUN_ID AND LIST_NO = P_LIST_NO;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in BI_SCH_AUDIT_LOG procedure: '||SUBSTR(SQLERRM,1,175));
END WMS_BI_SCH_AUDIT_LOG;
Batch Calling Procedure: We can call this procedure with batchno as input, to run all procedures within that batch sequentially.
CREATE OR REPLACE PROCEDURE CE_SCH_JOB_EXEC(LISTNO IN NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
V_LIST_NO NUMBER:=LISTNO;
V_RUN_ID NUMBER;
V_CREATE_DT DATE;
P_ERROR VARCHAR2(32767);
BEGIN
V_CREATE_DT:=SYSDATE;
SELECT SEQ_RUN_NO.nextval INTO V_RUN_ID FROM DUAL;
DECLARE
CURSOR C_BATCH_LIST
IS
SELECT LIST_NO,ORDER_NO,ETL_NAME
FROM ETL_LIST
WHERE LIST_NO=V_LIST_NO
AND ACTIVE_YN='Y'
ORDER BY ORDER_NO;
BEGIN
BI_SCH_AUDIT_LOG(P_LIST_NO =>V_LIST_NO,
P_ORDER_NO =>NULL,
P_CREATE_DTS =>NULL,
P_ETL_NAME =>NULL,
P_EXEC_TYPE =>'RS',
P_RUN_ID => V_RUN_ID);
FOR R_BATCH_LIST IN C_BATCH_LIST
LOOP
BI_SCH_AUDIT_LOG(P_LIST_NO => R_BATCH_LIST.LIST_NO,
P_ORDER_NO => R_BATCH_LIST.ORDER_NO,
P_CREATE_DTS => V_CREATE_DT,
P_ETL_NAME => R_BATCH_LIST.ETL_NAME,
P_EXEC_TYPE => 'LS',
P_RUN_ID => V_RUN_ID);
begin
execute immediate 'begin ' || R_BATCH_LIST.ETL_NAME || '; end;';
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in BI_SCH_AUDIT_LOG procedure: '||SUBSTR(SQLERRM,1,175));
end;
BI_SCH_AUDIT_LOG(P_LIST_NO => R_BATCH_LIST.LIST_NO,
P_ORDER_NO => R_BATCH_LIST.ORDER_NO,
P_CREATE_DTS => V_CREATE_DT,
P_ETL_NAME => R_BATCH_LIST.ETL_NAME,
P_EXEC_TYPE => 'LC',
P_RUN_ID => V_RUN_ID);
END LOOP;
BI_SCH_AUDIT_LOG(P_LIST_NO =>V_LIST_NO,
P_ORDER_NO =>NULL,
P_CREATE_DTS =>NULL,
P_ETL_NAME =>NULL,
P_EXEC_TYPE =>'RC',
P_RUN_ID => V_RUN_ID);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in BI_SCH_AUDIT_LOG procedure: '||SUBSTR(SQLERRM,1,175));
END;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in BI_SCH_AUDIT_LOG procedure: '||SUBSTR(SQLERRM,1,175));
END;
Calling Batches Parallelly: Call the three batches parallelly from different sql window or using different dbms job with same start time.
BEGIN
CE_SCH_JOB_EXEC(100);
END;
BEGIN
CE_SCH_JOB_EXEC(200);
END;
BEGIN
CE_SCH_JOB_EXEC(300);
END;