plsql procedure to compare two tables without any primary key column

Question

Have to compare the data differences between the below two tables. I have achieved this by writing a MINUS query but that does not fit for current assignment. Because few tables have 50- 60 columns and each time have to mention the columns before execution.

I have followed Expert's response and not succeeded in achieving the goal. Basically I want to write a procedure which:

Accepts both table names as parameters.
Fetch all the columns of CustomerTable.
Then MINUS query between CustomerTable and StagingCustTable only with the columns fetched in step-2.
Logging any differences.

CustomerTable

Custromer_Number
Address
order_Number
Contact
Country
Post_Code
Amount

StagingCustTable

Custromer_Number
Address
order_Number
Contact
Country
Post_Code
Amount
Run_Id
Record_Id

Run_Id and Record_Id can be ignored for getting differences.. — MathurS, Mar 22 '17 at 15:16
How would the results of the final `minus` query be used? For `execute immediate` you would have to select into variables or collections which would require prior knowledge of the structure. You could maybe use `dbms_sql` to open a ref cursor based on the query constructed from the table columns, but then something needs to handle that ref cursor. (Which could be as simple as SQL\*Plus' `print` command, but depends what your intentions are). Unless you really need this to be a procedure, krokodilko's approach will be much simpler. — Alex Poole, Mar 22 '17 at 16:29

krokodilko · Answer 1 · 2017-03-22T16:23:42.990

I would not use a procedure but a query to generate a final query.
Kind of dynamic SQL.
Simple example - let say we have the following tables and data in them:

CREATE TABLE CustomerTable(
Custromer_Number int,
Address varchar2(100),
order_Number int,
Contact int,
Country varchar2(10),
Post_Code varchar2(10),
Amount number
);

INSERT ALL 
INTO CustomerTable VALUES (1, 'aaa', 1, 1, 'AA', '111', 111.11 )
INTO CustomerTable VALUES (2, 'bbb', 2, 2, 'BB', '222', 222.22 )
SELECT 1 FROM dual;

CREATE TABLE StagingCustTable
AS SELECT t.*, 1 As run_id, 1 as record_id
FROM CustomerTable t
WHERE 1=0;

INSERT ALL 
INTO StagingCustTable VALUES (1, 'aaa', 1, 1, 'AA', '111', 111.11, 1, 1 )
INTO StagingCustTable VALUES (3, 'ccc', 3, 3, 'CC', '333', 333.33, 3, 3 )
SELECT 1 FROM dual;
commit;

Now when you run this simple query:

SELECT 'SELECT ' || listagg( column_name, ',' ) WITHIN GROUP ( ORDER BY column_id ) 
       || chr(10) || ' FROM ' || max( table_name ) 
       || chr(10) || ' MINUS '
       || chr(10) || 'SELECT ' || listagg( column_name, ',' ) WITHIN GROUP ( ORDER BY column_id ) 
       || chr(10) || ' FROM StagingCustTable '  as MySql
FROM user_tab_columns
WHERE table_name = upper( 'CustomerTable' );

you will get the following result:

MYSQL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
-------------------------------------------------------------------------
SELECT CUSTROMER_NUMBER,ADDRESS,ORDER_NUMBER,CONTACT,COUNTRY,POST_CODE,AMOUNT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
  FROM CUSTOMERTABLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
MINUS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
SELECT CUSTROMER_NUMBER,ADDRESS,ORDER_NUMBER,CONTACT,COUNTRY,POST_CODE,AMOUNT
 FROM StagingCustTable

Now just copy the above query, paste it to your SQL client, run it - and the task is done in a few minutes.

Actually I have to combine this code into package to complete a set of verification. Hence Procedure or function which can also write the differences into file or Console. Yes along with the above dynamic query. — MathurS, Mar 22 '17 at 17:02

plsql procedure to compare two tables without any primary key column

1 Answers1