Home » SQL & PL/SQL » SQL & PL/SQL » Extract CREATE TABLE statements from USER_TAB_COLUMNS (11g, linux)
Extract CREATE TABLE statements from USER_TAB_COLUMNS [message #381417] Fri, 16 January 2009 17:17 Go to next message
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]

Report message to a moderator

Re: Extract CREATE TABLE statements from USER_TAB_COLUMNS [message #381418 is a reply to message #381417] Fri, 16 January 2009 17:36 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
Why are you trying to re-invent the wheel?

DBMS_METADATA.GET_DDL is provided by Oracle to the same thing.
Re: Extract CREATE TABLE statements from USER_TAB_COLUMNS [message #381420 is a reply to message #381418] Fri, 16 January 2009 18:20 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Yes apparently I am, and no there is no reason for me to.

Quick question though, is there a way to simplify/dumb down the result?

I.e. given:
CREATE TABLE deleteme
( col1 VARCHAR2(32) );

commit;

select dbms_metadata.GET_DDL('TABLE','DELETEME','<REMOVED>') from dual;



I would like to see:

CREATE TABLE deleteme
( col1 VARCHAR2(32) );


Rather than:

  CREATE TABLE "<removed>"."DELETEME" 
   (	"COL1" VARCHAR2(32)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "<removed>" 


White spaces/formatting does not matter, however I'd like to get rid of everything after and including PCTFREE...

[Updated on: Fri, 16 January 2009 18:21]

Report message to a moderator

Re: Extract CREATE TABLE statements from USER_TAB_COLUMNS [message #381422 is a reply to message #381417] Fri, 16 January 2009 18:46 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
And to 'almost' answer my own question:

CREATE TABLE deleteme
( col1 VARCHAR2(32) );

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

select dbms_metadata.GET_DDL('TABLE','DELETEME') || ';' from dual;


Gets me to

  CREATE TABLE "<SCHEMA>"."DELETEME" 
   (	"COL1" VARCHAR2(32)
   ) ;


How do I get rid of "<SCHEMA>"?

Can't find anything here: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_metada.htm#BGBJBFGE

I suppose REPLACE could work but that seems dangerous...

SELECT REPLACE (DBMS_METADATA.get_ddl ('TABLE', 'DELETEME'), '"<SCHEMA>".', '')
  FROM DUAL;

[Updated on: Fri, 16 January 2009 18:47]

Report message to a moderator

Re: Extract CREATE TABLE statements from USER_TAB_COLUMNS [message #381444 is a reply to message #381422] Sat, 17 January 2009 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know other way to do it but it is safer if you use a longer string something like:
replace(..., 'CREATE TABLE "SCHEMA".','CREATE TABLE ')

Regards
Michel
Re: Extract CREATE TABLE statements from USER_TAB_COLUMNS [message #381756 is a reply to message #381444] Mon, 19 January 2009 12:07 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Yeah I'd be more comfortable using the longer string, that should be fine.

So this is what I'm using:

prompt SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON LINESIZE 8192 ARRAYSIZE 5000 TERMOUT OFF VERIFY OFF
prompt EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);
prompt EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
prompt EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',false);
prompt EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false);

prompt spool create_tables.sql

SELECT      'SELECT REPLACE (DBMS_METADATA.get_ddl (''TABLE'', '''
         || table_name
         || '''), ''CREATE TABLE "EXPR".'', ''CREATE TABLE '') from dual;'
    FROM user_tables
ORDER BY table_name;

prompt spool off

spool off

@ddl_table_extract


However the output isn't what I'd expect... Every single table is cut off on the second line...

Result from the sql spool script:

  CREATE TABLE "APC_AREA_OFFICE"
   (	"CODE" VARCHAR2(12),
	"VALUE" VARCHAR2(


  CREATE TABLE "APC_ENVIRONMENT"
   (	"CODE" VARCHAR2(12),
	"VALUE" VARCHAR2(


Result from manual
  CREATE TABLE "APC_AREA_OFFICE" 
   (	"CODE" VARCHAR2(12), 
	"VALUE" VARCHAR2(50)
   ) ;


  CREATE TABLE "APC_ENVIRONMENT" 
   (	"CODE" VARCHAR2(12), 
	"VALUE" VARCHAR2(50)
   ) ;


Any idea what is up? Does spool not interact well with clobs or ...?


Re: Extract CREATE TABLE statements from USER_TAB_COLUMNS [message #381758 is a reply to message #381756] Mon, 19 January 2009 12:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set long 10000

Regards
Michel

[Updated on: Mon, 19 January 2009 12:17]

Report message to a moderator

Re: Extract CREATE TABLE statements from USER_TAB_COLUMNS [message #381759 is a reply to message #381756] Mon, 19 January 2009 12:14 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Yep I guessed right, google: oracle spool clob leads me to: http://www.orafaq.com/wiki/SQL*Plus_FAQ#Something.27s_wrong_with_my_CLOB.2F_NCLOB.2F_LONG_column._Where_is_my_data.3F

Setting

I need to change
prompt SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON LINESIZE 8192 ARRAYSIZE 5000 TERMOUT OFF VERIFY OFF


to
prompt SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON LINESIZE 8192 LONG 8192 ARRAYSIZE 5000 TERMOUT OFF VERIFY OFF


And it works like a charm.
Re: Extract CREATE TABLE statements from USER_TAB_COLUMNS [message #381760 is a reply to message #381759] Mon, 19 January 2009 12:15 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Wow I thought I got my answer fast, but you were faster Razz.

Thx!
Re: Extract CREATE TABLE statements from USER_TAB_COLUMNS [message #381761 is a reply to message #381760] Mon, 19 January 2009 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But why don't use directly:
SELECT REPLACE (DBMS_METADATA.get_ddl ('TABLE', table_name), 'CREATE TABLE "EXPR".', 'CREATE TABLE ')
    FROM user_tables
ORDER BY table_name;


Regards
Michel
Re: Extract CREATE TABLE statements from USER_TAB_COLUMNS [message #381762 is a reply to message #381761] Mon, 19 January 2009 12:34 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
A very good question... Don't know why I got hooked into the 2 step process.

Thx again!
Re: Extract CREATE TABLE statements from USER_TAB_COLUMNS [message #381768 is a reply to message #381762] Mon, 19 January 2009 13:01 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Did my research in advance on this one, and it's stumping me so far.

There seems to be some interaction between spool and clobs that is causing it to ignore linesize and print out the data in 80 character chunks.

SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON LINESIZE 8192 LONG 8192 ARRAYSIZE 5000 TERMOUT OFF VERIFY OFF
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',false);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false);

-- create indexes DDL
set linesize 8192 -- set it again just in case
spool create_indexes.sql

SELECT   REPLACE (REPLACE (DBMS_METADATA.get_ddl ('INDEX', index_name),
                           'INDEX "<SCHEMA>".',
                           'INDEX '
                          ),
                  'ON "<SCHEMA>".',
                  'ON '
                 )
    FROM user_indexes
ORDER BY table_name;

spool off


Any thoughts?

[Updated on: Mon, 19 January 2009 13:04]

Report message to a moderator

Re: Extract CREATE TABLE statements from USER_TAB_COLUMNS [message #381769 is a reply to message #381768] Mon, 19 January 2009 13:14 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Try giving an alias to your SELECT statement, say XXX.
Then do a set command for that alias to say 1000 characters
col XXX format a1000

select {your dbms_metadata statement} XXX from user_indexes...;


See if that helps.
Re: Extract CREATE TABLE statements from USER_TAB_COLUMNS [message #381772 is a reply to message #381768] Mon, 19 January 2009 13:25 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
I'm confused. I added in additional SET commands and it still didn't work, then I added more, and it worked, then I removed ALL the extra SET commands ... and it still works...

As long as it works I'm happy - but I'm still confused...
Re: Extract CREATE TABLE statements from USER_TAB_COLUMNS [message #381773 is a reply to message #381417] Mon, 19 January 2009 13:29 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
What happens in a new/different session?
Re: Extract CREATE TABLE statements from USER_TAB_COLUMNS [message #381775 is a reply to message #381768] Mon, 19 January 2009 13:42 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set longchunksize <your line size>

Regards
Michel
Previous Topic: A challenging question
Next Topic: Update attributes, ORA-01733
Goto Forum:
  


Current Time: Mon Dec 05 09:12:05 CST 2016

Total time taken to generate the page: 0.10711 seconds