gabsaga_tata (AT) hotmail (DOT) com (Taji) wrote in

Try something like the following. %3 is the owner and %1 is the
tablename. This does not do partition, iot's and is also missing
constraints, support for more exotic data types and so on. But it does
the vanilla basics.

You may want to change the chr(13) into a proper linefeed too.

CREATE TABLE %3.%1 ' "LINE"
FROM dual
UNION ALL
SELECT
'('
FROM dual
UNION ALL
SELECT
*
FROM (
SELECT
' ' || RPAD( LOWER(t1.column_name), 30 ) ||
RPAD(
DECODE( t1.data_type,
'DATE', t1.data_type,
'NUMBER', t1.data_type ||'(' || t1.data_precision ||
DECODE( t1.data_scale,0, ')', ',' || t1.data_scale || ')' ) ,
t1.data_type ||'(' || t1.data_length || ')'
) , 20 ) ||
DECODE( t1.nullable, 'Y', '',
'NOT NULL') ||
DECODE( t2.maxcol, NULL, ',' , '' ) "COLUMN"
FROM all_tab_columns t1,
(SELECT MAX(column_id) MAXCOL FROM all_tab_columns
WHERE table_name = '%1'
AND owner = '%3' ) t2
WHERE t1.table_name = '%1'
AND t1.owner = '%3'
AND t1.column_id = t2.maxcol (+)
ORDER BY t1.column_id )
UNION ALL
SELECT
')'
FROM dual
UNION ALL
SELECT
'TABLESPACE ' || lower(a.tablespace_name)
FROM all_tables a
WHERE a.table_name = '%1'
AND a.owner = '%3'
UNION ALL
SELECT
DECODE( a.logging, 'YES', 'LOGGING', 'NOLOGGING' ) || chr(13) ||
'PCTFREE ' || a.pct_free || chr(13) ||
'PCTUSED ' || a.pct_used || chr(13) ||
'INITRANS ' || a.ini_trans || chr(13) ||
'MAXTRANS ' || a.max_trans || chr(13) ||
'PARALLEL ( degree ' || LTRIM(a.degree) || ' instances ' ||
LTRIM(a.instances) || ' )' || chr(13) ||
'STORAGE(' || chr(13) ||
' initial ' || a.initial_extent || chr(13) ||
' next ' || a.initial_extent || chr(13) ||
' minextents ' || a.min_extents || chr(13) ||
' maxextents ' || a.max_extents || chr(13) ||
' pctincrease ' || a.pct_increase || chr(13) ||
' freelists ' || a.freelists || chr(13) ||
' buffer_pool ' || a.buffer_pool || chr(13) ||
' freelist groups ' || a.freelist_groups || chr(13) ||
' )'
FROM all_tables a
WHERE a.table_name = '%1'
AND a.owner = '%3'


--
Billy

Well, finally got it work and if anyone was wondering, here is the
code.

select column_name,
decode (nullable, 'Y', 'NULL','NOT NULL') as "Null?",
RTRIM(decode(data_type || '(' || data_precision ||
decode(data_scale,0,'',null,'',',' || data_scale) || ')',
'DATE()','DATE',
'VARCHAR2()', data_type || '(' || data_length || ')' ,
data_type || '(' || data_precision ||
decode(data_scale,0,'',null,'',',' || data_scale) || ')')) as type
from dba_tab_columns where table_name = 'TABLE_NAME_GOES_HERE' order
by column_id


Taji


Sybrand Bakker <gooiditweg (AT) sybrandb (DOT) demon.nl> wrote