plsql comparing structure of tables in 2 different database
<p>I have a task of comparing structure of tables between 2 different databases. If there is a column missing I have to report the missing column and its tables. </p>

<p>Simple minus query will not return. As I am very new to pl sql programming, I am not getting any ideas.</p>

<p>can someone please help me with this?</p>

<pre><code>Table 1:
Columns 
ABC
DEF
GHI

Table2:
ABC
DEF 
GHI
JKL
</code></pre>

<p>Here is an additional column. If this has to be reported, how do I do it.</p>

<p>PFB my simple proc:</p>

<pre><code>create or replace PROCEDURE PROC_EU_TABLE_COMPARE (
R_CURSOR OUT SYS_REFCURSOR,
SRC_SCHEMA_NAME         IN VARCHAR2,
TGT_SCHEMA_NAME         IN VARCHAR2
)

IS 


CNT3 NUMBER;
SQL_TEXT3 VARCHAR2(4000);

BEGIN

--SRC_SCHEMA_NAME      := UPPER(SRC_SCHEMA_NAME);
--TGT_SCHEMA_NAME      := UPPER(TGT_SCHEMA_NAME);


SQL_TEXT3:='SELECT * FROM 
((SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME =''%EU%'' AND OWNER ='''||SRC_SCHEMA_NAME||'''
        MINUS
        SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME LIKE ''%EU%''AND OWNER ='''||TGT_SCHEMA_NAME||''')
        UNION
        (SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME LIKE ''%EU%''AND OWNER ='''||TGT_SCHEMA_NAME||'''
        MINUS
        SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME =''%EU%'' AND OWNER ='''||SRC_SCHEMA_NAME||'''))';

        EXECUTE IMMEDIATE SQL_TEXT3 INTO CNT3;
        DBMS_OUTPUT.PUT_LINE(CNT3);

END; 
</code></pre>