header image

Generating Auto generated SELECT SQL from Oracle Database

Posted by: | August 27, 2008 | No Comment |

 

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

under: 9548