--
-- 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
/
Return to : Oracle
Database, SQL, Application, Programming Tips