Re: SQL Question on convert the table structure into DML

Why don't you just execute each SQL statement one after the other and spool output to a file. It will work well. Remember to set recsep, feedback,pages, trimspool etc. Don't have access to Oracle right now but my suggestion is probabaly the most easiest. hope this helps.

defender wrote:
>
> All,
>
> I have a problem on the following SQL. the output of the structure can
> not connect as a complete SQL. So Anyone can hel me?
>
> select 0 sort2,
> 'create table '|| table_name || '('
> from user_tables
> union
> select c1.column_id sort2,
> ' ' ||
> rpad(c1.column_name,30,' ') ||
> rpad(decode((rtrim(c1.data_type) || '(' ||
> decode(c1.data_type,'NUMBER',c1.data_precision,c1.data_length)
> ||
> decode(c1.data_scale,
> null,null,
> 0,null,
> ',' || c1.data_scale) ||
> ')'),'DATE(7)','DATE','NUMBER()','NUMBER',
> (rtrim(c1.data_type) ||
> '(' ||
> decode(c1.data_type,'NUMBER',c1.data_precision,c1.data_length)
> ||
> decode(c1.data_scale,null,null,0,null,',' || c1.data_scale) ||
> ')')),15,' ') ||
> decode(c1.nullable,'NOT NULL','NOT NULL','N','NOT NULL',' ')
> ||
> ','
> from user_tab_columns c1
> where c1.column_id < (select max(column_id)
> from user_tab_columns
> where table_name = c1.table_name)
> union
> select c1.column_id sort2,
> ' ' ||
> rpad(c1.column_name,30,' ') ||
> rpad(decode((rtrim(c1.data_type) || '(' ||
> decode(c1.data_type,'NUMBER',c1.data_precision,c1.data_length)
> ||
> decode(c1.data_scale,
> null,null,
> 0,null,
> ',' || c1.data_scale) ||
> ')'),'DATE(7)','DATE','NUMBER()','NUMBER',
> (rtrim(c1.data_type) ||
> '(' ||
> decode(c1.data_type,'NUMBER',c1.data_precision,c1.data_length)
> ||
> decode(c1.data_scale,null,null,0,null,',' || c1.data_scale) ||
> ')')),15,' ') ||
> decode(c1.nullable,'NOT NULL','NOT NULL','N','NOT NULL',' ')
> ||
> ')'
> from user_tab_columns c1
> where c1.column_id = (select max(column_id)
> from user_tab_columns
> where table_name = c1.table_name)
>
> Regards,
> Wilson
 

-- 

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
        _   __         __        __
       | | / /__ ___  / /_____ _/ /_   Email :
       | |/ / -_) _ \/  '_/ _ `/ __/    venkat1_at_one.net
       |___/\__/_//_/_/\_\\_,_/\__/     venkat_rajagopal_at_yahoo.com

                  < http://w3.one.net/~venkat1 >

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++