Friday, October 11, 2019

ETL Testing Automation - PL/SQL

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;

No comments:

Post a Comment

Delete Row Button to all records of Interactive Report in Oracle Apex

 1. add 'Delete' Del column to Report Query 2. Set the Following Properties for the DEL Column Type: Link Heading: Delete Targ...