OraFAQ Forum: Server Administration » DBMS_METADATA.GET

Home » RDBMS Server » Server Administration » DBMS_METADATA.GET_DDL formatting problem

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

DBMS_METADATA.GET_DDL formatting problem [message #221052]

Fri, 23 February 2007 08:44

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

I have been using DBMS_METADATA.GET_DDL for a long time to go against tables and indexes. Wouldn't you know, in all this time I have never tried to use it to get source code.
Well, not matter what I do, I cannot get the format correct. Wrapping, line feeds, etc. are making it too much of a maintenance nightmare to make it worthwhile to use this package. I have to edit every single line.
I have used set long, linesize, wrap but to no avail.
What am I doing wrong?

Report message to a moderator

Re: DBMS_METADATA.GET_DDL formatting problem [message #221057 is a reply to message #221052]

Fri, 23 February 2007 09:06

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Do you have a particular example to work with?
I use dbms_metadata a lot. Usually having a column alias on output and formatting the column works pretty good for me.
Just an example here

scott@9i > get trigger_ddl
  1  set long 500000
  2  set linesize 1000
  3  SET HEAD off
  4  set trimspool on
  5  set verify off
  6  column XXXX format a300 -->increase it to your need.
  7  exec dbms_metadata.SET_TRANSFORM_PARAM (dbms_metadata.SESSION_TRANSFORM, 'PRETTY', true);
  8  SELECT dbms_metadata.GET_DDL('TRIGGER',D.TRIGGER_NAME)||';' as xxxx FROM user_triggers D;
  9* set head on;

In a related thread,while recomending the same,the OP came up another option using WRAP from asktom. Please see whether is works.
http://www.orafaq.com/forum/m/178241/42800/?srch=dbms_metadata+format#msg_217078

Report message to a moderator

Re: DBMS_METADATA.GET_DDL formatting problem [message #221068 is a reply to message #221057]

Fri, 23 February 2007 10:29

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Thanks once again Mahesh.
I had all the set commands pretty much like yours. I just didn't have the column alias. Once I put that in, voila, it worked like a charm.

Report message to a moderator

Previous Topic:	dblink problem
Next Topic:	CPU Utilization - Find long running SQL query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 20:15:44 CDT 2024