Re: DDL for triggers.

From: joel garry <joel-garry_at_home.com>
Date: 27 Oct 2006 11:20:52 -0700
Message-ID: <1161973252.759191.92300@i42g2000cwa.googlegroups.com>

Maxim Demenko wrote:
> 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)

Yeah, this is how I used to get stuff 10 years ago too :-) I was starting to do it this way when I got distracted by your previous post.  Thanks for the Note.

> 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.

I generally don't have a login.sql, habit from the days when I would be on many customer systems per day. Scripts I develop tend to propagate explicit settings.

>

> 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.

Outside of my requirements, though I was playing around with it to see if it was unix-specific. Results already forgotten, I think XE beta worked ok.

I'm off to the next alligator now, thanks again.

jg

--
@home.com is bogus.
http://legal-solutions.hypermart.net/Doc10_files/image006.gif

Received on Fri Oct 27 2006 - 13:20:52 CDT