Home » RDBMS Server » Server Administration » DBMS_METADATA.GET_DDL formatting problem
DBMS_METADATA.GET_DDL formatting problem [message #221052] Fri, 23 February 2007 08:44 Go to next message
joy_division
Messages: 4640
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?
Re: DBMS_METADATA.GET_DDL formatting problem [message #221057 is a reply to message #221052] Fri, 23 February 2007 09:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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
Re: DBMS_METADATA.GET_DDL formatting problem [message #221068 is a reply to message #221057] Fri, 23 February 2007 10:29 Go to previous message
joy_division
Messages: 4640
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.
Previous Topic: dblink problem
Next Topic: CPU Utilization - Find long running SQL query
Goto Forum:
  


Current Time: Sat Dec 03 09:50:29 CST 2016

Total time taken to generate the page: 0.04557 seconds