How to check oracle database is either 32 bit or 64bit

SQL> conn / as sysdba
Connected.
SQL>  select length(addr)*4 || '-bits' word_length from v$process where rownum=1;

WORD_LENGTH
---------------------------------------------
64-bits

SQL>

-- using cmd in Windows machine

C:\Users\rajam>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 30 17:36:27 2019

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL>
SQL> select length(addr)*4 || '-bits' word_length from v$process where rownum=1;

WORD_LENGTH
---------------------------------------------
32-bits

SQL>

FLASHBACK in Oracle

SQL> SET DEFINE OFF;
SQL> SET LINES 100;
SQL> SET PAGES 50;
SQL> CREATE TABLE TEST_FLASHBACK (ID NUMBER(2) ,NAME VARCHAR2(10));

Table created.

SQL> INSERT INTO TEST_FLASHBACK VALUES(1,'RAJ');

1 row created.

SQL> INSERT INTO TEST_FLASHBACK VALUES(2,'SEKHAR');

1 row created.

SQL> INSERT INTO TEST_FLASHBACK VALUES(3,'JAGA');

1 row created.

SQL> INSERT INTO TEST_FLASHBACK VALUES(4,'SIVA');

1 row created.

SQL> INSERT INTO TEST_FLASHBACK VALUES(5,'SCOTT');

1 row created.

SQL> INSERT INTO TEST_FLASHBACK VALUES(6,'SMITH');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TEST_FLASHBACK;

        ID NAME
---------- ----------
         1 RAJ
         2 SEKHAR
         3 JAGA
         4 SIVA
         5 SCOTT
         6 SMITH

6 rows selected.

SQL>
SQL> SELECT * FROM TEST_FLASHBACK;

        ID NAME
---------- ----------
         1 RAJ
         2 SEKHAR
         3 JAGA
         4 SIVA
         5 SCOTT
         6 SMITH

6 rows selected.

SQL> DELETE FROM TEST_FLASHBACK WHERE ID IN (4,5,6);

3 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TEST_FLASHBACK;

        ID NAME
---------- ----------
         1 RAJ
         2 SEKHAR
         3 JAGA

SQL> FLASHBACK TABLE TEST_FLASHBACK TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);
FLASHBACK TABLE TEST_FLASHBACK TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE)
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled


SQL> ALTER TABLE TEST_FLASHBACK ENABLE ROW MOVEMENT;

Table altered.

SQL> FLASHBACK TABLE TEST_FLASHBACK TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);

Flashback complete.

SQL> SELECT * FROM TEST_FLASHBACK;

        ID NAME
---------- ----------
         1 RAJ
         2 SEKHAR
         3 JAGA
         4 SIVA
         5 SCOTT
         6 SMITH

6 rows selected.

SQL>

ORA-24247: network access denied by access control list (ACL) in 12c

SQL> conn scott/tiger
Connected.
SQL> SELECT UTL_HTTP.request('https://wsctt.pearsonvue.com/cxfws2/services/Ping','http://www-proxy-abc.us.oracle.com:80','file:/u01/app/oracle/product/12.1.0/dbhome_2/owm/wsctt_102019','abcedef')FROM DUAL;
SELECT UTL_HTTP.request('https://wsctt.pearsonvue.com/cxfws2/services/Ping','http://www-proxy-abc.us.oracle.com:80','file:/u01/app/oracle/product/12.1.0/dbhome_2/owm/wsctt_102019','abcedef')FROM DUAL
       *
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_HTTP", line 1491
ORA-06512: at line 1

SQL>

SQL> conn sys as sysdba
Enter password: 
Connected.
SQL> BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => '*', 
    lower_port => 1,
    upper_port => 9999,
    ace        => xs$ace_type(privilege_list => xs$name_list('connect'),
                              principal_name => 'SCOTT',
                              principal_type => xs_acl.ptype_db)); 
END;
/
  2    3    4    5    6    7    8    9   10  
PL/SQL procedure successfully completed.

SQL> 


SQL> conn scott/tiger
Connected.

SQL> SELECT UTL_HTTP.request('https://wsctt.pearsonvue.com/cxfws2/services/Ping','http://www-proxy-abc.us.oracle.com:80','file:/u01/app/oracle/product/12.1.0/dbhome_2/owm/wsctt_102019','abcedef')FROM DUAL;

UTL_HTTP.REQUEST('HTTPS://WSCTT.PEARSONVUE.COM/CXFWS2/SERVICES/PING','HTTP://WWW
--------------------------------------------------------------------------------
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:soapen
v="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><soap:Fault><soap:Code>
<soap:Value>soap:Receiver</soap:Value></soap:Code><soap:Reason><soap:Text xml:la
ng="en">Fault occurred while processing.</soap:Text></soap:Reason></soap:Fault><
/soap:Body></soap:Envelope>


SQL>

-- Access wallets in 11g

BEGIN
   DBMS_NETWORK_ACL_ADMIN.create_acl (
      acl           => 'wsctt_102019.xml',
      description   => 'ACL for scott user to access wsctt_102019 wallet',
      principal     => 'SCOTT',
      is_grant      => TRUE,
      privilege     => 'use-client-certificates',
      start_date    => NULL,
      end_date      => NULL);
   COMMIT;
END;
/

BEGIN
   DBMS_NETWORK_ACL_ADMIN.assign_wallet_acl (
      acl           => 'wsctt_102019.xml',
      wallet_path   => 'file:/u01/app/oracle/product/12.1.0/dbhome_2/owm/wsctt_102019');
   COMMIT;
END;

/

ORA-28000: the account is locked

SQL> conn scott/tiger
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.
SQL> 

SQL> conn sys as sysdba
Enter password: 
Connected.
SQL> alter user scott identified by tiger account unlock;

User altered.

SQL> conn scott/tiger
Connected.
SQL> 

BULK COLLECT - SAVE EXCEPTIONS in Oracle

FORALL allows us to perform bulk DML operations, but what happens if one of those individual operations results in an exception?

If there is no exception handler, all the work done by the current bulk operation is rolled back.So we should use the SAVE EXCEPTIONS clause to capture the exceptions and allow us to continue even error occurred for individual operations results.

SQL> CREATE TABLE BULK_EXCEPTION_TBL (ID NUMBER (10) NOT NULL);

Table created.

SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     TYPE T_ID IS TABLE OF BULK_EXCEPTION_TBL%ROWTYPE;
  3
  4     L_ID            T_ID := T_ID ();
  5     l_error_count   NUMBER;
  6
  7     E_DML_ERROR     EXCEPTION;
  8     PRAGMA EXCEPTION_INIT (E_DML_ERROR, -24381);
  9  BEGIN
 10     -- Fill the collection.
 11     FOR I IN 1 .. 10
 12     LOOP
 13        L_ID.EXTEND;
 14        L_ID (L_ID.LAST).ID := I;
 15     END LOOP;
 16
 17     -- Cause a failure.
 18     L_ID (5).id := NULL;
 19     L_ID (6).id := NULL;
 20
 21     EXECUTE IMMEDIATE 'TRUNCATE TABLE BULK_EXCEPTION_TBL';
 22
 23     -- Perform a bulk operation.
 24     BEGIN
 25        FORALL i IN L_ID.FIRST .. L_ID.LAST SAVE EXCEPTIONS
 26           INSERT INTO BULK_EXCEPTION_TBL
 27                VALUES L_ID (i);
 28     EXCEPTION
 29        WHEN E_DML_ERROR
 30        THEN
 31           l_error_count := SQL%BULK_EXCEPTIONS.COUNT;
 32           DBMS_OUTPUT.put_line ('Number of failures: ' || l_error_count);
 33
 34           FOR i IN 1 .. l_error_count
 35           LOOP
 36              DBMS_OUTPUT.put_line (
 37                    'Error: '
 38                 || i
 39                 || ' Array Index: '
 40                 || SQL%BULK_EXCEPTIONS (i).ERROR_INDEX
 41                 || ' Message: '
 42                 || SQLERRM (-SQL%BULK_EXCEPTIONS (i).ERROR_CODE));
 43           END LOOP;
 44     END;
 45  END;
 46  /
Number of failures: 2
Error: 1 Array Index: 5 Message: ORA-01400: cannot insert NULL into ()
Error: 2 Array Index: 6 Message: ORA-01400: cannot insert NULL into ()

PL/SQL procedure successfully completed.

SQL> SELECT * FROM BULK_EXCEPTION_TBL;

        ID
----------
         1
         2
         3
         4
         7
         8
         9
        10

8 rows selected.

SQL>

PL/SQL Procedure/Function to display total salary without using sum function

SQL> CREATE OR REPLACE PROCEDURE P_TOTAL_SALARY (I_DEPTNO NUMBER)
  2  AS
  3     V_SAL_SUM   NUMBER := 0;
  4  BEGIN
  5     FOR K IN (SELECT SAL, COMM
  6                 FROM EMP
  7                WHERE DEPTNO = I_DEPTNO)
  8     LOOP
  9        V_SAL_SUM := V_SAL_SUM + NVL (K.SAL, 0) + NVL (K.COMM, 0);
 10     END LOOP;
 11
 12     DBMS_OUTPUT.PUT_LINE ('Total Salary Of Employee=' || V_SAL_SUM);
 13  EXCEPTION
 14     WHEN OTHERS
 15     THEN
 16        DBMS_OUTPUT.PUT_LINE (
 17           'Oracel Error Code-' || SQLCODE || '~Error Message-' || SQLERRM);
 18  END;
 19  /

Procedure created.

SQL> -- Execution
SQL> SET SERVEROUTPUT ON
SQL> EXEC P_TOTAL_SALARY(10);
Total Salary Of Employee=8750

PL/SQL procedure successfully completed.


SQL> BEGIN P_TOTAL_SALARY(10); END;
  2  /
Total Salary Of Employee=8750

PL/SQL procedure successfully completed.

SQL>
SQL> -- Using SUM function
SQL> SELECT SUM(NVL(SAL,0)+NVL(COMM,0)) AS TOTAL_SALARY FROM EMP WHERE DEPTNO=10;

TOTAL_SALARY
------------
        8750

SQL>

SQL> CREATE OR REPLACE FUNCTION F_TOTAL_SALRAY (I_DEPTNO NUMBER)
  2     RETURN NUMBER
  3  AS
  4     V_SAL_SUM   NUMBER := 0;
  5  BEGIN
  6     FOR K IN (SELECT SAL, COMM
  7                 FROM EMP
  8                WHERE DEPTNO = I_DEPTNO)
  9     LOOP
 10        V_SAL_SUM := V_SAL_SUM + NVL (K.SAL, 0) + NVL (K.COMM, 0);
 11     END LOOP;
 12
 13     DBMS_OUTPUT.PUT_LINE ('Total Salary Of Employee=' || V_SAL_SUM);
 14     RETURN V_SAL_SUM;
 15  EXCEPTION
 16     WHEN OTHERS
 17     THEN
 18        RETURN 0;
 19        DBMS_OUTPUT.PUT_LINE (
 20           'Oracel Error Code-' || SQLCODE || '~Error Message-' || SQLERRM);
 21  END;
 22  /

Function created.

SQL> SELECT F_TOTAL_SALRAY(10) FROM DUAL;

F_TOTAL_SALRAY(10)
------------------
              8750

Total Salary Of Employee=8750
SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
  2     V_DEPTNO      NUMBER := 10;
  3     V_TOTAL_SAL   NUMBER := NULL;
  4  BEGIN
  5     V_TOTAL_SAL := F_TOTAL_SALRAY (V_DEPTNO);
  6     DBMS_OUTPUT.PUT_LINE ('Total Salary Of Employee=' || V_TOTAL_SAL);
  7  END;
  8  /
Total Salary Of Employee=8750
Total Salary Of Employee=8750

PL/SQL procedure successfully completed.

SQL>

Create Directory in Oracle

Create directory path using unix terminal

[oracle@ODIGettingStarted ~]$ pwd
/home/oracle
[oracle@ODIGettingStarted ~]$ su
Password:
[root@ODIGettingStarted oracle]# pwd
/home/oracle
[root@ODIGettingStarted oracle]# cd /
[root@ODIGettingStarted /]# pwd
/
[root@ODIGettingStarted /]# ls
bin  boot  cgroup  dev  etc  home  lib  lib64  lost+found  media  misc  mnt  net  ODIshared  opt  proc  root  sbin  selinux  srv  sys  tmp  u01  usr  var
[root@ODIGettingStarted /]# mkdir u05
[root@ODIGettingStarted /]# cd u05
[root@ODIGettingStarted u05]# mkdir ftp
[root@ODIGettingStarted u05]# cd ftp
[root@ODIGettingStarted ftp]# mkdir ouappprd
[root@ODIGettingStarted ftp]# cd ouappprd
[root@ODIGettingStarted ouappprd]# mkdir ocp
[root@ODIGettingStarted ouappprd]# cd ocp
[root@ODIGettingStarted ocp]# mkdir dat_cdd
[root@ODIGettingStarted ocp]# cd dat_cdd
[root@ODIGettingStarted dat_cdd]# pwd
/u05/ftp/ouappprd/ocp/dat_cdd
[root@ODIGettingStarted dat_cdd]# cd ..
[root@ODIGettingStarted ocp]# ls -ltr
total 16
drwxr-xr-x. 2 root root 4096 Oct 17 07:07 dat_cdd
[root@ODIGettingStarted ocp]# chmod 777 dat_cdd
[root@ODIGettingStarted ocp]# ls -ltr
total 16
drwxrwxrwx. 2 root root 4096 Oct 17 07:07 dat_cdd
[root@ODIGettingStarted ocp]#

Create a Oracle directory and giving read/write permissions to scott user:

[oracle@ODIGettingStarted ocp]$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 17 06:45:41 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE OR REPLACE DIRECTORY DIR_CDD AS '/u05/ftp/ouappprd/ocp/dat_cdd';

Directory created.

SQL> GRANT READ,WRITE ON DIRECTORY DIR_CDD TO SCOTT;

Grant succeeded.

SQL> SET LINES 150
SQL> COLUMN OWNER FORMAT A20
SQL> COLUMN DIRECTORY_NAME FORMAT A20
SQL> COLUMN DIRECTORY_PATH FORMAT A50
SQL> SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='DIR_CDD';

OWNER      DIRECTORY_NAME   DIRECTORY_PATH
-------------------- -------------------- --------------------------------------------------
SYS      DIR_CDD   /u05/ftp/ouappprd/ocp/dat_cdd

SQL>


Verifying Directory Details from scott user:

[oracle@ODIGettingStarted ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 17 06:46:13 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SET LINES 150
SQL> COLUMN OWNER FORMAT A20
SQL> COLUMN DIRECTORY_NAME FORMAT A20
SQL> COLUMN DIRECTORY_PATH FORMAT A50
SQL> SELECT * FROM ALL_DIRECTORIES WHERE DIRECTORY_NAME='DIR_CDD';

OWNER      DIRECTORY_NAME   DIRECTORY_PATH
-------------------- -------------------- --------------------------------------------------
SYS      DIR_CDD   /u05/ftp/ouappprd/ocp/dat_cdd

SQL>

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;