Script for reverse eng of table

From: Shreepad Vaidya <Shreepad.Vaidya_at_alltel.com>
Date: Tue, 03 Jul 2001 05:14:32 -0700
Message-ID: <F001.0033FC08.20010703044550@fatcity.com>

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;

Received on Tue Jul 03 2001 - 07:14:32 CDT