OraFAQ Forum: SQL & PL/SQL » dbms_metadata.get_ddl introduces a newline in the ddl

Home » SQL & PL/SQL » SQL & PL/SQL » dbms_metadata.get_ddl introduces a newline in the ddl

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

dbms_metadata.get_ddl introduces a newline in the ddl [message #178234]

Tue, 20 June 2006 04:00

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

Report message to a moderator

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

Mahesh Rajendran
Messages: 10708
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;

Report message to a moderator

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

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

Report message to a moderator

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

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

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

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.

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> SET LONG 31000

Report message to a moderator

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

agostino_neto
Messages: 180
Registered: July 2005

Senior Member

Hi anacedent,
I already have long 90000

Report message to a moderator

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

Mahesh Rajendran
Messages: 10708
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

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.

Report message to a moderator

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

Mahesh Rajendran
Messages: 10708
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
 ;

Report message to a moderator

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

agostino_neto
Messages: 180
Registered: July 2005

Senior Member

Thanks Mahesh

Report message to a moderator

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

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 15:08:56 CST 2024