Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: DDL for triggers.

Re: DDL for triggers.

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 27 Oct 2006 09:43:52 +0200
Message-ID: <ehsdbm$ch9$01$1@news.t-online.com>


joel garry schrieb:
> joel garry wrote:

>> Maxim Demenko wrote:
>>> joel garry schrieb:
>>>
>>> I don't have 9i actually to test, but i doubt, it would behave
>>> differently. Here is for 10gR2
>>>
>>> SQL> select * from v$version
>>>    2  /
>>>
>>> BANNER
>>> --------------------------------------------------------------------------------
>>> Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
>>> PL/SQL Release 10.2.0.2.0 - Production
>>> CORE    10.2.0.2.0      Production
>>> TNS for Linux: Version 10.2.0.2.0 - Production
>>> NLSRTL Version 10.2.0.2.0 - Production
>>>
>>> SQL> set long 1000000 pages 0
>>> SQL> select dbms_metadata.get_ddl('TRIGGER','FILES_TRG') from dual;
>>>
>> Thank you Maxim, that is indeed what I was missing.
>>
>> D'Oh!
>>

>
> Actually, I D'Oh'd too soon. dbms_metadata actually is breaking up the
> first variable after the BEGIN statement in the trigger.
>
> The first few characters are like this (as displayed in EM or
> trigger_body in user_triggers):
>
> BEGIN :new.GL_BALANCE_00 :=
>
>>From dbms_metadata, it's coming out like this:

>
> BEFORE INSERT OR UPDATE ON <obscured for posting> FOR EACH ROW BEGIN
> :ne
> w.
> GL_BALANCE_00 :=
>
> (If newsreaders are reformatting, there is a newline before
> GL_BALANCE_00 and BEFORE through new. is on one line.)
>
> It's not linesize, as I can set linesize to 12000 or whatever, then
> strip out pairs of blanks with sed and it still seems to be putting out
> <80 character lines, breaking in the middle of whatever it is printing.
> trimspool on doesn't help either. Something I'm still not
> understanding about sql*plus, I guess. Or is there something I need
> to be telling dbms_metadata? Or am I forced to utl_file.put?
>
> jg

Joel, i've never seen the behaviour like you described, but my guess would be - sqlplus settings cause this line breaking. If you have already solved this, maybe it is not worth the time to investigate further, but just in case, i would try
1) to get source from the export (rows=n triggers=y) file with simple awk formatting ( Note 29765.1 ) - i used this often before dbms_metadata and never had any sort of issues, to see the actual source in the database ( for one particular table and trigger "strings exp.dmp" will be good enough as well i guess)
2) select the clob with all default settings in sqlplus ( i.e. rename your login.sql) and only set long for appropriate size ( set long 10000) to see , whether it is one of your sqlplus settings which produces the newlines.

A very obscure guess - it depends on your terminal setting, so maybe it make sense to try with a dos client and get the results in remote sqlplus.

Best regards

Maxim Received on Fri Oct 27 2006 - 02:43:52 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US