Oracle SQL & PL/SQL : ETL Testing Automation

Step 1: Create below tables to store source/target details and test results

DROP TABLE SRC_TGT_DETAILS;

CREATE TABLE SRC_TGT_DETAILS
(
SEQ_NO NUMBER PRIMARY KEY,
SRC_TBL_OWNER VARCHAR2 (50) NOT NULL,
SRC_TBL_NAME VARCHAR2 (50) NOT NULL,
SRC_TBL_DBLINK VARCHAR2 (50),
TGT_TBL_OWNER VARCHAR2 (50) NOT NULL,
TGT_TBL_NAME VARCHAR2 (50) NOT NULL,
TGT_TBL_DBLINK VARCHAR2 (50),
KEY_COLUMNS VARCHAR2 (4000) NOT NULL,
SRC_FILTER VARCHAR2 (4000),
TGT_FILTER VARCHAR2 (4000)
);

DROP TABLE TEST_RESULTS;

CREATE TABLE TEST_RESULTS
(
SEQ_NO NUMBER (5),
SRC_TBL_OWNER VARCHAR2 (50),
SRC_TBL_NAME VARCHAR2 (50),
TGT_TBL_OWNER VARCHAR2 (50),
TGT_TBL_NAME VARCHAR2 (50),
SRC_COUNT NUMBER (30),
TGT_COUNT NUMBER (30),
SRC_DUPLICATE_COUNT NUMBER (30),
TGT_DUPLICATE_COUNT NUMBER (30),
KEY_COLUMNS VARCHAR2 (1000),
NON_KEY_COLUMN VARCHAR2 (50),
SRC_MINUS_TGT_COUNT NUMBER (20),
TGT_MINUS_SRC_COUNT NUMBER (20),
STATUS VARCHAR2 (1000),
SRC_TGT_COUNT_QUERY VARCHAR2 (4000),
SRC_MINUS_TGT_QUERY VARCHAR2 (4000),
TGT_MINUS_SRC_QUERY VARCHAR2 (4000),
SRC_TGT_DUP_QUERY VARCHAR2 (4000)
);

Step 2: Insert Source and target details

INSERT INTO SRC_TGT_DETAILS (SEQ_NO,SRC_TBL_OWNER,SRC_TBL_NAME,SRC_TBL_DBLINK,TGT_TBL_OWNER,TGT_TBL_NAME,TGT_TBL_DBLINK,KEY_COLUMNS,SRC_FILTER,TGT_FILTER)
VALUES (123,'SYSTEM','EMP',NULL,'SCOTT','EMP',NULL,'EMPNO','DEPTNO=10','DEPTNO=10');
COMMIT;

Step 3: Execute below Automated PL/SQL Script

SET SERVEROUTPUT ON;

DECLARE
V_SEQ_NO NUMBER;
V_SRC_TBL_OWNER SYS.ALL_TAB_COLS.OWNER%TYPE;
V_TGT_TBL_OWNER SYS.ALL_TAB_COLS.OWNER%TYPE;
V_SRC_TBL_NAME SYS.ALL_TAB_COLS.TABLE_NAME%TYPE;
V_TGT_TBL_NAME SYS.ALL_TAB_COLS.TABLE_NAME%TYPE;
V_SRC_TBL_DBLINK VARCHAR2 (1000);
V_TGT_TBL_DBLINK VARCHAR2 (1000);
V_KEY_COLUMNS VARCHAR2 (5000);
V_SRC_FILTER VARCHAR2 (4000);
V_TGT_FILTER VARCHAR2 (4000);
V_SRC_COUNT NUMBER;
V_TGT_COUNT NUMBER;
V_SRC_DUPLICATE_COUNT NUMBER;
V_TGT_DUPLICATE_COUNT NUMBER;
V_COLUMN_LIST_Q VARCHAR2 (32000);
V_COLUMN_LIST DBMS_SQL.VARCHAR2_TABLE;
V_SRC_TGT_COUNT_Q VARCHAR2 (32000);
V_SRC_TGT_DUP_Q VARCHAR2 (32000);
V_SMT_Q VARCHAR2 (32000);
V_SMT_COUNT_Q VARCHAR2 (32000);
V_SMT_COUNT NUMBER;
V_TMS_Q VARCHAR2 (32000);
V_TMS_COUNT_Q VARCHAR2 (32000);
V_TMS_COUNT NUMBER;
V_SRC_TBL VARCHAR2 (100);
V_TGT_TBL VARCHAR2 (100);
BEGIN
DBMS_OUTPUT.ENABLE (10000000);

FOR I IN ( SELECT SEQ_NO,
SRC_TBL_OWNER,
SRC_TBL_NAME,
SRC_TBL_DBLINK,
TGT_TBL_OWNER,
TGT_TBL_NAME,
TGT_TBL_DBLINK,
KEY_COLUMNS,
NVL (SRC_FILTER, '1=1') AS SRC_FILTER,
NVL (TGT_FILTER, '1=1') AS TGT_FILTER
FROM SRC_TGT_DETAILS
ORDER BY SEQ_NO)
LOOP
V_SEQ_NO := I.SEQ_NO;
V_SRC_TBL_OWNER := I.SRC_TBL_OWNER;
V_SRC_TBL_NAME := I.SRC_TBL_NAME;
V_SRC_TBL_DBLINK := I.SRC_TBL_DBLINK;
V_TGT_TBL_OWNER := I.TGT_TBL_OWNER;
V_TGT_TBL_NAME := I.TGT_TBL_NAME;
V_TGT_TBL_DBLINK := I.TGT_TBL_DBLINK;
V_KEY_COLUMNS := I.KEY_COLUMNS;
V_SRC_FILTER := I.SRC_FILTER;
V_TGT_FILTER := I.TGT_FILTER;

DELETE FROM TEST_RESULTS
WHERE SEQ_NO = I.SEQ_NO;

COMMIT;

V_SRC_TBL :=
V_SRC_TBL_OWNER
|| '.'
|| V_SRC_TBL_NAME
|| CASE WHEN V_SRC_TBL_DBLINK IS NULL THEN '' ELSE '@' END
|| V_SRC_TBL_DBLINK;

V_TGT_TBL :=
V_TGT_TBL_OWNER
|| '.'
|| V_TGT_TBL_NAME
|| CASE WHEN V_TGT_TBL_DBLINK IS NULL THEN '' ELSE '@' END
|| V_TGT_TBL_DBLINK;

V_SRC_TGT_COUNT_Q :=
'SELECT '
|| DBMS_ASSERT.ENQUOTE_LITERAL (V_SRC_TBL)
|| ' AS SOURCE,COUNT(*) AS CNT FROM '
|| V_SRC_TBL
|| ' WHERE '
|| V_SRC_FILTER
|| ' UNION ALL SELECT '
|| DBMS_ASSERT.ENQUOTE_LITERAL (V_TGT_TBL)
|| ', COUNT(*) FROM '
|| V_TGT_TBL
|| ' WHERE '
|| V_TGT_FILTER
|| ';';

-- DBMS_OUTPUT.PUT_LINE ('V_SRC_TGT_COUNT_Q=' || V_SRC_TGT_COUNT_Q);

V_SRC_TGT_DUP_Q :=
'SELECT '
|| DBMS_ASSERT.ENQUOTE_LITERAL (V_SRC_TBL)
|| ' AS SOURCE,'
|| V_KEY_COLUMNS
|| ',COUNT(*) AS CNT FROM '
|| V_SRC_TBL
|| ' WHERE '
|| V_SRC_FILTER
|| ' GROUP BY '
|| V_KEY_COLUMNS
|| ' HAVING COUNT(*) > 1 UNION ALL SELECT '
|| DBMS_ASSERT.ENQUOTE_LITERAL (V_TGT_TBL)
|| ', '
|| V_KEY_COLUMNS
|| ', COUNT(*) AS CNT FROM '
|| V_TGT_TBL
|| ' WHERE '
|| V_TGT_FILTER
|| ' GROUP BY '
|| V_KEY_COLUMNS
|| ' HAVING COUNT(*) > 1 ;';

-- DBMS_OUTPUT.PUT_LINE ('V_SRC_TGT_DUP_Q=' || V_SRC_TGT_DUP_Q);

EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '
|| V_SRC_TBL_OWNER
|| '.'
|| V_SRC_TBL_NAME
|| CASE WHEN V_SRC_TBL_DBLINK IS NULL THEN '' ELSE '@' END
|| V_SRC_TBL_DBLINK
|| ' WHERE '
|| V_SRC_FILTER
INTO V_SRC_COUNT;

EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '
|| V_TGT_TBL_OWNER
|| '.'
|| V_TGT_TBL_NAME
|| CASE WHEN V_TGT_TBL_DBLINK IS NULL THEN '' ELSE '@' END
|| V_TGT_TBL_DBLINK
|| ' WHERE '
|| V_TGT_FILTER
INTO V_TGT_COUNT;

EXECUTE IMMEDIATE
'SELECT COUNT(CNT) FROM (SELECT '
|| V_KEY_COLUMNS
|| ', COUNT(*) AS CNT FROM '
|| V_SRC_TBL_OWNER
|| '.'
|| V_SRC_TBL_NAME
|| CASE WHEN V_SRC_TBL_DBLINK IS NULL THEN '' ELSE '@' END
|| V_SRC_TBL_DBLINK
|| ' WHERE '
|| V_SRC_FILTER
|| ' GROUP BY '
|| V_KEY_COLUMNS
|| ' HAVING COUNT(*) > 1)'
INTO V_SRC_DUPLICATE_COUNT;

EXECUTE IMMEDIATE
'SELECT COUNT(CNT) FROM (SELECT '
|| V_KEY_COLUMNS
|| ', COUNT(*) AS CNT FROM '
|| V_TGT_TBL_OWNER
|| '.'
|| V_TGT_TBL_NAME
|| CASE WHEN V_TGT_TBL_DBLINK IS NULL THEN '' ELSE '@' END
|| V_TGT_TBL_DBLINK
|| ' WHERE '
|| V_TGT_FILTER
|| ' GROUP BY '
|| V_KEY_COLUMNS
|| ' HAVING COUNT(*) > 1)'
INTO V_TGT_DUPLICATE_COUNT;

-- DBMS_OUTPUT.PUT_LINE ('V_SRC_COUNT=' || V_SRC_COUNT);
-- DBMS_OUTPUT.PUT_LINE ('V_TGT_COUNT=' || V_TGT_COUNT);
-- DBMS_OUTPUT.PUT_LINE ('V_SRC_DUPLICATE_COUNT=' || V_SRC_DUPLICATE_COUNT);
-- DBMS_OUTPUT.PUT_LINE ('V_TGT_DUPLICATE_COUNT=' || V_TGT_DUPLICATE_COUNT);

-- TARGET COUNT IS NOT MATCHES WITH SOURCE

IF V_SRC_COUNT - V_TGT_COUNT <> 0
THEN
INSERT INTO TEST_RESULTS (SEQ_NO,
SRC_TBL_OWNER,
SRC_TBL_NAME,
TGT_TBL_OWNER,
TGT_TBL_NAME,
SRC_COUNT,
TGT_COUNT,
SRC_DUPLICATE_COUNT,
TGT_DUPLICATE_COUNT,
KEY_COLUMNS,
NON_KEY_COLUMN,
SRC_MINUS_TGT_COUNT,
TGT_MINUS_SRC_COUNT,
STATUS,
SRC_TGT_COUNT_QUERY,
SRC_MINUS_TGT_QUERY,
TGT_MINUS_SRC_QUERY,
SRC_TGT_DUP_QUERY)
VALUES (V_SEQ_NO,
V_SRC_TBL_OWNER,
V_SRC_TBL_NAME,
V_TGT_TBL_OWNER,
V_TGT_TBL_NAME,
V_SRC_COUNT,
V_TGT_COUNT,
V_SRC_DUPLICATE_COUNT,
V_TGT_DUPLICATE_COUNT,
V_KEY_COLUMNS,
'NA',
NULL,
NULL,
'COUNT MISMATCH',
V_SRC_TGT_COUNT_Q,
'NA',
'NA',
V_SRC_TGT_DUP_Q);

COMMIT;
ELSE
V_COLUMN_LIST_Q :=
'SELECT COLUMN_NAME FROM SYS.ALL_TAB_COLS'
|| CASE WHEN V_SRC_TBL_DBLINK IS NULL THEN '' ELSE '@' END
|| V_SRC_TBL_DBLINK
|| ' WHERE TABLE_NAME = '
|| DBMS_ASSERT.ENQUOTE_LITERAL (V_SRC_TBL_NAME)
|| ' AND OWNER = '
|| DBMS_ASSERT.ENQUOTE_LITERAL (V_SRC_TBL_OWNER)
|| ' AND COLUMN_NAME NOT LIKE ''%SYS_STS%'' INTERSECT SELECT COLUMN_NAME FROM SYS.ALL_TAB_COLS'
|| CASE WHEN V_TGT_TBL_DBLINK IS NULL THEN '' ELSE '@' END
|| V_TGT_TBL_DBLINK
|| ' WHERE TABLE_NAME = '
|| DBMS_ASSERT.ENQUOTE_LITERAL (V_TGT_TBL_NAME)
|| ' AND OWNER = '
|| DBMS_ASSERT.ENQUOTE_LITERAL (V_TGT_TBL_OWNER);

-- DBMS_OUTPUT.PUT_LINE ('V_COLUMN_LIST_Q=' || V_COLUMN_LIST_Q);
EXECUTE IMMEDIATE V_COLUMN_LIST_Q BULK COLLECT INTO V_COLUMN_LIST;

FOR I IN 1 .. V_COLUMN_LIST.COUNT
LOOP
-- SOURCE MINUS TARGET

V_SMT_Q :=
'SELECT /*+ parallel(16) */ '
|| V_KEY_COLUMNS
|| ','
|| V_COLUMN_LIST (I)
|| ' FROM '
|| V_SRC_TBL_OWNER
|| '.'
|| V_SRC_TBL_NAME
|| CASE WHEN V_SRC_TBL_DBLINK IS NULL THEN '' ELSE '@' END
|| V_SRC_TBL_DBLINK
|| ' WHERE '
|| V_SRC_FILTER
|| ' MINUS '
|| 'SELECT /*+ parallel(16) */ '
|| V_KEY_COLUMNS
|| ','
|| V_COLUMN_LIST (I)
|| ' FROM '
|| V_TGT_TBL_OWNER
|| '.'
|| V_TGT_TBL_NAME
|| CASE WHEN V_TGT_TBL_DBLINK IS NULL THEN '' ELSE '@' END
|| V_TGT_TBL_DBLINK
|| ' WHERE '
|| V_TGT_FILTER;
V_SMT_COUNT_Q := 'SELECT COUNT(*) FROM (' || V_SMT_Q || ')';

--DBMS_OUTPUT.PUT_LINE ('V_SMT_COUNT_Q=' || V_SMT_COUNT_Q);
EXECUTE IMMEDIATE V_SMT_COUNT_Q INTO V_SMT_COUNT;

--DBMS_OUTPUT.PUT_LINE ('V_SMT_COUNT=' || V_SMT_COUNT);
-- TARGET MINUS SOURCE
V_TMS_Q :=
'SELECT /*+ parallel(16) */ '
|| V_KEY_COLUMNS
|| ','
|| V_COLUMN_LIST (I)
|| ' FROM '
|| V_TGT_TBL_OWNER
|| '.'
|| V_TGT_TBL_NAME
|| CASE WHEN V_TGT_TBL_DBLINK IS NULL THEN '' ELSE '@' END
|| V_TGT_TBL_DBLINK
|| ' WHERE '
|| V_SRC_FILTER
|| ' MINUS '
|| 'SELECT /*+ parallel(16) */ '
|| V_KEY_COLUMNS
|| ','
|| V_COLUMN_LIST (I)
|| ' FROM '
|| V_SRC_TBL_OWNER
|| '.'
|| V_SRC_TBL_NAME
|| CASE WHEN V_SRC_TBL_DBLINK IS NULL THEN '' ELSE '@' END
|| V_SRC_TBL_DBLINK
|| ' WHERE '
|| V_TGT_FILTER;

V_TMS_COUNT_Q := 'SELECT COUNT(*) FROM (' || V_TMS_Q || ')';

--DBMS_OUTPUT.PUT_LINE ('V_TMS_COUNT_Q=' || V_TMS_COUNT_Q);
EXECUTE IMMEDIATE V_TMS_COUNT_Q INTO V_TMS_COUNT;

--DBMS_OUTPUT.PUT_LINE ('V_TMS_COUNT=' || V_TMS_COUNT);
INSERT INTO TEST_RESULTS (SEQ_NO,
SRC_TBL_OWNER,
SRC_TBL_NAME,
TGT_TBL_OWNER,
TGT_TBL_NAME,
SRC_COUNT,
TGT_COUNT,
SRC_DUPLICATE_COUNT,
TGT_DUPLICATE_COUNT,
KEY_COLUMNS,
NON_KEY_COLUMN,
SRC_MINUS_TGT_COUNT,
TGT_MINUS_SRC_COUNT,
STATUS,
SRC_TGT_COUNT_QUERY,
SRC_MINUS_TGT_QUERY,
TGT_MINUS_SRC_QUERY,
SRC_TGT_DUP_QUERY)
VALUES (
V_SEQ_NO,
V_SRC_TBL_OWNER,
V_SRC_TBL_NAME,
V_TGT_TBL_OWNER,
V_TGT_TBL_NAME,
V_SRC_COUNT,
V_TGT_COUNT,
V_SRC_DUPLICATE_COUNT,
V_TGT_DUPLICATE_COUNT,
V_KEY_COLUMNS,
V_COLUMN_LIST (I),
V_SMT_COUNT,
V_TMS_COUNT,
CASE
WHEN V_SMT_COUNT + V_TMS_COUNT = 0
THEN
'PASS'
ELSE
'FAIL'
END,
V_SRC_TGT_COUNT_Q,
V_SMT_Q || ';',
V_TMS_Q || ';',
V_SRC_TGT_DUP_Q);

COMMIT;
END LOOP;
END IF;
END LOOP;
END;
/

Step 4: View Test Results using below Query

SELECT * FROM TEST_RESULTS;

Oracle SQL & PL/SQL

Friday, October 11, 2019

ETL Testing Automation - PL/SQL

No comments:

Post a Comment