--
--  Creates a data dictionary listing which combines the Oracle dic with
--  the Apps dic.  So you get the helpful comments and foreign key help
--  if it's available and you still get the basics even when Apps has
--  nothing on it.
--
--  Views are not included.  Tables are not shown if they contain no rows.
--
--  23oct98  Created.  Tested against Apps 10.7SC on Oracle 7.3
--  17nov98  Added accurate rowcounts for small tables, rather than relying
--           on the stats.
--
--
 

set head off
set pages 0
set lines 2000
set echo off
set serverout on
set termout on
set feed on
drop table nic_temp_text
/
create table nic_temp_text (seq number, text varchar2(2000))
/

declare
 i         number;
 pkey      varchar2(9);
 fkey      varchar2(99);
 col_desc  varchar2(2000);
 line      varchar2(2000);
 tab       varchar2(99);
 col       varchar2(99);
 in_apps   char(1);
 tab_id    number;
 app_id    number;
 col_id    number;
 row_count number;
 x_cursor  number;
 x_n       number;
 x_sql     varchar2(2000);
 --
 --  The tab_col cursor is the place to restrict things if you just want a
 --  few tables, or just want the tables for a single module:
 --
 cursor tab_col is
   select t.owner,
          t.table_name,
          t.num_rows,
          c.column_name,
          decode(c.nullable,'N','NN','') nul,
          c.data_type||decode(c.data_type,
                           'NUMBER',
 decode(c.data_precision,null,'','('||to_char(c.data_precision)||','||to_char(c.data_scale)||')'), 'DATE','',  'LONG','',  'LONG RAW','','('||to_char(c.data_length)||')') datatype
     from all_tables t, all_tab_columns c
     where t.owner = c.owner
       and c.table_name = t.table_name
     order by t.table_name, c.column_id;
 --
 cursor app_tab is
   select ft.table_id,
          ft.application_id,
          fa.application_name||':  '||ft.description txt
     from fnd_tables ft, fnd_application fa
     where ft.application_id = fa.application_id
       and ft.table_name = tab;
 --
 cursor app_col is
   select column_id,
          description
     from fnd_columns
     where column_name = col
       and table_id = tab_id
       and application_id = app_id;
 --
 cursor pk is
   select decode(max(primary_key_id), null,'', min(primary_key_id),' PK','*PK') txt
     from fnd_primary_key_columns
     where application_id = app_id
       and table_id = tab_id
       and column_id = col_id;
 --
 cursor fk is
   select pt.table_name||'.'||pc.column_name txt
     from fnd_foreign_key_columns fkc, fnd_foreign_keys fk,
          fnd_primary_key_columns pk, fnd_columns pc, fnd_tables pt
     where fkc.table_id = tab_id
       and fkc.application_id = app_id
       and fkc.column_id = col_id
       and fk.table_id = tab_id
       and fk.application_id = app_id
       and fk.foreign_key_id = fkc.foreign_key_id
       and pk.table_id = fk.primary_key_table_id
       and pk.application_id = fk.primary_key_application_id
       and pk.primary_key_sequence = fkc.foreign_key_sequence
       and pk.primary_key_id = fk.primary_key_id
       and pc.table_id = pk.table_id
       and pc.application_id = pk.application_id
       and pc.column_id = pk.column_id
       and pt.table_id = pk.table_id
       and pt.application_id = pk.application_id;
 --
 begin
 --
 i := 1;
 tab := 'ZZZ';
 --
 dbms_output.enable(999999);
 dbms_output.put_line('Start build '||to_char(sysdate,'DD MON YY HH24:MI'));
 --
 x_cursor := dbms_sql.open_cursor;
 --
 for x in tab_col loop
 begin
   if x.table_name = tab then
     null;
   else
   begin
 --
     tab := x.table_name;
     row_count := nvl(x.num_rows, 0);
 --
     if row_count < 10 then
       begin
         x_sql := 'SELECT COUNT(*) FROM '|| tab;
         dbms_sql.parse(x_cursor, x_sql, dbms_sql.v7);
         dbms_sql.define_column(x_cursor, 1, row_count);
         x_n := dbms_sql.execute(x_cursor);
         x_n := dbms_sql.fetch_rows(x_cursor);
         if x_n > 0 then
           dbms_sql.column_value(x_cursor, 1, row_count);
  else
           dbms_output.put_line('WARNING no count on '||tab);
    row_count := 0;
         end if;
       exception
         when others then
           dbms_output.put_line('ERROR getting count on '||tab||' '||sqlerrm);
       end;
       row_count := nvl(row_count, 0);
     end if;
 --
 --  To include empty tables, force this condition to true (and another one below):
 --
     if row_count > 0 then
       insert into nic_temp_text (seq, text)
         values (i, ' ');
       i := i + 1;
       insert into nic_temp_text (seq, text)
         values (i, ' ');
       i := i + 1;
       insert into nic_temp_text (seq, text)
         values (i, tab);
       i := i + 1;
 --
       open app_tab;
       fetch app_tab into tab_id, app_id, line;
       if app_tab%found then
         in_apps := 'Y';
         insert into nic_temp_text (seq, text)
           values (i, '  '||x.owner||' ('||to_char(row_count)||')'||' '||line);
         i := i + 1;
       else
         in_apps := 'N';
         insert into nic_temp_text (seq, text)
           values (i, '  '||x.owner||' ('||to_char(row_count)||')');
         i := i + 1;
       end if;
       close app_tab;
 --
       insert into nic_temp_text (seq, text)
         values (i, ' ');
       i := i + 1;
 --
       commit;
     end if;
 --
   end;
   end if;
 --
 --  To include empty tables, force this condition to true (and another one above):
 --
   if row_count > 0 then
     line := ' '||rpad(x.column_name, 31)||rpad(x.datatype, 17)||x.nul;
     col := x.column_name;
 --
     if in_apps = 'Y' then
       open app_col;
       fetch app_col into col_id, col_desc;
       if app_col%found then
 --
         open pk;
         fetch pk into pkey;
         if pk%found then
           line := rpad(line, 52)||pkey;
         end if;
         close pk;
 --
         open fk;
         fetch fk into fkey;
         if fk%found then
           line := rpad(line, 56)||fkey;
 --
           fetch fk into fkey;
           while fk%found loop
             line := line||'  '||fkey;
             fetch fk into fkey;
           end loop;
 --
           line := line||'  '||col_desc;
         else
           line := rpad(line, 58)||col_desc;
         end if;
         close fk;
 --
       end if;
       close app_col;
     end if;
 --
     insert into nic_temp_text (seq, text)
       values (i, line);
     i := i + 1;
 --
     commit;
   end if;
 exception
   when others then
     dbms_output.put_line('ERROR '||x.table_name||' '||x.column_name||' '||sqlerrm);
 end;
 end loop;
 --
 commit;
 dbms_sql.close_cursor(x_cursor);
 dbms_output.put_line('Build done '||to_char(sysdate,'DD MON YY HH24:MI'));
 end;
/
 set trimspool on
 set termout off
 set feed off
 spool apps_tables.txt
 select text
   from nic_temp_text
   order by seq
/
 spool off
 set termout on
 set feed on
 drop table nic_temp_text
/

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips