Generating Auto generated SELECT SQL from Oracle Database
set pagesize 2000
set linesize 2000
set long 4000
--create table gensql(line number(4) not null primary key,txt varchar2(4000))
delete gensql
/
commit
/
declare
cursor cl
is
select 'CREATE TABLE '||TABLE_NAME||' ( ' tbl,
column_name clmn,
SUBSTR(decode(data_type,'VARCHAR2','VARCHAR2('||TO_CHAR(DATA_LENGTH)||')',
'CHAR','CHAR('||TO_CHAR(DATA_LENGTH)||')',
'NUMBER', DECODE(DATA_PRECISION, NULL, 'NUMBER',
'NUMBER('||TO_CHAR(DATA_PRECISION)||','||TO_CHAR(DATA_SCALE)||')'),
'DATE','DATE'),1,14) data_type,
decode(nullable, 'N', 'Not Null', null) null_stat
from cols
where table_name not in (select tname from tab where tabtype='VIEW');
flag number(1) :=0;
lineno number(4) :=0;
tbl varchar2(100);
clmn varchar2(100);
data_type varchar2(30);
null_stat varchar2(15);
lstcol varchar2(100);
begin
for c in cl loop
tbl:=c.tbl;
clmn:=c.clmn;
data_type := c.data_type;
null_stat := c.null_stat;
if flag = 0 then
-- dbms_output.put_line(tbl);
lineno:=lineno+1;
insert into gensql(line,txt) values(lineno,tbl);
flag:=1;
end if;
if lstcol <> tbl then
-- dbms_output.put_line(');');
lineno:=lineno+1;
insert into gensql(line,txt) values(lineno,');');
-- dbms_output.put_line(tbl);
lineno:=lineno+1;
insert into gensql(line,txt) values(lineno,tbl);
-- dbms_output.put_line(' '||clmn||' '||data_type||' '||null_stat);
lineno:=lineno+1;
insert into gensql(line,txt) values(lineno,' '||clmn||' '||data_type||' '||null_stat);
else
-- dbms_output.put_line(' '||clmn||' '||data_type||' '||null_stat);
lineno:=lineno+1;
insert into gensql(line,txt) values(lineno,', '||clmn||' '||data_type||' '||null_stat);
end if;
lstcol:=tbl;
end loop;
lineno:=lineno+1;
insert into gensql(line,txt) values(lineno,');');
end;
/
declare
flag number(2) := 0;
tname varchar2(100); cname varchar2(100);
tbl varchar2(100); con varchar2(100); str varchar2(2000);
lineno number(4);
cursor cmain is
select TABLE_NAME,CONSTRAINT_NAME
from user_constraints
where CONSTRAINT_TYPE='P';
cursor cdetail(k varchar2) is
select TABLE_NAME,column_name from user_cons_columns where CONSTRAINT_NAME=k;
begin
begin
select nvl(max(line),0) into lineno from gensql;
exception
when no_data_found then null;
end;
for cm in cmain loop
tbl:=cm.TABLE_NAME; con:=cm.CONSTRAINT_NAME;
str:='alter table '|| tbl ||' add constraints '||con||' primary key (';
open cdetail(con);
fetch cdetail into tname,cname;
loop
exit when cdetail%NOTFOUND;
if flag = 0 then
str:=str||cname;
flag:=1;
else
str:=str||','||cname;
end if;
fetch cdetail into tname,cname;
end loop;
str:=str||') ;';
flag:=0;
close cdetail;
-- dbms_output.put_line(str);
lineno:=lineno+1;
insert into gensql(line,txt) values(lineno,str);
end loop;
end;
/
declare
flag number(2) := 0;
tname varchar2(100); cname varchar2(100);
tbl varchar2(100); con varchar2(100); rcon varchar2(100); str varchar2(2000);
lineno number(4);
cursor cmain is
select TABLE_NAME,R_CONSTRAINT_NAME,CONSTRAINT_NAME
from user_constraints
where R_CONSTRAINT_NAME is not null;
cursor cdetail(k varchar2) is
select TABLE_NAME,column_name from user_cons_columns where CONSTRAINT_NAME=k;
begin
begin
select nvl(max(line),0) into lineno from gensql;
exception
when no_data_found then null;
end;
for cm in cmain loop
tbl:=cm.TABLE_NAME; con:=cm.CONSTRAINT_NAME; rcon:=cm.R_CONSTRAINT_NAME;
str:='alter table '|| tbl ||' add constraints '||con||' foreign key (';
open cdetail(con);
fetch cdetail into tname,cname;
loop
exit when cdetail%NOTFOUND;
if flag = 0 then
str:=str||cname;
flag:=1;
else
str:=str||','||cname;
end if;
fetch cdetail into tname,cname;
end loop;
str:=str||') ';
flag:=0;
close cdetail;
open cdetail(rcon);
fetch cdetail into tname,cname;
loop
exit when cdetail%NOTFOUND;
if flag = 0 then
str:=str||' references '|| tname || ' (';
str:=str||cname;
flag:=1;
else
str:=str||','||cname;
end if;
fetch cdetail into tname,cname;
end loop;
str:=str||') ;';
flag:=0;
close cdetail;
-- dbms_output.put_line(str);
lineno:=lineno+1;
insert into gensql(line,txt) values(lineno,str);
end loop;
end;
/
declare
tname varchar2(100); ttype varchar2(100);
txt varchar2(2000);
lineno number(4);
cursor cmain is
select distinct NAME,TYPE
from user_source;
cursor cdetail(k varchar2) is
select text from user_source where NAME=k order by line;
begin
begin
select nvl(max(line),0) into lineno from gensql;
exception
when no_data_found then null;
end;
for cm in cmain loop
tname:=cm.NAME; ttype:=cm.type;
txt:='create or replace '||ttype||' '||tname|| ' is ';
lineno:=lineno+1;
insert into gensql(line,txt) values(lineno,txt);
open cdetail(tname);
fetch cdetail into txt;
loop
exit when cdetail%NOTFOUND;
lineno:=lineno+1;
insert into gensql(line,txt) values(lineno,txt);
fetch cdetail into txt;
end loop;
close cdetail;
end loop;
end;
/
spool c:\tables.txt
select txt from gensql order by line
/
select 'create or replace view '||view_name||' as ',text from user_views
/
select 'CREATE OR REPLACE TRIGGER '||TRIGGER_NAME||
decode(instr(trigger_type,'BEFORE'),1,' BEFORE ')||
decode(instr(trigger_type,'AFTER'),1,' AFTER ')
||TRIGGERING_EVENT|| ' ON ' ||TABLE_NAME||' '
|| REFERENCING_NAMES || ' '||
decode(instr(trigger_type,'EACH ROW'),7,' FOR EACH ROW',8,' FOR EACH ROW')
|| ' '||
DECODE(NVL(WHEN_CLAUSE,'X'),'X','',' WHEN '||WHEN_CLAUSE) trigger_head
,Trigger_body
from user_triggers
/
spool off