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!

• Wernfried Domscheit: Did you check DBMS_SQL package? I think the closed example to your problem is this: docs.oracle.com/cloud/latest/db121/ARPLS/d_sql.htm#sthref9627
• Michael Oddie: Yep, that would have been perfect! I've got around it now though. Thanks for the help.

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;
/