title: plsql comparing structure of tables in 2 different database
body: <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>