REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	c_tab_rec_plsql.sql
REM
REM <SQLDIR_GRP>REVERSE TAB SEGMENT</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy (vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Reverse Engineer Table DDL
REM	</SQLDIR_TXT>
REM
REM Usage:
REM	c_tab_rec_plsql.sql
REM 
REM Example:
REM	c_tab_rec_plsql.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM


set serveroutput on size 100000


accept shema_owner char prompt "Enter Shema Owner Name [Enter For All]:"
accept tab_name char prompt "Enter Table Name [Enter For All]:"

declare
   cursor tab_name_cursor is
    select owner,
           table_name
    from   dba_tables
    where  owner = nvl(upper('&shema_owner'), owner)
    and    table_name = nvl(upper('&tab_name'), table_name);
  
  cursor tab_col_cursor (in_owner VARCHAR2, in_table_name VARCHAR2) is
   select cl.column_name                                                                        col_name
   ,	decode(cl.data_type,
                 'CHAR','CHAR('||cl.data_length||')',
              'VARCHAR','VARCHAR('||cl.data_length||')',
             'VARCHAR2','VARCHAR2('||cl.data_length||')',
               'NUMBER',decode(cl.data_precision,
                                  NULL,'NUMBER'
                                      ,'NUMBER('||cl.data_precision||','||cl.data_scale||')')
                       ,cl.data_type)                                                           d_type
   ,    decode(cl.nullable,
                 'Y','NULL',
                 'N','NOT NULL')||decode(cl.column_id,
                                            max(gr.column_id),');'
                                                             ,',' )                             nn_or_n
		from dba_tab_columns          cl
		,    dba_tab_columns          gr
		where cl.owner = in_owner
		and   cl.table_name = in_table_name
		and   cl.table_name = gr.table_name
		group by cl.owner,
		         cl.table_name,
		         cl.column_name,
		         cl.data_type,
		         cl.data_length,
		         cl.data_precision,
		         cl.data_scale,
		         cl.nullable,
		         cl.column_id
		order by cl.column_id;
begin
   for tab_name_rec in tab_name_cursor
   loop
    dbms_output.put_line('--             ');
    dbms_output.put_line('create table '||tab_name_rec.owner||'.'||tab_name_rec.table_name||'_new (');
    for tab_col_rec in tab_col_cursor(tab_name_rec.owner, tab_name_rec.table_name)
    loop
     dbms_output.put_line(' '||rpad(tab_col_rec.col_name,40)||rpad(tab_col_rec.d_type,20)||tab_col_rec.nn_or_n||' ');
    end loop;
   end loop;
end;
/