SQL Question on convert the table structure into DML

From: defender <defender_at_netvigator.com>
Date: 2000/04/23
Message-ID: <8duael$65u1_at_imsp212.netvigator.com>#1/1

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 Received on Sun Apr 23 2000 - 00:00:00 CEST