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!