When PL*SQL is used as an ETL tool (instead of OWB or another off the shelf tool) there is a need to audit the results of the ETL procedures to monitor the loading of the data warehouse.
In this blog a simple logging procedure is created and it is shown how to use it to log a test procedure.
Create the log table:
CREATE TABLE MAP_LOG
(ID NUMBER NOT NULL ENABLE
,MAP_NAME VARCHAR2(30)
,MAP_START_DATE DATE
,MAP_END_DATE DATE
,ELAPSED NUMBER
,NR_RECORDS NUMBER
,ERR_MSG VARCHAR2(2000)
,CONSTRAINT MAP_LOG_PK PRIMARY KEY (ID) ENABLE);
The log table contains an id as primary key, because the map name combined with the start date is in general not enough to make a primary key.
Further columns as the map (or procedure) name, start date, end date, elapsed time in seconds, the number of records deleted, inserted, merged or updated and the eventual error message.
Create the sequence:
CREATE SEQUENCE SEQ_MAP_LOG MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE;
Create the logging procedure:
CREATE OR REPLACE PROCEDURE P_LOG
(I_ID IN NUMBER
,I_MAP_NAME IN VARCHAR2
,I_BE_IN IN VARCHAR2
,I_NR_RECORDS IN NUMBER
,I_ERR_MSG IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF I_BE_IN = 'B'
THEN
INSERT INTO MAP_LOG(ID,MAP_NAME,MAP_START_DATE)
VALUES (I_ID,I_MAP_NAME,SYSDATE);
ELSE
UPDATE MAP_LOG
SET
MAP_END_DATE = SYSDATE
,ELAPSED = ROUND((SYSDATE - MAP_START_DATE)*86400)
,NR_RECORDS = I_NR_RECORDS
,ERR_MSG = I_ERR_MSG
WHERE ID = I_ID;
END IF;
COMMIT;
END;
By using the pragma autonomous_transaction the insert and update in the logging table can be committed without influencing the transaction that is logged. The explicit commit at the end of the logging procedure is necessary because of the autonomous transaction.
Create a test table to load in the test procedure :
Note: 'LT' is used here instead of the less then sign.
CREATE TABLE TEST AS
SELECT * FROM USER_OBJECTS
CROSS JOIN (SELECT LEVEL FROM DUAL CONNECT BY LEVEL LT 2)
WHERE 1 = 0;
With the join with level the number or records in the selection is easily varied.
Create a test procedure:
CREATE OR REPLACE PROCEDURE MAP_TEST
IS
LOG_ID NUMBER;
BEGIN
SELECT SEQ_MAP_LOG.NEXTVAL INTO LOG_ID FROM DUAL;
P_LOG(LOG_ID,$$PLSQL_UNIT,'B',NULL,NULL);
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEST';
INSERT INTO TEST
SELECT * FROM USER_OBJECTS
CROSS JOIN (SELECT LEVEL FROM DUAL CONNECT BY LEVEL LT 10001);
P_LOG(LOG_ID,NULL,'E',SQL%ROWCOUNT,NULL);
EXCEPTION WHEN OTHERS THEN
P_LOG(LOG_ID,NULL,'E',SQL%ROWCOUNT,SQLERRM);
END;
This test procedure selects the contents of user_objects 10.000 times (to make the procedure run for a few seconds and test the elapsed column) and inserts it into the test table.
Before the insert into test the sequence number is selected and an entry in the log table is made.
After the insert into the test table the logrecord is updated.
In case of an error the sql error message is logged in the exception handler.
