OraFAQ Forum: SQL & PL/SQL » Extract CREATE TABLE statements from USER_TAB

Home » SQL & PL/SQL » SQL & PL/SQL » Extract CREATE TABLE statements from USER_TAB_COLUMNS (11g, linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Extract CREATE TABLE statements from USER_TAB_COLUMNS [message #381417]

Fri, 16 January 2009 17:17

tmcallister
Messages: 107
Registered: December 2007

Senior Member

I'm trying to extract some create table statements from my database, however I'm running into the 4000 varchar2 limit within SYS_CONNECT_BY_PATH, and as far as I can tell there is absolutely nothing that I can do about it.

Here is the query that I have so far:

SELECT        'create table '
           || UPPER (table_name)
           || CHR (10)
           || '('
           || CHR (10)
           || SUBSTR (SYS_CONNECT_BY_PATH (   column_name
                                           || CHR (9)
                                           || data_type
                                           || DECODE (data_type,
                                                      'DATE', '',
                                                      'NUMBER', '('
                                                       || data_precision
                                                       || ','
                                                       || data_scale
                                                       || ')',
                                                         '('
                                                      || data_length
                                                      || ')'
                                                      || DECODE (nullable,
                                                                 'N', CHR (9)
                                                                  || 'NOT NULL'
                                                                )
                                                      || DECODE (column_id,
                                                                 cnt, '',
                                                                 ','
                                                                )
                                                     ),
                                           '
           '
                                          ),
                      0
                     )
           || CHR (10)
           || ');'
      FROM (SELECT table_name, column_name, data_type, data_length,
                   data_precision, data_scale, column_id, nullable,
                   COUNT (*) OVER (PARTITION BY table_name) cnt
              FROM user_tab_columns)
     WHERE column_id = cnt
START WITH column_id = 1
CONNECT BY PRIOR column_id + 1 = column_id AND PRIOR table_name = table_name;

It works, except when you get an exceptionally large table (which I happen to have a few of) where the result of the SYS_CONNECT_BY_PATH clause is >4000 characters.

Another relatively minor issue is that I cannot find anyway to get the connecting character to be a carriage return except by using: '
'
Which I *REALLY* don't like, but I cannot get it to take a CHR() code however I try to format it.

Any tips/thoughts?

I suppose my only real option is to drop into pl/sql and use a cursor ... I really prefer to keeps things straight sql whenever possible Sad

[Updated on: Fri, 16 January 2009 17:24]