Home » SQL & PL/SQL » SQL & PL/SQL » dbms_metadata.get_ddl introduces a newline in the ddl
dbms_metadata.get_ddl introduces a newline in the ddl [message #178234] Tue, 20 June 2006 04:00 Go to next message
arunkathirvelu
Messages: 3
Registered: June 2006
Junior Member
Hi,

I tried to fetch the ddl of the database objects like tables, triggers and so on, using the dbms_metadata.get_ddl function. It worked fine for all the objecs except for a few triggers. The problem is that it has broken few of the comment lines (arbitrarily) in the trigger's ddl. I had to manually edit this before playing it to recreate the trigger.

The same problem occurs wiht the fetch_clob method too. Did anyone experienced this before? Any help is greatly appreciated.

Thanks,
Arun
Re: dbms_metadata.get_ddl introduces a newline in the ddl [message #178241 is a reply to message #178234] Tue, 20 June 2006 04:21 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I haven't faced any.
Did you 'prettify' the query?
Increase the formatting in line 6, if your code is more than 300 characters per line.
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
  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;
Re: dbms_metadata.get_ddl introduces a newline in the ddl [message #180350 is a reply to message #178241] Mon, 03 July 2006 03:17 Go to previous messageGo to next message
arunkathirvelu
Messages: 3
Registered: June 2006
Junior Member
Hi Mahesh,

Thanks for the reply. I did have the PRETTY flag set. I guess that's by default. Anyways I did force that setting. Even then the results were same.

Finally I took the redundant way, collecting the information from the dba_source view for whatever objects that view is supporting!! Those values are clean and there was no break!!

I'm wondering.. for the same trigger, when the other tools like TOAD, PLEdit(Benthic) are collecting the source properly, just the dbms_metadata package conks!! Anyways thanks a lot.

Regards,
Arun

Re: dbms_metadata.get_ddl introduces a newline in the ddl [message #180361 is a reply to message #180350] Mon, 03 July 2006 04:36 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I cannot reproduce your case.
Please post the code, if you can afford. I will try to reproduce.
Re: dbms_metadata.get_ddl introduces a newline in the ddl [message #216597 is a reply to message #180361] Mon, 29 January 2007 11:19 Go to previous messageGo to next message
agostino_neto
Messages: 180
Registered: July 2005
Senior Member
Hi Mahesh,

I am using DBMS_METADATA but the output is like this (after table description):
---
PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255 NOC
OMPRESS LOGGING
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2
147483645
PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
---
I don't know how to put NOCOMPRESS in one line.
Since NOCOMPRESS is in 2 lines, I am having ORA-00922: NOC Invalid or missing option.

Thanks.
Re: dbms_metadata.get_ddl introduces a newline in the ddl [message #216599 is a reply to message #178234] Mon, 29 January 2007 11:23 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
SQL> SET LONG 31000
Re: dbms_metadata.get_ddl introduces a newline in the ddl [message #217004 is a reply to message #216599] Wed, 31 January 2007 05:02 Go to previous messageGo to next message
agostino_neto
Messages: 180
Registered: July 2005
Senior Member
Hi anacedent,
I already have long 90000
Re: dbms_metadata.get_ddl introduces a newline in the ddl [message #217013 is a reply to message #217004] Wed, 31 January 2007 05:56 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Did you try the same formatting options as i did in previous example? (lines 6 and 8 )?
Create a column alias ( i called it XXXX) and do a column format on it (increase it if you want)

[Updated on: Wed, 31 January 2007 05:56]

Report message to a moderator

Re: dbms_metadata.get_ddl introduces a newline in the ddl [message #217069 is a reply to message #217013] Wed, 31 January 2007 09:57 Go to previous messageGo to next message
agostino_neto
Messages: 180
Registered: July 2005
Senior Member
Many thanks Mahesh.
It is working now. The last problem is I am not able to suppress object owner on the result.
I tried exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'SCHEMA', false) but that 'SCHEMA' is not valid.
Re: dbms_metadata.get_ddl introduces a newline in the ddl [message #217071 is a reply to message #217069] Wed, 31 January 2007 10:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
What i meant is
scott@9i > SELECT replace(DBMS_METADATA.GET_DDL('INDEX',D.INDEX_NAME)||';','"SCOTT".') FROM user_indexes D
  2  ;

REPLACE(DBMS_METADATA.GET_DDL('INDEX',D.INDEX_NAME)||';','"SCOTT".')
--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "SYS_C001612" ON "DEPT" ("DEPTNO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
 ;

Re: dbms_metadata.get_ddl introduces a newline in the ddl [message #217078 is a reply to message #217071] Wed, 31 January 2007 10:43 Go to previous messageGo to next message
agostino_neto
Messages: 180
Registered: July 2005
Senior Member
Thanks Mahesh
Re: dbms_metadata.get_ddl introduces a newline in the ddl [message #217314 is a reply to message #217078] Thu, 01 February 2007 11:41 Go to previous message
agostino_neto
Messages: 180
Registered: July 2005
Senior Member
Hi Mahesh,

I think this link is interesting for formatting long values.
We won't be sure that our formatting options will be convenient for an unknown table structure.
Thanks


Previous Topic: IN function
Next Topic: insert record and increment date
Goto Forum:
  


Current Time: Sat Dec 10 16:44:25 CST 2016

Total time taken to generate the page: 0.12957 seconds