Hi,
This script for reverse eng of tables works on 7.3 .........not on
8.0 or above .....
HTH.
shreepad
(See attached file: ddltab.sql)
-- This script is created to obtain the ddl for the tables.
-- By SHREE.
-- Not Nulls are not included in syntax as they are picked up in a
different script .
declare
v_table_name VARCHAR2(30) ;
v_sqlfhandle UTL_FILE.FILE_TYPE ;
-- Pls ensure that your init.ora parameter "utl_file_dir" to what
you want . In my example it is set as
-- /hildb1/dbdump4/ghi100
v_filedir VARCHAR2(70) := '/hildb1/dbdump4/ghi100' ;
v_sqlfname VARCHAR2(70);
-- This script gives the text for the create statements for a table
-- Created by shree
v_owner dba_tables.owner%type;
v_tablename dba_tables.table_name%type;
v_tablespacename dba_tables.tablespace_name%type;
v_inittrans dba_tables.ini_trans%type;
v_maxtrans dba_tables.max_trans%type;
v_pctfree dba_tables.pct_free%type;
v_pctused dba_tables.pct_used%type;
v_pctinc dba_tables.pct_increase%type;
v_initial dba_tables.initial_extent%type;
v_next dba_tables.next_extent%type;
v_min dba_tables.min_extents%type;
v_max dba_tables.max_extents%type;
v_tab dba_tab_columns.table_name%type;
v_col dba_tab_columns.column_name%type;
v_type varchar2(2000) ;
v_len dba_tab_columns.default_length%type;
v_col1 dba_tab_columns.column_name%type;
v_type1 dba_tab_columns.data_type%type;
v_precision dba_tab_columns.data_precision%type;
v_scale dba_tab_columns.data_scale%type;
v_datadef long ;
v_sync varchar2(2000);
v_sync1 varchar2(2000) ;
v_sync2 varchar2(2000) ;
v_def dba_tab_columns.default_length%type ;
v_cid dba_tab_columns.column_id%type ;
v_cid1 dba_tab_columns.column_id%type ;
v_null varchar2(30);
cursor c1 is
select owner,table_name,tablespace_name,ini_trans,max_trans,
pct_free,pct_used,pct_increase,initial_extent,next_extent,min_extents,
max_extents
from dba_tables
where owner='<schema_name>'
-- In case you want a ddl for a particular table remove the comments
from the line below.
-- and table_name in ('<table_name>')
order by table_name;
cursor c2(v_tablename varchar2,v_owner varchar2) is
select column_name,
decode(data_type,'LONG',' LONG ',
'RAW',' RAW ' || '(' || data_length || ')' ,
'LONG RAW','LONG RAW' ,
'DATE' , ' DATE ' ,
'CHAR' , ' CHAR' || '(' || data_length || ')',
'VARCHAR2', ' VARCHAR2' || '(' || data_length || ')' ,
'NUMBER' , ' NUMBER ' ) datatyp,
decode(nvl(data_precision,0),' ' , '(' || data_precision ),
decode(nvl(data_scale,0), ')' , ',' || data_scale || ')' ),
decode(nullable,'N','NOT NULL',' ') ,
data_default,nvl(default_length,0)
from dba_tab_columns where table_name=v_tablename
and owner=v_owner
order by column_id ;
cursor c3 (v_tablename varchar2,v_owner varchar2) is
select max(column_id) from dba_tab_columns where owner=v_owner
and table_name=v_tablename;
begin
open c1;
loop
v_sync:=' ' ;
v_sync1:=' ' ;
fetch c1 into v_owner,v_tablename,v_tablespacename,v_inittrans,
v_maxtrans,v_pctfree,v_pctused,v_pctinc,v_initial,v_next,v_min,v_max;
exit when c1%notfound;
v_sqlfname:=v_tablename || '_tab'|| '.sql' ;
v_sqlfhandle := UTL_FILE.FOPEN(v_filedir, v_sqlfname, 'w');
v_sync:='create table ' || v_owner|| '.'||v_tablename || '(' ;
UTL_FILE.PUTF(v_sqlfhandle, '%s\n',
'------ This script has been created by Shree -------------');
UTL_FILE.PUTF(v_sqlfhandle, '%s\n',
'
----------------------------------------------------------------------
-----');
UTL_FILE.PUTF(v_sqlfhandle, '%s\n', v_sync);
v_sync:=' ' ;
open c2(v_tablename,v_owner);
open c3(v_tablename,v_owner);
fetch c3 into v_cid;
loop
fetch c2 into v_col,v_type,v_precision,v_scale,
v_null,v_datadef,v_len;
exit when c2%notfound;
v_sync:=' ' ;
if v_cid=c2%rowcount
then
if v_len=0
then
v_sync:=v_col || v_type || v_precision || v_scale ;
else
v_sync:=v_col || v_type || v_precision || v_scale || ' default '
||
v_datadef ;
end if;
else
if v_len=0
then
v_sync:=v_col || v_type || v_precision || v_scale || ' ,' ;
else
v_sync:=v_col || v_type || v_precision || v_scale || ' default '
||
v_datadef || ' , ' ;
end if;
end if;
UTL_FILE.PUTF(v_sqlfhandle, '%s\n',v_sync);
end loop;
close c2;
close c3;
UTL_FILE.PUTF(v_sqlfhandle, '%s\n',' ) ' );
v_sync1:=' ' ;
v_sync1:=' tablespace ' || v_tablespacename
|| ' INITRANS '|| v_inittrans
|| ' MAXTRANS ' || v_maxtrans ||
' PCTFREE ' || v_pctfree || ' PCTUSED ' || v_pctused ||
' STORAGE ' || '(' ||
-- Initial has been commented
' INITIAL ' || v_initial ||
' NEXT ' || v_next ||
' MINEXTENTS '|| v_min ||
' MAXEXTENTS ' || v_max || ' PCTINCREASE ' || v_pctinc || ')'|| ';';
UTL_FILE.PUTF(v_sqlfhandle, '%s\n',v_sync1 );
UTL_FILE.PUTF(v_sqlfhandle, '%s\n',
'
----------------------------------------------------------------------
---');
v_sync2:=' ' ;
v_sync2:='Create public synonym ' || v_tablename || ' for '|| v_owner
|| '.' ||
v_tablename || ' ;' ;
UTL_FILE.PUTF(v_sqlfhandle, '%s\n',v_sync2 );
UTL_FILE.FFLUSH(v_sqlfhandle);
v_sync2:=' ' ;
v_sync2:='grant select,insert,delete,update on oradba.' || v_tablename
||
' to Sysprocess ; ' ;
UTL_FILE.PUTF(v_sqlfhandle, '%s\n',v_sync2 );
UTL_FILE.PUTF(v_sqlfhandle, '%s\n',
'-------------------------- End of
Script---------------------------------');
UTL_FILE.FFLUSH(v_sqlfhandle);
UTL_FILE.FCLOSE(v_sqlfhandle);
end loop;
close c1;
end;
/
ddltab.sql
