Captures the basic DDL for a table. Does not support partitioning or IOT. Does not capture the storage clause. Just captures the basic logical layout of the table.
Sample output:
ASP> @d:\tree\tfts\tab_constructor Enter value for owner: bulkload Enter value for tabname: st_ps_job_driver create table BULKLOAD.ST_PS_JOB_DRIVER ( EMPL_ID VARCHAR2 (11), EMPL_RCD_NO NUMBER (22,38), FILL_IN VARCHAR2 (1), RECONCILE VARCHAR2 (1), DIST_ONLY VARCHAR2 (1), EC_ONLY VARCHAR2 (1), EFF_DT DATE, ); ASP>
NB: Be sure to remove the trailing comma at the end of the last column name (highlighted in red above).
After you've captured the table DDL, you can also capture the table's indices.
Note: In 9i and above, you can use the PL/SQL supplied package DBMS_METADATA to extract DDL, instead of using scripts like this one.
Script:
set heading off
set pagesize 0
set echo off
set feedback off
set verify off
col mycolumn for a100
select 'create table ' || '&&owner' || '.' || '&&tabname' || ' (' from dual
/
select column_name ||' '|| data_type ||
decode (data_type,
'DATE',NULL,
'LONG',NULL,
'LONG RAW',NULL,
'CLOB',NULL,
'BLOB',NULL,
'LOB',NULL,
' (' || data_length || decode (data_precision, null, null, ','
||data_precision) || ')'
)
|| decode(nullable,'N','NOT NULL', null)
|| ',' MYCOLUMN
from dba_tab_columns w
here table_name=upper('&tabname') and owner=upper('&owner')
ORDER BY column_id;
select '); ' mycolumn from dual
/