Skip to Main Content
  • Questions
  • Using dbms_metadata.get_ddl - getting rid of carriage returns

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: December 27, 2004 - 11:47 am UTC

Last updated: October 26, 2017 - 12:42 am UTC

Version: 9.2.0.5

Viewed 50K+ times! This question is

You Asked

I am currently using dbms_metadata to get VIEWS DDLs (in order to re-create them following a FROMUSER-TOUSER import; compilation errors due to in-text schema names). However, the generated file (spool) is full of carriage-returns which makes mandatory a very tedious editing step (splitting the text using the continuation character "-").

How to get rid of them, such that the outcome-spool can be re-used straightforward ?

How to cope with an "Input line too long (>24xx)" issue (got some text_length of more than 3100)?

I use :

set long 200000 pages 0 lines 131
spo v.sql
select dbms_metadata.get_ddl('VIEW','SOMEVIEWNAME') from dual;
spo off

Thanx for yr forthcoming saving-time answer.

and Tom said...

set long 200000 pages 0 lines 131
column txt format a121 word_wrapped
spo v.sql
select dbms_metadata.get_ddl('VIEW','DBA_SEGMENTS') txt from dual;
spo off


how does that work for you....
column txt format a121 word_wrapped


Rating

  (47 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, December 28, 2004 - 3:39 am UTC

Well, I still have the pb for a few views.

However, it is OK for the first part of the DDL (CREATE VIEW ...) but not for the second half (AS SELECT ...) : carriage returns are appended cutting inadequately field names.


Tom Kyte
December 28, 2004 - 10:28 am UTC

the "pb" ?!? had to stare at that for a couple of seconds to realize what you meant. anyway.


I need a for example please -- can you reproduce this using the data dictionary views.



try this

another reader, December 28, 2004 - 10:51 am UTC

If I understand what you are running into, I had a similar problem and solved it by setting:
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',false);


Tom Kyte
December 28, 2004 - 11:09 am UTC

thanks, hope they try that out.

Bernard H, December 28, 2004 - 12:00 pm UTC

I am sorry but the following still does not work

SQL> set long 200000 pages 0 lines 131
SQL> col txt for a121 word_wrapped
SQL> begin
  2  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',false);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select DBMS_METADATA.get_ddl('VIEW','S_D_TRANSCOM_DOCUMENT_SP') txt from dual;

The first half (CREATE VIEW ...) is OK (semicolon at every line end) but the second half is NOT (field names are cut by carriage returns.

e.g.,
   
OD_.R_OBJECT
_TYPE,

instead of OD_.R_OBJECT_TYPE,
 

Tom Kyte
December 28, 2004 - 2:17 pm UTC

give me an example to work with on my own machine.

do you have views like:

select c1,c2,c3,c4,c5,c6......................................................

(eg: one big old line forever with not a whitespace to be seen?)

if there is no whitespace -- word_wrapping ceases to be "an option".

perhaps you need to widen your column to accomidate the orginal view text.

Bernard H, December 29, 2004 - 5:18 am UTC

Yes, I do have this OLD one-line no-whitespaces text (by the way, why do u call it OLD ?)

So, I tried the following :

SQL> select text_length from user_views where view_name ='S_D_TRANSCOM_DOCUMENT_SP';

TEXT_LENGTH
-----------
       3174

1 row selected.

SQL> set lines 3174
SQL> col txt for a3174
SQL> begin      
  2  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',false);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select DBMS_METADATA.get_ddl('VIEW','S_D_TRANSCOM_DOCUMENT_SP') txt from dual
CREATE OR REPLACE FORCE VIEW "TCMTMPL"."S_D_TRANSCOM_DOCUMENT_SP" ...
...
RROR", "CM_PDFSTORE", "CM_KEY") AS SELECT -- whitespaces here --
OD_.R_OBJECT_ID,OD_.OBJECT_NAME,OD_.R_OBJECT_TYPE, ...


So, ON SCREEN, everything seems to be on a SINGLE line.

If I do SPOOLING and run "wc -l spoolfilename.sql", I got 8 (i.e., 8 lines) however (?????)

If I take a look at the spool file "cat spoolfilename.sql", the output is different from the previous
ON-SCREEN display :


CREATE OR REPLACE FORCE VIEW "TCMTMPL"."S_D_TRANSCOM_DOCUMENT_SP" ...
... 
RROR", "CM_PDFSTORE", "CM_KEY") AS SELECT -- whitespaces here --
-- a line of whitespaces here --
OD_.R_OBJECT_ID,OD_.OBJECT_NAME,OD_.R_OBJECT_TYPE, ...
......
-- and a lot of whitespaces spreading over several lines --



Moreover, I cannot play the DDL back (sta spoolfilename.sql) because of 2 :

SP2-0027: Input is too long (> 2499 characters) - line ignored
SP2-0027: Input is too long (> 2499 characters) - line ignored

Note that I cannot edit the DDL in vi (unix) either : Line too long


By the way, how to get rid of statement echoing in SQL+ : set echo off (does not work).  Thx.
 

Tom Kyte
December 29, 2004 - 10:10 am UTC

"big old line of text" is a "colloquialism" sorry about that, I'm trying to avoid those (eg: sayings like "6 one way, 1/2 dozen the other" -- mess up people in the same way "records in the 100's of lakh's" messes me up -- I still don't know how many that is...)

how big is your biggest view (and what/who created it?)

A reader, December 29, 2004 - 10:29 am UTC

Metalink Bug 3330599

DBMS_METADATA produces unusable output

Range of versions believed to be affected Versions < 10.2

This issue is fixed in
9.2.0.6 (Server Patch Set)
10.1.0.4 (Server Patch Set)
10g Release 2 (future version)


Description:
------------
DBMS_METADATA can produce unusable output in that keywords and values may be split across lines such that the output has to be editted before it can be executed as SQL.



Tom Kyte
December 29, 2004 - 10:39 am UTC

you cannot see it -- but the resolution was all about:

a) give column alias
b) use column alias_name format aNNNNNNNN word_wrapped

they were getting "unusable output" because sqlplus was wrapping at 80 characters.


the other issue that is fixed later is an issue with the DEFAULT clause -- not an issue here.


so, this does not apply.

Bernard H, December 29, 2004 - 10:53 am UTC

SQL> select max(text_length) from user_views;

MAX(TEXT_LENGTH)
----------------
            3174

Is that the wanted information ?

=============

Could u explain the following error :

SP2-0027: Input is too long (> 2499 characters) - line ignored

Any parameter to increase/set/unset to allow over 2499 chars ??????

================
how to get rid of statement echoing in SQL+ : set echo off (does not go for it)

============ END. 

Tom Kyte
December 29, 2004 - 6:53 pm UTC

the error seems obvious?  you have a line longer than 2499 characters (do you have trimspool on?  else spool files have a fixed length line, that of set linesize).  that is a fixed limit in sqlplus. 

in the spool file, set echo off does go for it.  put this in a script:

spool foo
set echo on
select 'hello world 1' from dual;
set echo off
select 'hello world 2' from dual;
spool off

run it and you should see:


ops$tkyte@ORA9IR2> !cat foo.lst
<b>ops$tkyte@ORA9IR2> select 'hello world 1' from dual;
 
'HELLOWORLD1'
-------------
hello world 1
 
ops$tkyte@ORA9IR2> set echo off</b>
 
'HELLOWORLD2'
-------------
hello world 2
 

stuff from echo on in bold, echo off -- no query to be seen.


(your keyboard is failing there -- Y and O are really important characters in this language, without them -- many words become hard to read)




you can 

select replace( dbms_lob.substr( dbms_metadata...., 4000, 1 ), ',', ', ' ) txt
  from dual;

make that big long line have whitespace in it so you can word wrap it.
 

Bernard H, December 29, 2004 - 10:54 am UTC

SQL> select max(text_length) from user_views;

MAX(TEXT_LENGTH)
----------------
            3174

Is that the wanted information ?

=============

Could u explain the following error :

SP2-0027: Input is too long (> 2499 characters) - line ignored

Any parameter to increase/set/unset to allow over 2499 chars ??????

================
how to get rid of statement echoing in SQL+ : set echo off (does not go for it)

=================
These are bespoke/Application views.

============ END. 

Bernard H, December 30, 2004 - 9:09 am UTC

SQL> set lines 4000 long 200000 pages 0
SQL> col txt for a4000 word_wrapped    
SQL> begin
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',false);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
end;
/  2    3    4    5  

PL/SQL procedure successfully completed.


SQL> sho echo
echo OFF
SQL> set echo off
SQL> sho echo
echo OFF
SQL> spo toto.sql
SQL> select replace( dbms_lob.substr(DBMS_METADATA.get_ddl('VIEW','S_D_TRANSCOM_DOCUMENT_SP'), 4000,1), ',', ', ' ) txt from dual;
....
SQL> spo off
SQL> !wc -l toto.sql
       6 toto.sql

SQL> !wc -c toto.sql
    4157 toto.sql

SQL> sta toto.sql 
SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.
SP2-0027: Input is too long (> 2499 characters) - line ignored
SP2-0734: unknown command beginning "SQL> spo o..." - rest of line ignored.
SQL> 



==========

NOTE that "set echo off" DOES NOT WORK AT ALL as shown by the two "SP2-0734" errors (I am running 9205 on AIX 5.2).
and I am STILL unable to play back the resulting DDL (SP2-0027).

==========
Any clue/help mostly appreciated. 

Tom Kyte
December 30, 2004 - 11:03 am UTC

put it IN A SCRIPT (i think i said that, lookup up, yes I can see "put this in a script")

Bernard H, December 30, 2004 - 9:17 am UTC

I am trying a workaround (i.e., forgetting about dbms_metadata) :

Here is the script listing (getaview.sql) :

set heading off
set feedback off
set linesize 1000
set trimspool on
set verify off
set termout off
set embedded on
set long 200000
set pages 0

column column_name format a1000
column txt format a1000 word_wrapped

spool &1..sql
prompt create or replace view &1 (
select column_name||', ' column_name
from user_tab_columns
where table_name = upper('&1')
order by column_id
/
prompt ) as
select replace(text,',',', ') txt
from user_views
where view_name = upper('&1')
/
prompt /
spool off

set heading on
set feedback on
set verify on
set termout on
set pages 33

=============
However, I run into the following problem (on-screen output):

CM_BUILDERROR,
CM_PUBLISHERROR,
CM_PDFSTORE,
CM_KEY,
) as
select replace(text,',',', ') txt
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

How to deal with this (any other function to be used ?)

Thx.


Tom Kyte
December 30, 2004 - 11:18 am UTC

see above, if set echo off doesn't work, gee, wonder how this works? according to you it wouldn't work, couldn't work.... the sql would just be echoed into the file itself.


first of all

o do you have TRIMSPOOL on (for the 2499 problem - lets revist THAT please)



set linesize 2499
use spool
FROM A SCRIPT
with trimspool
using the TXT alias
with column TXT format a2490 word_wrapped

unless your list of columns is over an amazing 2499 characters, that'll do it straight up --

Bernard H, December 30, 2004 - 11:55 am UTC

SQL> !cat z.sql
set long 200000 pages 0 lines 2499 trimspool on echo off
col txt for a2499 word_wrapped
begin
 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',false);
 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

end;
/
spo z.lst
select replace(dbms_lob.substr(DBMS_METADATA.get_ddl('VIEW','S_D_TRANSCOM_DOCUMENT_SP'), 4000,1), ',', ', ' ) txt from dual;

spo off

SQL> sta z.sql


SQL> !wc -l z.lst
       4 z.lst

SQL> !wc -c z.lst
    3999 z.lst

SQL> sta z.lst
SQL> 
SQL> select object_type, object_name, created, status from user_objects where object_name ='S_D_TRANSCOM_DOCUMENT_SP';

no rows selected


====> In fact, it does not get executed (?)

When I look at the z.lst file (via the "more" cmd), there are a lot of blank lines then the view DDL starts but I do not
see the second half of the DDL "i.e., AS SELECT ...... ;"




 

Tom Kyte
December 30, 2004 - 2:08 pm UTC

because the expansion of your view exceeed 4,000 characters.  Not going to happen with SQL then.

using my word_wrap routine, and a bit of plsql, we can print it out.


I created a nasty view:

ops$tkyte@ORA9IR2> set long 500
ops$tkyte@ORA9IR2> select text from user_views where view_name = 'V';
 
TEXT
-------------------------------------------------------------------------------
select 1 id,1"C00000000000000000000000000001",1"C00000000000000000000000000002"
,1"C00000000000000000000000000003",1"C00000000000000000000000000004",1"C0000000
0000000000000000000005",1"C00000000000000000000000000006",1"C000000000000000000
00000000007",1"C00000000000000000000000000008",1"C00000000000000000000000000009
",1"C00000000000000000000000000010",1"C00000000000000000000000000011",1"C000000
00000000000000000000012",1"C00000000000000000000000000013",1"C00000000000000000
000000000014",1"C000000000
 
(the columns are mashed together, 74 of them, really long...)



set long 500000
declare
    l_clob long;
    procedure word_wrap( p_string in varchar2, p_len in number default 65 )
    as
        l_string    long default replace( p_string || chr(10), chr(9), '    ' );
        l_result    long default NULL;
        l_piece     long;
        l_ws        varchar2(25) default ' ' || chr(9);
        l_sep       varchar2(5) default NULL;
        n           number;
    begin
    loop
        exit when l_string is NULL;
                                                                                                                   
        n := instr( l_string, chr(10) );
        l_piece := substr( l_string, 1, n-1 );
        l_string := substr( l_string, n+1 );
                                                                                                                   
        loop
            exit when l_piece is NULL;
            n := length(l_piece);
            if ( n > p_len ) then
                n := instr( substr(
                              translate(l_piece,l_ws,rpad(' ',length(l_ws))),
                                   1, p_len ), ' ', -1);
                if ( nvl(n,0) = 0 ) then
                    n := p_len;
                end if;
            end if;
            dbms_output.put_line( substr( l_piece, 1, n ) );
            l_piece := substr( l_piece, n+1 );
        end loop;
    end loop;
    end;
begin
    l_clob := dbms_lob.substr( dbms_metadata.get_ddl( 'VIEW', 'V' ), 32760, 1 );
    l_clob := replace( l_clob, ',', ', ' );
    word_wrap( l_clob );
end;
/


prints it out:

CREATE OR REPLACE FORCE VIEW "OPS$TKYTE"."V" ("ID",
"C00000000000000000000000000001",
"C00000000000000000000000000002",
"C00000000000000000000000000003",
"C00000000000000000000000000004",
"C00000000000000000000000000005",
"C00000000000000000000000000006",
"C00000000000000000000000000007",
"C00000000000000000000000000008",
"C00000000000000000000000000009",
"C00000000000000000000000000010",
"C00000000000000000000000000011",
"C00000000000000000000000000012",
"C00000000000000000000000000013",
"C00000000000000000000000000014",
"C00000000000000000000000000015",
"C00000000000000000000000000016",
"C00000000000000000000000000017",
"C00000000000000000000000000018",
"C00000000000000000000000000019",
"C00000000000000000000000000020",
"C00000000000000000000000000021",
"C00000000000000000000000000022",
"C00000000000000000000000000023",
"C00000000000000000000000000024",
"C00000000000000000000000000025",
"C00000000000000000000000000026",
"C00000000000000000000000000027",
"C00000000000000000000000000028",
"C00000000000000000000000000029",
"C00000000000000000000000000030",
"C00000000000000000000000000031",
"C00000000000000000000000000032",
"C00000000000000000000000000033",
"C00000000000000000000000000034",
"C00000000000000000000000000035",
"C00000000000000000000000000036",
"C00000000000000000000000000037",
"C00000000000000000000000000038",
"C00000000000000000000000000039",
"C00000000000000000000000000040",
"C00000000000000000000000000041",
"C00000000000000000000000000042",
"C00000000000000000000000000043",
"C00000000000000000000000000044",
"C00000000000000000000000000045",
"C00000000000000000000000000046",
"C00000000000000000000000000047",
"C00000000000000000000000000048",
"C00000000000000000000000000049",
"C00000000000000000000000000050",
"C00000000000000000000000000051",
"C00000000000000000000000000052",
"C00000000000000000000000000053",
"C00000000000000000000000000054",
"C00000000000000000000000000055",
"C00000000000000000000000000056",
"C00000000000000000000000000057",
"C00000000000000000000000000058",
"C00000000000000000000000000059",
"C00000000000000000000000000060",
"C00000000000000000000000000061",
"C00000000000000000000000000062",
"C00000000000000000000000000063",
"C00000000000000000000000000064",
"C00000000000000000000000000065",
"C00000000000000000000000000066",
"C00000000000000000000000000067",
"C00000000000000000000000000068",
"C00000000000000000000000000069",
"C00000000000000000000000000070",
"C00000000000000000000000000071",
"C00000000000000000000000000072",
"C00000000000000000000000000073",
"C00000000000000000000000000074") AS
select 1 id, 1"C00000000000000000000000000001",
1"C00000000000000000000000000002",
1"C00000000000000000000000000003",
1"C00000000000000000000000000004",
1"C00000000000000000000000000005",
1"C00000000000000000000000000006",
1"C00000000000000000000000000007",
1"C00000000000000000000000000008",
1"C00000000000000000000000000009",
1"C00000000000000000000000000010",
1"C00000000000000000000000000011",
1"C00000000000000000000000000012",
1"C00000000000000000000000000013",
1"C00000000000000000000000000014",
1"C00000000000000000000000000015",
1"C00000000000000000000000000016",
1"C00000000000000000000000000017",
1"C00000000000000000000000000018",
1"C00000000000000000000000000019",
1"C00000000000000000000000000020",
1"C00000000000000000000000000021",
1"C00000000000000000000000000022",
1"C00000000000000000000000000023",
1"C00000000000000000000000000024",
1"C00000000000000000000000000025",
1"C00000000000000000000000000026",
1"C00000000000000000000000000027",
1"C00000000000000000000000000028",
1"C00000000000000000000000000029",
1"C00000000000000000000000000030",
1"C00000000000000000000000000031",
1"C00000000000000000000000000032",
1"C00000000000000000000000000033",
1"C00000000000000000000000000034",
1"C00000000000000000000000000035",
1"C00000000000000000000000000036",
1"C00000000000000000000000000037",
1"C00000000000000000000000000038",
1"C00000000000000000000000000039",
1"C00000000000000000000000000040",
1"C00000000000000000000000000041",
1"C00000000000000000000000000042",
1"C00000000000000000000000000043",
1"C00000000000000000000000000044",
1"C00000000000000000000000000045",
1"C00000000000000000000000000046",
1"C00000000000000000000000000047",
1"C00000000000000000000000000048",
1"C00000000000000000000000000049",
1"C00000000000000000000000000050",
1"C00000000000000000000000000051",
1"C00000000000000000000000000052",
1"C00000000000000000000000000053",
1"C00000000000000000000000000054",
1"C00000000000000000000000000055",
1"C00000000000000000000000000056",
1"C00000000000000000000000000057",
1"C00000000000000000000000000058",
1"C00000000000000000000000000059",
1"C00000000000000000000000000060",
1"C00000000000000000000000000061",
1"C00000000000000000000000000062",
1"C00000000000000000000000000063",
1"C00000000000000000000000000064",
1"C00000000000000000000000000065",
1"C00000000000000000000000000066",
1"C00000000000000000000000000067",
1"C00000000000000000000000000068",
1"C00000000000000000000000000069",
1"C00000000000000000000000000070",
1"C00000000000000000000000000071",
1"C00000000000000000000000000072",
1"C00000000000000000000000000073",
1"C00000000000000000000000000074" from dual
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>

<b>add a set feedback off, set echo off, add a dbms_output.put_line( '/' ); add a set serveroutput on size 1000000 and there you go.</b> 

Bernard H, December 31, 2004 - 4:54 am UTC

Thank u very much indeed.

This works (delightly).

I prefer to do it the ORACLE way instead of the TOAD way, since I am an ORACLE DBA not a TOAD DBA.

Happy New Year 2005.

SP2-0027: Input is too long (> 2499 characters) - line ignored

A reader, January 27, 2005 - 1:05 pm UTC

Hi Tom,

I have table with CLOB datatype. I also have a script which has 10000 'insert' statement into this table. The script is for migration data(stories) from file system to Oracle 9i on HP-UX.

For some records(stories), I get SP2-0027 error. I used the technic you mentioned in this thread but to no avail.
Like:
set heading off
set feedback off
set linesize 1000
set trimspool on
set verify off
set embedded on
set long 200000
set pages 0

even I break the line with Enter to smaller lines, but still getting the same error.

I even used SQL>@foo.sql, but got the same error

Could you please tell me how I can run the script file in SQLPLUS? 

Thanks 

Tom Kyte
January 27, 2005 - 1:17 pm UTC

sorry, you are not going to exceed sqlplus's limits here -- sqlplus is a simple command line reporting tool.

this is not a script you can run in sqlplus, unless you change the script that is.

A reader, January 27, 2005 - 1:34 pm UTC

What tools can I use? How can I change the script that can be run in sqlplus? Should I use sql loader?

Thanks

Tom Kyte
January 27, 2005 - 1:51 pm UTC

you would make lines that are less than 2499 characters in length if you want to use sqlplus to run a script.


yes, you can use sqlldr, you can use an external table (to query the flat file directly even).

Or you could have had the program that generated the inserts, actually execute them instead of just writing them to a file.

A reader, January 27, 2005 - 2:02 pm UTC

Thank you so much!

- Arash


A reader, May 04, 2005 - 4:15 pm UTC

Tom,
  We are getting XML from other system and we have to insert into oracle table (xmltype). I am getting following errors. Could you please help?

SQL> desc TEST_XML_TABLE
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------------
 AA                                                 SYS.XMLTYPE
 BB                                                 VARCHAR2(100)
 C                                                  DATE



DECLARE

xx VARCHAR2(32000) := '<?xml version="1.0" encoding="UTF-16"?><?Siebel-Property-Set EscapeNames="false"?><SiebelMessage MessageId="1-GDBF" LineItemCount="" IntObjectName="MH Order Item Integration" MessageType="Integration Object" IntObjectFormat="Siebel Hierarchical"><ListOfMhOrderItemIntegration><OrderItem><AccountExecID>308</AccountExecID><ActionCode>Add</ActionCode><AltRepID>983</AltRepID><AttentionTo></AttentionTo><CancelCode></CancelCode><CancelDate></CancelDate><City>Greenwich</City><ContractDate></ContractDate><Country>USA</Country><CreatedBy>SADMIN</CreatedBy><CreatedDateTime>04/26/2005 10:02:15</CreatedDateTime><CreditCardExpDate></CreditCardExpDate><CreditCardNumber></CreditCardNumber><CreditCardType></CreditCardType><CustomerNumber>005003743100</CustomerNumber><EditionYear>2005</EditionYear><GrossAmount>430</GrossAmount><HoldBilling></HoldBilling><HoldShipping></HoldShipping><InceptionCode>N</InceptionCode><LastUpdatedDateTime>04/27/2005 14:05:24</LastUpdatedDateTime><LastYearTrackingNumber></LastYearTrackingNumber><LineAmount>344</LineAmount><LineItemRowID>1-P3KVV</LineItemRowID><LineItemStatus>New</LineItemStatus><ListingID>320063</ListingID><MigrationChildCode></MigrationChildCode><MigrationType></MigrationType><OrderCanvassDate>04/27/2005 00:00:00</OrderCanvassDate><OrderHeaderId>1-P3KVK</OrderHeaderId><OrderLastUpdatedBy>UBROWH3</OrderLastUpdatedBy><OrderNumber>796740</OrderNumber><OrderTaker>983</OrderTaker><OrgID>269640</OrgID><POBox></POBox><PrimaryRepID>308</PrimaryRepID><Producer>SIEBEL</Producer><ProductCode>01811</ProductCode><ProductContent>51</ProductContent><ProductName>LHP Basic</ProductName><PurchaseOrderNumber></PurchaseOrderNumber><Quantity>1</Quantity><ReferenceNumber></ReferenceNumber><ReinstatedDate></ReinstatedDate><ReturnMessage>NONE</ReturnMessage><Revision>0</Revision><RollupCount></RollupCount><ServiceAccountName>Zeichner Ellman &amp; Krause 
LLP</ServiceAccountName><ShippingCharges></ShippingCharges><SiebelAssetIntegrationId>1-P3KVV</SiebelAssetIntegrationId><SiebelID>2102</SiebelID><SiebelIntegrationId>1-P3KVV</SiebelIntegrationId><SiebelLineNumber>6</SiebelLineNumber><SiebelLineNumber2></SiebelLineNumber2><SignedBy></SignedBy><State>CT</State><StateCode>8</StateCode><StreetAddress1>35 Mason Street</StreetAddress1><Suite></Suite><TotalDiscountAmount>86</TotalDiscountAmount><TrackingNumber>1-P3LI8</TrackingNumber><UnitPrice>430</UnitPrice><ZipCode>06830</ZipCode><TaxableFlg></TaxableFlg><SolRept></SolRept><ListOfAttribute><Attribute><AttrProductCode>1812</AttrProductCode><TrackingNumber>1-P3LIH</TrackingNumber><Name>Number of Attorneys</Name><SiebelAttrName>200Number of Atty Web Listings|1812</SiebelAttrName><Value>2</Value></Attribute><Attribute><AttrProductCode>1812</AttrProductCode><TrackingNumber>1-P3LIK</TrackingNumber><Name>Contact First Name</Name><SiebelAttrName>250Contact First Name</SiebelAttrName><Value>first</Value></Attribute><Attribute><AttrProductCode>1812</AttrProductCode><TrackingNumber>1-P3LIF</TrackingNumber><Name>Contact Last Name</Name><SiebelAttrName>251Contact Last Name</SiebelAttrName><Value>last</Value></Attribute><Attribute><AttrProductCode>1812</AttrProductCode><TrackingNumber>1-P3LIE</TrackingNumber><Name>Contact eMail</Name><SiebelAttrName>252Contact eMail</SiebelAttrName><Value>fl@yahoo.com</Value></Attribute><Attribute><AttrProductCode>1812</AttrProductCode><TrackingNumber>1-P3LIC</TrackingNumber><Name>Contact Phone</Name><SiebelAttrName>253Contact Phone</SiebelAttrName><Value>3456789023</Value></Attribute><Attribute><AttrProductCode>1812</AttrProductCode><TrackingNumber>1-P3LID</TrackingNumber><Name>Contact Fax</Name><SiebelAttrName>253zContact Fax</SiebelAttrName><Value>4456789765</Value></Attribute><Attribute><AttrProductCode>1812</AttrProductCode><TrackingNumber>1-P3LIJ</TrackingNumber><Name>Template</Name><SiebelAttrName>254Template</SiebelAttrName><Value>Traditional 
2</Value></Attribute><Attribute><AttrProductCode>1812</AttrProductCode><TrackingNumber>1-P3LII</TrackingNumber><Name>Tag Line</Name><SiebelAttrName>255Tag Line</SiebelAttrName><Value>tag</Value></Attribute><Attribute><AttrProductCode>1812</AttrProductCode><TrackingNumber>1-P3LIG</TrackingNumber><Name>URL</Name><SiebelAttrName>256URL</SiebelAttrName><Value>url</Value></Attribute><Attribute><AttrProductCode>1812</AttrProductCode><TrackingNumber>1-P3LIL</TrackingNumber><Name>Future unique heads</Name><SiebelAttrName>Future unique heads</SiebelAttrName><Value>5</Value></Attribute></ListOfAttribute></OrderItem></ListOfMhOrderItemIntegration></SiebelMessage>';

BEGIN
insert into TEST_XML_TABLE values (xmltype.createxml(xx),'AAA' , sysdate);

END;
/
SP2-0027: Input is too long (> 2499 characters) - line ignored
  8  /
insert into TEST_XML_TABLE values (xmltype.createxml(xx),'AAA' , sysdate);
                                                     *
ERROR at line 5:
ORA-06550: line 5, column 54:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 5, column 1:
PL/SQL: SQL Statement ignored


Thanks 

Tom Kyte
May 04, 2005 - 6:08 pm UTC

the line is longer than the simple command line utility that is sqlplus will deal with. to write a program, you'll need to use a programming language.


consider using PLSQL to read a file or use a bfile.

How to exclude schema name in my ddl

Raza, June 01, 2006 - 11:17 am UTC

Hi Tom,

What I am trying to achieve is, extract the ddl for an object that is in another schema. e.g.

dsedapr@DSDM2PRD> exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE',false);

PL/SQL procedure successfully completed.

dsedapr@DSDM2PRD> exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR',true);

PL/SQL procedure successfully completed.

dsedapr@DSDM2PRD> exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES',false);

PL/SQL procedure successfully completed.

dsedapr@DSDM2PRD> set long 200000 pages 0 lines 131
dsedapr@DSDM2PRD> column txt format a121 word_wrapped
dsedapr@DSDM2PRD> select dbms_metadata.get_ddl( 'TABLE','SUM_EQUIFAX_FACT','EMPOWER') txt from dual;
CREATE TABLE "EMPOWER"."SUM_EQUIFAX_FACT"
( "MONTH_KEY" NUMBER(6,0) NOT NULL ENABLE,
"ACCOUNT_NO" NUMBER(16,0) NOT NULL ENABLE,
"COMPBAL_CDTCRDS_FI" NUMBER,
"COMPBAL_CDTCRDS_MONOLINE" NUMBER,
"COMPBAL_CDTCRDS_RETAIL" NUMBER,
"COMPBAL_CDTCRDS" NUMBER,
"COMPBAL_LOC_OTHREVOLV_RETAIL" NUMBER,
"COMPBAL_LOC_OTHREVOLV" NUMBER,
"COMPBAL_OTHLOANS" NUMBER,
"COMPAUTHCDT_CDTCRDS_FI" NUMBER,
"COMPAUTHCDT_CDTCRDS_MONOLINE" NUMBER,
"COMPAUTHCDT_CDTCRDS" NUMBER,
"COMPAUTHCDT_LOC_OTHREVOLV_RET" NUMBER,
"COMPAUTHCDT_LOC_OTHREVOLV" NUMBER,
"HAS_CDTCRDS_FI" NUMBER(1,0),
"ACCOUNT_KEY" NUMBER,
"HAS_CDTCRDS_MONOLINE" NUMBER(1,0),
"HAS_CDTCRDS_RETAIL" NUMBER(1,0),
"HAS_CDTCRDS" NUMBER(1,0),
"HAS_LOC_OTHREVOLV_RETAIL" NUMBER(1,0),
"HAS_LOC_OTHREVOLV" NUMBER(1,0),
"DATELASTOPEN_COMPCDTCARD" DATE,
"ACCOUNT_ID" VARCHAR2(20)
) ;

What I donÂ’t want is the SCHEMA name in the create statement, or different table name like "SUM_EQUIFAX_FACT_BKUP" instead of SUM_EQUIFAX_FACT.

Your help will be appreciated.

Thanks


Tom Kyte
June 01, 2006 - 11:32 am UTC

one way would be to use "replace"

Getting Script of all table related data

Rob, November 01, 2006 - 12:18 am UTC

1. My requirement is to get the all table, constraints, indexes, trigger etc script for a particular table. I mean all these triggers, indexes, constraints are on this table. How can I get this info?

2. I also need a script to recreate the snapshots (of course with indexes, constraints etc)

Thanks in advance.



Tom Kyte
November 01, 2006 - 8:43 am UTC

1) by reading about dbms_metadata

2) see #1


but you know, this actually means you are working someplace that has ZERO CONFIGURATION MANAGEMENT. Another option is "run away as fast as you can".

procedure word_wrap

neto, March 26, 2007 - 3:03 pm UTC

Hi,
I have a problem with word_wrap procedure.
The procedure is not working when we have long CLOB.
Does anyone have a version for long CLOB ?
Thanks

Create statements in one sql!

SP, July 18, 2007 - 6:02 am UTC

This is a very nice little trick I learned from Laurent Schneider, here:
http://laurentschneider.com/wordpress/2007/04/how-do-i-store-the-counts-of-all-tables.html

I have modified this to generate create scripts of objects, instead of counts.

SELECT EXTRACTVALUE(XMLTYPE(
DBMS_XMLGEN.GETXML( 'SELECT DBMS_METADATA.GET_DDL( ''TABLE'', '''||TABLE_NAME||''' ) SCR FROM DUAL'))
, '/ROWSET/ROW/SCR') OBJECT_SCRIPT
FROM USER_TABLES

Can be easily extended to get dependent object scripts...

I find the idea of SQL executing another SQL (without any execute immediate or PLSQL) absolutely awesome.

Of course, you wouldn't need to run this particular sql on your dbs because as Tom suggest it means Zero configuration management at the site and its best to run away as fast as you can.
But it just shows how powerful the construct is and can be put to other uses.

Thanks, SP..

Get_ddl

Sachin, January 25, 2008 - 1:14 am UTC

Hi Tom

i am creating recreate scripts for all views exist in schema by using get_ddl, i am facing one critical issue.
i know you have solution of it. the problem is when create ddl of views by using get_ddl , i get this defintion of views like as given mentioned. Each column of views is creating by "" the problem for "CA_STATUS
_DT" column . it is not behave like previous column ca_status_dt. both column are behave differnlty.


i am setting these env. variables.
set echo off
set feedback off
set pagesize 0
SET LONG 500000
set linesize 200
set heading off

thanks in advance.



CREATE OR REPLACE FORCE VIEW "FMS_SCHEMA5"."CASES_VIEW" ("CA_NUMBER", "CUST_NAME_CUS", "CUST_STATUS", "CA_CREATE_DT", "CA_PROF_NO", "CC_GROUP", "CC_NAME", "CT_NAME", "CD_NAME", "CS_NAME", "CA_STATUS
_DT", "CA_SECURITY_LEVEL", "CA_PRIORITY") AS
select
ca.ca_number,
cu.cust_name_cus as cust_name_cus,
--cu.phone_no_ind as cust_type,
null as cust_status,
--cu.ext_stat_sbb is replaced by gen_ext_stat.
-- commented by manohar due to changed subscriber map on 24/04/06 TBD
ca.ca_create_dt,
ca.ca_prof_no,
cc.cc_group, --cc_number,
cc.cc_name,
ct.ct_name, --ct_number,
ca.cd_name,
ca.cs_name,
ca.ca_status_dt,
ca.ca_security_level,
ca.ca_priority

from
ct_cases ca,
ct_category cc,
ct_types ct,
ct_subscribers cu,
adm_users us1,
adm_users us2,
(
select
al_2.al_prof_no,
nvl(rl.rl_name, al_2.al_desc) as rl_name,
al_2.rl_number
from
(select al_prof_no, min(al_number) as al_number from alerts group by al_prof_no) al_1,
alerts al_2,
rp_rules rl
where
al_1.al_number = al_2.al_number and
al_2.rl_number = rl.rl_number(+)
) al,
(select
max(al_1.al_last_fire_dt) al_last_fire_dt,
al_prof_no
from alerts al_1
group by al_prof_no
) a2
where
al.al_prof_no(+) = ca.ca_prof_no and
ca.ca_prof_no = a2.al_prof_no(+) and
ca.cc_number = cc.cc_number(+) and
ca.ct_number = ct.ct_number(+) and
ca.ca_number = cu.ca_number(+) and
ca.us_acct_mgr = us1.us_number(+) and
ca.us_agent = us2.us_number(+)
Tom Kyte
January 25, 2008 - 8:49 am UTC

ops$tkyte%ORA10GR2> create or replace view v
  2  as
  3  select * from all_objects;

View created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'VIEW', 'V' ) txt from dual;

TXT
-------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "OPS$TKYTE"."V" ("OWNER", "OBJECT_NAME", "SUBOBJ
ECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_T
IME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY") AS
  select "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","O
BJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERAT
ED","SECONDARY" from all_objects


<b>
ops$tkyte%ORA10GR2> column txt format a80 WORD_WRAPPED</b>
ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'VIEW', 'V' ) txt from dual;

TXT
-------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "OPS$TKYTE"."V" ("OWNER", "OBJECT_NAME",
"SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED",
"LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY")
AS
select
"OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJE
CT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED"
,"SECONDARY" from all_objects


Solution of Get_ddl

Sachin, January 28, 2008 - 2:20 am UTC

Hi Tom,
i added this line(column columns format a20 word_wrapped) in our scripts but didnt get right result. now i have changed my script's logic, to create views DDL.

before i was using like this...

SELECT '
prompt creating script for '||object_type||' '||object_name||'
set term off
spool C:\upgrade\scripts\backup\fms_schema\'||object_type||'s\'||lower(object_name)||'.sql;'||'
select dbms_metadata.get_ddl('''||object_type||''','''||object_name||''') from dual;'||'
'||'SPOOL OFF
set term on'
FROM USER_OBJECTS
WHERE OBJECT_TYPE IN ('VIEW')
ORDER BY OBJECT_TYPE, OBJECT_NAME;

Now, i m using

SELECT '
prompt creating script for '||object_type||' '||object_name||'
set term off
spool C:\upgrade\scripts\backup\fms_schema\'||object_type||'s\'||lower(object_name)||'.sql;'||'
select ''create or replace '||object_name||' as '' '||CHR(44)||' text from user_views where view_name ='''||object_name||''' ;'||'
'||'SPOOL OFF
set term on'
FROM USER_OBJECTS
WHERE OBJECT_TYPE IN ('VIEW')
ORDER BY OBJECT_TYPE, OBJECT_NAME;

its woring fine .

Thanks!
Tom Kyte
January 29, 2008 - 2:26 am UTC

a20???? that is a bit extreme, don't you think - a80 would have been reasonable - a20 - that isn't even long enough to hold an identifier.

Sachin, January 29, 2008 - 11:14 pm UTC

whoops!!!, Actually i had used a80.
Tom Kyte
January 30, 2008 - 9:47 am UTC

then show us what was "not working", if you did what I described, it would have wrapped correctly.

dbms_metadata and formatting

Bob, December 19, 2008 - 7:31 pm UTC

Hi Tom,

I have a question for you regarding using dbms_metadata.get_ddl and formatting. We have an Oracle DW where we are doing the standard: make index unusable (non-unique indexes), load table, rebuild index and analyze. However, on this particular table we have a primary key with associated unique index. My idea was to store the DDL for the the constraint and unique index in a global temp table, drop primary key and unique index, load the table and recreate primary key constraint and unique index from teh DDL in the GTT.

I have a table p(x varchar2(10), y clob)

Then:

insert into p
select index_name,
(select dbms_metadata.get_ddl('CONSTRAINT', 'x_pk') from dual)
from user_indexes
where table_name = 'H'
and uniqueness = 'UNIQUE'

This code is in a procedure within a package. And the call to the package is from PERL.

My question is, is there a way for me to set longchunksize
or make a SQL*Plus command from PERL?

Thanks!

Tom Kyte
December 29, 2008 - 2:14 pm UTC

you do not need long chunksize or anything here - you are not using longs at all, you are using lobs and they take care of themselves.

dbms_metadata.get_ddl and formatting

Bob, December 19, 2008 - 7:56 pm UTC

Sorry - I should have mentioned that code inserted into my CLOB column is broken up:

e.g. CREATE UNIQUE INDEX x O
N x(y) TABLESPACE FIG_
SMALL

etc. Hence why I need to increase LONGCHUNKSIZE. But I can't set this SQL*Plus setting in PERL.

Any thoughts would be much appreciated. Cheers
Tom Kyte
December 29, 2008 - 2:17 pm UTC

it is not doing that.

maybe sqlplus is when you display it in that very primitive command line tool - but the data IS NOT stored that way.

what are you fetching this with?

dbms_metadata

Bob, December 21, 2008 - 6:26 pm UTC

Hi Tom,

I found a way of getting round this problem - I used the following setting:

dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', false );

Seemed to do the trick.

I was trying to get the DDL for a create constraint statement and I was getting:

ALTER TABLE "Y"."X" ADD CONSTRAINT "A_PK" PRIMARY KEY ("CCDB_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SMALL_TBS"
ALTER INDEX "Y"."A_IDX01" UNUSABLE; ENABLE;

I saved the above in a CLOB column in a table. Dropped primary constraint. Loaded table. Then selected above statement into a variable and execute immediated it.

The second alter statement was causing the add constraint to fail. The set transform param got rid of the second alter statement.

Cheers!

what about sql worksheet ?

Ozgur TUGRUL, February 10, 2009 - 9:49 am UTC

in sql plus .. great answer Tom .. thanks ;)

what about sql worksheet ?

i've had some experience in sql worksheet and it gave me an output below ..


SQLWKS> desc test
Column Name Null? Type
------------------------------ -------- ----
A NUMBER(3)
SQLWKS> set long 2000;
Longwidth 2000
SQLWKS> SELECT dbms_metadata.get_ddl('TABLE', 'TEST') FROM dual;
DBMS_METADATA.GET_DDL('TABLE','TEST')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0A2020435245415445205441424C4520224F49443744415441222E225445535422200A202020280922412220202020202020202020202020202020202020202020202020202020202020202020202020202020202020
1 row selected.


any idea ?

if this is a new question, i'm so sorry, just ignore it ..
Tom Kyte
February 11, 2009 - 9:20 am UTC

well, for whatever reason, it thinks it is dealing with a raw:

ops$tkyte%ORA11GR1> select utl_raw.cast_to_varchar2( hextoraw(
  2  '0A2020435245415445205441424C4520224F49443744415441222E225445535422200A202020280922412220202020202020202020202020202020202020202020202020202020202020202020202020202020202020'
  3  ) ) from dual;

UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('0A2020435245415445205441424C4520224F49443744
-------------------------------------------------------------------------------

  CREATE TABLE "OID7DATA"."TEST"
   (    "A"




if sqlworksheet is part of sqldeveloper, suggest you post on the sqldeveloper forum on otn.oracle.com

thank you

Ozgur TUGRUL, February 23, 2009 - 6:26 am UTC

thank you Tom ..

How about a function

Abhisek, August 26, 2010 - 9:31 am UTC

CREATE OR REPLACE FUNCTION
get_file_with_number_details (p_object_Type VARCHAR2, p_object_Name VARCHAR2)
RETURN CLOB
AS
v_return CLOB;
v_string CLOB;
v_pos NUMBER (17) := 1;
v_counter NUMBER := 0;

-- Commonly Used: TABLE, VIEW, PROCEDURE, FUNCTION, SEQUENCE, TABLESPACE, MATERIALIZED_VIEW, PACKAGE, PACKAGE_SPEC, PACKAGE_BODY, DB_LINK, CONSTRAINT

BEGIN
SELECT DBMS_METADATA.get_ddl (p_object_Type, p_object_Name)
INTO v_string
FROM DUAL;

WHILE v_pos != 0
LOOP
v_pos := INSTR (v_string, CHR (10));

IF SUBSTR (v_string, 1, v_pos - 1) IS NOT NULL
THEN
v_counter := v_counter + 1;
v_return :=
v_return
|| CHR (10)
|| TO_CHAR (v_counter)
|| ' '
|| SUBSTR (v_string, 1, v_pos - 1);
END IF;

v_string := SUBSTR (v_string, v_pos + 1);
END LOOP;

RETURN v_return;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
/

I use it and it works fine for me.. May be others can use it as well.
Tom Kyte
August 26, 2010 - 1:42 pm UTC

EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
END;
/


I hate your code.

http://asktom.oracle.com/pls/asktom/asktom.search?p_string=%22i+hate+your+code%22

why do you do that. That is a worst practice, it is worse than horrible - it is beyond excuse. sorry for being so blunt, I just get so tired of seeing it over and over again.

The rest of the code might be perfect (don't know, didn't look at it). It doesn't matter, I would not permit it based on the when others then null.

Before you comment for the mistake I did..

Abhisek, August 26, 2010 - 9:37 am UTC

Never use OTHERS exception with NULL..

But in this case I ddint know how to handle ORA-31603 exception. may be Tom can help us :-)
Tom Kyte
August 26, 2010 - 1:45 pm UTC

why did you then?

the CALLER should deal with the error, the CALLER deserves (truly, they do) to know that their requested object does not exist. As it stands - they have NO CLUE.

This error should not be caught - you cannot do anything about it, the caller needs to receive this - THEY might be able to deal with it, but you cannot. You catch ONLY exceptions that you can deal with. if you cannot deal with it - log it and re-raise it or do not catch it all.
declare
   foo exception;
   pragma exception_init( -31603, foo );
begin
   ...

exception
   when foo then 
      log_it_maybe;
      RAISE;
end;
/



how to handle ORA-31603 exception.

Oleksandr Alesinskyy, August 27, 2010 - 3:13 am UTC

I ddint know how to handle ORA-31603 exception.
While Tom is completely correct that you need not to handle this exception in your case, the one of the following techniques may be used if you wish to catch it anyway (e.g. for logging purposes):
declare
   DBMS_METADATA_EXCEPTION exception;
   pragma EXCEPTION_INIT(DBMS_METADATA_EXCEPTION, -31603);
begin    -- Your code that may cause an ORA-31603
exception
   when DBMS_METADATA_EXCEPTION then
      -- Perform logging or whatever you wish
      raise;
end;
/

or
when
    -- Your code that may cause an ORA-31603
exception
   when OTHERS then
      if SQLCODE=-31603 then
      -- Perform logging or whatever you wish
      end if;
      raise;
end;
/


I would prefer the first approach unless there are several exception codes that should be handled identically.

BTW, all it is explained in "Oracle® Database PL/SQL User's Guide and Reference" in details.
Tom Kyte
September 07, 2010 - 7:47 am UTC


Code modified

Abhisek, August 27, 2010 - 3:30 am UTC

Now, I rewrote the code yesterday, after I posted my earlier code, to print the line numbers with the code lines. Seems it is more presentable than the previous one.

CREATE OR REPLACE FUNCTION get_file_with_number_details (p_object_Type VARCHAR2, p_object_Name VARCHAR2)
   RETURN CLOB
AS
   v_return    CLOB;
   v_string    CLOB;
   lv_Str_List_Quote    CLOB;
   l_tablen  BINARY_INTEGER;
   l_tab     DBMS_UTILITY.uncl_array;
   v_pos       NUMBER (17) := 1;
   v_counter   NUMBER := 0;
   
-- Commonly Used: TABLE, VIEW, PROCEDURE, FUNCTION, SEQUENCE, TABLESPACE, MATERIALIZED_VIEW, PACKAGE, PACKAGE_SPEC, PACKAGE_BODY, DB_LINK, CONSTRAINT
   
BEGIN
   SELECT   DBMS_METADATA.get_ddl (p_object_Type, p_object_Name)
     INTO   v_string
     FROM   DUAL;

   WHILE v_pos != 0
   LOOP
      v_pos := INSTR (v_string, CHR (10));

      IF SUBSTR (v_string, 1, v_pos - 1) IS NOT NULL
      THEN
         v_counter := v_counter + 1;
         
          
            IF INSTR(SUBSTR (v_string, 1, v_pos - 1),', "') > 0
            THEN
                IF INSTR(SUBSTR (v_string, 1, v_pos - 1),'CREATE') > 0
                    THEN    
                v_return := v_return
                            || CHR (10)
                            || TO_CHAR (v_counter)
                            || '   '
                            || SUBSTR(REPLACE(SUBSTR (v_string, 1, v_pos - 1),'"',''),1,INSTR(REPLACE(SUBSTR (v_string, 1, v_pos - 1),'"',''),'('));
                v_counter := v_counter + 1;
                
                lv_Str_List_Quote := '"' || REPLACE( SUBSTR(REPLACE(SUBSTR (v_string, 1, v_pos - 1),'"',''),INSTR(REPLACE(SUBSTR (v_string, 1, v_pos - 1),'"',''),'(') + 1), ',', '","' ) || '"';
                DBMS_UTILITY.comma_to_table (
                 list   => lv_Str_List_Quote,
                 tablen => l_tablen,
                 tab    => l_tab);
                  FOR i IN 1 .. l_tablen LOOP
                    v_counter := v_counter + 1;
                    v_return :=
                               v_return
                            || CHR (10)
                            || TO_CHAR (v_counter)
                            || '   '
                            || TRIM(REPLACE( l_tab(i), '"', '' ) || ',');                
                  END LOOP;
                  v_return := SUBSTR(v_return, 1,length(v_return) -1); 
                  
                  ELSE
                        v_return :=
                       v_return
                    || CHR (10)
                    || TO_CHAR (v_counter)
                    || '   '
                    || SUBSTR (v_string, 1, v_pos - 1);
                     v_counter := v_counter + 1;
                END IF;
            ELSE
               

            v_return :=
                   v_return
                || CHR (10)
                || TO_CHAR (v_counter)
                || '   '
                || SUBSTR (v_string, 1, v_pos - 1);
            END IF;
          
         
      END IF;
      
              
      v_string := SUBSTR (v_string, v_pos + 1);
   END LOOP;

   RETURN v_return || CHR (10)  || TO_CHAR (v_counter) || '   ' || '/';
   
EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -31603 THEN
          raise_application_error ('-20001', 'Error: ' ||SQLCode || ' Message: Please verify Object TYPE and NAME');
      END IF;
END;
/


To use this function:

SELECT GET_FILE_WITH_NUMBER_DETAILS('TABLE','TABLE_NAME') FROM DUAL;

I wasn't sure other users would like to have raise_application_error, so I left it as NULL for them to rectify, which you suggested :-)

And yes, Thanks Tom for being kind on me with those words. The moment I posted I started reworking as I knew what you would see first. So no OTHERS with NULL here..

Lets see if the code helps one single person :-)



@Abhisek

Oleksandr Alesinskyy, August 30, 2010 - 4:36 am UTC

Your new code is not much better than previous one due to the following problem
EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -31603 THEN
          raise_application_error ('-20001', 'Error: ' ||SQLCode || ' Message: Please verify Object 
TYPE and NAME');
      END IF;

This snippet silently swallows any error save -31603. This i a very bad practice.
You have to add RAISE right after END IF:
EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -31603 THEN
          raise_application_error ('-20001', 'Error: ' ||SQLCode || ' Message: Please verify Object 
TYPE and NAME');
      END IF;
      RAISE;

Tom Kyte
September 09, 2010 - 9:09 am UTC

thank you :)

Now I didn't have to write it...

@Oleksandr

Abhisek, August 30, 2010 - 6:27 am UTC

Thanks Oleksandr for your useful comments.

But may be i am not very sure of this concept. Because when ever I try to call this function (either through selecting the function or assigning the result of function in variable through PL/SQL), it gives me the error message I want to see.

<code>
ORA-20001: Error: -31603 Message: Please verify Object TYPE and NAME
ORA-06512: at "GET_FILE_WITH_NUMBER_DETAILS", line 87


May be I misunderstood, whats written in Oracle documentation:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm#BABECFCD

Please correct me if I am wrong. Thanks for kind suggestion.</code>
Tom Kyte
September 09, 2010 - 9:14 am UTC


@Oleksandr and Abhisek

Michel Cador, September 07, 2010 - 9:12 am UTC


The correct way to trap a specific exception is the following one.
1/ declare an exception variable and initialize it
2/ trap this exception and this one only.

In your case, in the declaration section:
   excep exception;
   pragma exception_init(excep, -31603);

In the exception section:
EXCEPTION
  WHEN excep THEN
    raise_application_error (
      '-20001', 
      'Error: ' ||SQLCode || ' Message: Please verify Object TYPE and NAME'
      );
END;

Regards
Michel

@Michel

Oleksandr Alesinskyy, September 08, 2010 - 10:16 am UTC

It is one of correct ways. It has its advantages and its drawbacks, see my comment from 27.08.2010.

@Abhisek

Oleksandr Alesinskyy, September 08, 2010 - 10:19 am UTC

Sorry, but I do not understand what you misunderstood.
Yes, it produces ORA-20100, and it is intended behavior of raise_application_error.

But may you explain what do you want instead?

@Oleksandr

Michel Cadot, September 09, 2010 - 2:32 am UTC



If there are several errors that are handle in the same way then list them in WHEN clause:
when except1 or except2 or except3 then ...

This is not more difficult than
if sqlcode in (...,...,...) then
and WHEN OTHERS will hide the original message and line number for the errors you do not handle, too bad.

Regards
Michel

@Michel

Oleksandr Alesinskyy, September 13, 2010 - 6:37 am UTC

Yes, you are right - completely forgot that PL/SQL permits OR in the WHEN clause.

Anyhow it is not "the only proper way" - but yes, seems to be the best.

dbms_metadata

Reader, December 06, 2010 - 3:58 pm UTC

When I do like this,

> cat gen_cretable.sql
set long 200000 pages 0 lines 131
column txt format a121 word_wrapped

spool /temp/cre_table.sql

select dbms_metadata.get_ddl('TABLE','PART_TABLE','SCOTT') from dual;
spool off;
exit;

partition definition does not appear on the same line as shown below

(PARTITION "Y2007_WEEK_1" VALUES LESS THAN (TO_DATE(' 2007-01-08 00:00:00', 'S
YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

How do i have it on the same line? Thanks.
Tom Kyte
December 07, 2010 - 10:07 am UTC

set linesize <some big number>


longchunksize

Laurent Schneider, December 15, 2010 - 10:05 am UTC

@reader

set longchunksize 200000

should do the trick...

dbbms_metadata.get_ddl not fixed in 11gr2

Nazir, January 18, 2011 - 12:58 am UTC

Hi Tom,
Thanks. The solution you suggested for getting output on line, and add semi colon worked for me. I should metnion though that according to metalink note, this bug should have ebeen fixed in 10gr2 and above, but it's still there in 11gr2. Here is what I did to make it worke, baed on your earlier suggestion( that apparently didn't work for others in sqlplus!)
SET ECHO OFF
SET PAGESIZE 0
SET LINES 3000
SET LONG 200000
SET FEEDBACK OFF
SET HEADING OFF
SET SERVEROUTPUT ON SIZE 1000000



COLUMN txt FORMAT a3000 WORD_WRAPPED

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',false);
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
SPOOL c:\temp\create_fct_index.sql

SELECT DBMS_METADATA.GET_DDL ('INDEX', u.index_name) txt
FROM user_indexes u
WHERE index_type = 'FUNCTION-BASED NORMAL';

SPOOL OFF;

SET ECHO ON
SET FEEDBACK ON
SET HEADING ON
SET SERVEROUTPUT OFF

Not fixed in 11.1.0.7.0 (per the above)

William, March 08, 2012 - 5:33 am UTC

Since I came to this thread whilst tearing my hair out with formatting problems with dbms_metadata.get_ddl, I thought I'd just add this for anyone who comes after:

The above comments about setting linesize and column format are the correct solution to line truncation/wrapping with get_ddl, but the metalink bug mentioned still exists (in 11.1.0.7.0 anyway) - yesterday pulling DDL out of the db gave me, essentially, random truncation of lines at arbitrary points; today on the same database (after a restore from backup - happens automatically overnight every night since I'm on a dev database) the DDL came out correctly formatted.

If you are following the instructions above and it's not working, I am not sure what to suggest - clearly a restoring from a backup isn't something that is available to everyone, however equally I can say that playing with settings probably isn't going to solve the problem (or at least it didn't for me) - you may end up having to hand-edit the code afterwards.

If it happens to me again today, I'll see if bouncing the database or similar helps, and if so will post again.

Regards,

William

Virtual column

Developer, March 16, 2012 - 9:21 am UTC

Is there any way to suppress getting definition of virtual columns in a table when using dbms_metadata.get_ddl? I am on 11.2.0.3

i.e. I don't want get_ddl to return virtual columns.
Tom Kyte
March 16, 2012 - 9:23 am UTC

I am not aware of a method to make get ddl skip some columns of a table, no.

Nora, October 02, 2013 - 12:09 pm UTC

Hi Tom,

I am trying to spool our db objects so we can add them to SVN (11.2.0.2). So I'd prefer it if the result were formatted.

From what I see, setting column txt format a120 WORD_WRAPPED is causing much, if not all, of the existing formatting to be lost.
Setting DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'PRETTY', TRUE); does not seem to have much of an influence.
Also, I see whitespace in the spooled file up to the defined line/column length.

I was able to get over the WORD_WRAPED impediment using a longer line (200 is fine for our code) and dropping the column formatting.

I am at a bit of a loss for the trailing whitespace. It does cause the files to be larger, and is quite anoying. Any suggestions on getting rid of it?

Thanks,
Nora

P.S. My sqlplus script looks something like this:
set long 500000
set head off
set echo off
set pagesize 0
set verify off
set feedback off
--column txt format a80
set linesize 200


BEGIN
   DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',FALSE);
   DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'SIZE_BYTE_KEYWORD',TRUE);
   DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'PRETTY', TRUE);
   DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
   DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
end;
/

spool dw_utils.sql
SELECT DBMS_METADATA.GET_DDL('PACKAGE', 'PACKAGE_NAME', 'OWNER') txt FROM dba_objects
          WHERE object_type = 'PACKAGE' and OWNER = 'OWNER' and object_name = 'PACKAGE_NAME';
spool off

Nora, October 02, 2013 - 1:42 pm UTC

Whoops, need the column to be 200 too.

You are a lifesaver!

Karen, October 25, 2017 - 1:16 pm UTC

Thank you. This still works as of Oct 24, 2017 and sqlplus is on Oracle 11.2.0.4!
Connor McDonald
October 26, 2017 - 12:42 am UTC

glad we could help

This could be one of the solutions

Ulhas K, January 26, 2021 - 11:42 am UTC

Simple and easy using regexp_replace

set heading off feedback off trimspool on escape off
set long 1000 linesize 1000 pagesize 0
col SEQDDL format A300
spool tts_create_seq.sql
prompt /* ========================= */
prompt /* Drop and create sequences */
prompt /* ========================= */
select regexp_replace(
dbms_metadata.get_ddl('SEQUENCE',sequence_name,sequence_owner),
'^.*(CREATE SEQUENCE.*CYCLE).*$')||';' SEQDDL
from dba_sequences
where sequence_owner not in
(select name
from system.logstdby$skip_support
where action=0);
spool off

More to Explore

DBMS_METADATA

More on PL/SQL routine DBMS_METADATA here