RE: dbms_metadata.get_ddl

From: Patterson, Joel <jpatterson_at_entint.com>
Date: Mon, 30 Sep 2013 12:31:28 -0400
Message-ID: <C1117B1AA0340645894671E09A7891F71512F720BA_at_EIHQEXVM2.ei.local>

You could try this one. The commented out one kind of hold reminders of some of the other parameters. Let me know if it works. Haven't found the perfect solutions yet, seems the out put is also related to the actual query you use.

SET LONG 1000000
SET LONGCHUNK 1000000
SET LINESIZE 200 /*
Examples:
begin

        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,'SEGMENT_ATTRIBUTES',TRUE);
        dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE', FALSE);
        dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',TRUE);
        dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SPECIFICATION',TRUE);
        dbms_metadata.set_transform_param(dbms_metadata.session_transform,'BODY',TRUE);
        dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS',TRUE);
        dbms_metadata.set_transform_param(dbms_metadata.session_transform,'REF_CONSTRAINTS',TRUE);
        dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS_AS_ALTER',TRUE);

end;
/
*/

SET LONG 1000000
SET LONGCHUNK 1000000
SET LINESIZE 200 begin

  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,'CONSTRAINTS_AS_ALTER',TRUE);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE', FALSE);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',TRUE);

end;
/

Joel Patterson
Database Administrator
904 928-2790

--

Joel Patterson
Sr. Database Administrator | Enterprise Integration Phone: 904-928-2790 | Fax: 904-733-4916
http://www.entint.com/

http://www.entint.com/

http://www.facebook.com/pages/Enterprise-Integration/212351215444231 http://twitter.com/#!/entint http://www.linkedin.com/company/18276?trk=tyah http://www.youtube.com/user/ValueofIT

This message (and any associated files) is intended only for the use of the addressee and may contain information that is confidential, subject to copyright or constitutes a trade secret. If you are not the intended recipient, you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message, is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and deleting it from your computer. Messages sent to and from us may be monitored. Any views or opinions presented are solely those of the author and do not necessarily represent those of the company. [v.1.1]

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ray Stell Sent: Monday, September 30, 2013 10:15 AM To: oracle-l_at_freelists.org
Subject: dbms_metadata.get_ddl

Whenever I try to use the dbms_metadata.get_ddl the output is not valid. I think the issue must be terminal related. The docs say use these:

SQL> SET PAGESIZE 0
SQL> SET LONG 1000000
SQL> SELECT get_table_md FROM dual;

and then shows perfectly formated output. I wonder what editor they used to repair the damage? When I use it there are linebreaks in the middle of strings that produce errors.

This thread https://forums.oracle.com/thread/703189 reports using:

set pagesize 0
set long 90000
set lines 131
column txt format a121 word_wrapped
set longchunksize to 250

This seemed to work a little better, but I feel like it won't on the next object. Tom has this rambling thread: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:30802454515375

Seems like rather a fishing expedition. Is there some magic that just works?

TIA
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Mon Sep 30 2013 - 18:31:28 CEST