Comparing multiple tables on two databases using dynamic PL/SQL

Question

The issue I'm having is I have a block of dynamic SQL that I can't get the results from.

Here's the SQL:

CREATE OR REPLACE PROCEDURE GGCHECKER

IS 
    cursor c1 is 
        SELECT TABLE_NAME, TABLE_OWNER, PERCENTAGE_CHECK, LAST_UPDATED, PRIMARY_KEY FROM system.TEMP_TABLE;

    CTABLE_NAME          VARCHAR2(50); 

    CTABLE_OWNER         VARCHAR2(50);

    CPERCENTAGE_CHECK    VARCHAR2(50);

    LU_CTABLE            VARCHAR2(50);

    PK_CTABLE           VARCHAR2(50);

    var2                varchar2(4000);

    var3                varchar2(4000);

    var4                varchar2(4000);

    var5                varchar2(4000);

BEGIN

OPEN C1;

LOOP

    FETCH C1 INTO CTABLE_NAME, CTABLE_OWNER, CPERCENTAGE_CHECK, LU_CTABLE, PK_CTABLE;

var2:='CREATE TABLE system.COMPARISON_RANDOM AS (
    SELECT ' ||PK_CTABLE|| ' FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| ' SAMPLE(' ||CPERCENTAGE_CHECK|| ')
    WHERE ' ||LU_CTABLE|| ' < SYSDATE -1 
    )';

    EXECUTE IMMEDIATE var2;

VAR5:='
SELECT '|| PK_CTABLE || ' 
    FROM (
            (
                SELECT * FROM ' || CTABLE_OWNER || '.' ||CTABLE_NAME|| '
                    WHERE ' || PK_CTABLE || ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM system.COMPARISON_RANDOM
                        )
                MINUS
                SELECT * FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| '@DBLINK
                    WHERE ' ||PK_CTABLE|| ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM system.COMPARISON_RANDOM
                        )
            )
            UNION ALL
            (
                SELECT * FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| '@DBLINK
                    WHERE ' ||PK_CTABLE|| ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM system.COMPARISON_RANDOM
                        )
                MINUS
                SELECT * FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| '
                    WHERE ' ||PK_CTABLE|| ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM system.COMPARISON_RANDOM
                        )
            )
        )';


    var3:='DROP TABLE system.COMPARISON_RANDOM';
    execute IMMEDIATE var3;
    EXIT WHEN c1%NOTFOUND;

    END LOOP;
    CLOSE C1;
    var4:='DROP TABLE system.TEMP_TABLE';
    execute IMMEDIATE var4;

end;
/

So because it has to go through a number of tables on a number of schemas on each database all the SQL for the comparison of the data has to be dynamic. I'm looking to get the results from the query in variable var5. The issue being that I also need the schema and table name for ID's that are outputted so the data is useful to me.

Ideal output would be something like this:

SCHEMA_NAME | TABLE_NAME | COL_ID

ODD         |     MIKE   | 1234567890

Thanks in advance!

Answer 1

I've found a solution but it's probably not the best way of doing it.

CREATE OR REPLACE PROCEDURE GGCHECKER

IS 
    cursor c1 is 
        SELECT TABLE_NAME, TABLE_OWNER, PERCENTAGE_CHECK, LAST_UPDATED, PRIMARY_KEY FROM MODDIE.TEMP_TABLE;

    CTABLE_NAME          VARCHAR2(50); 

    CTABLE_OWNER         VARCHAR2(50);

    CPERCENTAGE_CHECK    VARCHAR2(50);

    LU_CTABLE            VARCHAR2(50);

    PK_CTABLE           VARCHAR2(50);

    var2                varchar2(4000);

    var3                varchar2(4000);

    var4                varchar2(4000);

    var5                varchar2(4000);

    var6                varchar2(4000);

    VAR7                VARCHAR2(4000);

    var8                VARCHAR2(4000);

    var9                VARCHAR2(4000);

    NUMROWS             NUMBER(20);

BEGIN

OPEN C1;

LOOP

    FETCH C1 INTO CTABLE_NAME, CTABLE_OWNER, CPERCENTAGE_CHECK, LU_CTABLE, PK_CTABLE;

-- Create query to populate comparison table

var2:='CREATE TABLE system.COMPARISON_RANDOM AS (
    SELECT ' ||PK_CTABLE|| ' FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| ' SAMPLE(' ||CPERCENTAGE_CHECK|| ')
    WHERE ' ||LU_CTABLE|| ' < SYSDATE -1 
    )';



    EXECUTE IMMEDIATE var2;


--Compare the two tables
VAR5:='CREATE TABLE System.TEMP_RESULTS AS (
SELECT DISTINCT '|| PK_CTABLE || ' 
    FROM (
            (
                SELECT * FROM ' || CTABLE_OWNER || '.' ||CTABLE_NAME|| '
                    WHERE ' || PK_CTABLE || ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM System.COMPARISON_RANDOM
                        )
                MINUS
                SELECT * FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| '@dblink
                    WHERE ' ||PK_CTABLE|| ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM system.COMPARISON_RANDOM
                        )
            )
            UNION ALL
            (
                SELECT * FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| '@dblink
                    WHERE ' ||PK_CTABLE|| ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM system.COMPARISON_RANDOM
                        )
                MINUS
                SELECT * FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| '
                    WHERE ' ||PK_CTABLE|| ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM system.COMPARISON_RANDOM
                        )
            )
        )
        )';
    execute immediate var5;   

    var8:= 'SELECT COUNT(*) FROM system.TEMP_RESULTS'; 

    -- Find the number of results for the loop
    execute immediate var8 into NUMROWS;

    DBMS_OUTPUT.PUT_LINE('THE NUMBER OF DISCREPANCIES IN ' || CTABLE_OWNER || '.' || CTABLE_NAME || ' IS: '|| NUMROWS);  

    -- For every result add a record to the results table 
    IF NUMROWS > 0 THEN

        var6:='   
        INSERT INTO system.RESULTS_TABLE
        (TABLE_OWNER, TABLE_NAME, COL_ID)
        VALUES
        ( ''' || CTABLE_OWNER || '''  , ''' || CTABLE_NAME || ''' , NULL)';

        FOR N IN 1..NUMROWS LOOP
            DBMS_OUTPUT.PUT_LINE(var6);
            EXECUTE IMMEDIATE var6;
        END LOOP;
    DBMS_OUTPUT.PUT_LINE('mid if');

        FOR M IN 1..NUMROWS LOOP
            DBMS_OUTPUT.PUT_LINE('mid for2');
            var7:=' UPDATE system.RESULTS_TABLE SET COL_ID = (SELECT * FROM system.TEMP_RESULTS WHERE ROWNUM = ' || M  || ' ) WHERE ROWNUM = ' || M || ' ';
            DBMS_OUTPUT.PUT_LINE(var7);
            EXECUTE IMMEDIATE VAR7; 
         END LOOP;

     END IF; 

    var3:='DROP TABLE system.COMPARISON_RANDOM';
    execute IMMEDIATE var3;

    var9:='DROP TABLE system.TEMP_RESULTS';
    EXECUTE IMMEDIATE VAR9;
     DBMS_OUTPUT.PUT_LINE('exit 1');
    EXIT WHEN c1%NOTFOUND;


    END LOOP;
        CLOSE C1;
    var4:='DROP TABLE system.TEMP_TABLE';
    execute IMMEDIATE var4;
     DBMS_OUTPUT.PUT_LINE('exit 3');


end;
/