Re: Capture of DDL Statement

In article <3C9509CC.646536E0_at_ix.netcom.com>, Jerry says...
>
>This is a multi-part message in MIME format.
>--------------0EAA5E16632A80E1490EFD32
>Content-Type: text/plain; charset=us-ascii
>Content-Transfer-Encoding: 7bit
>
>I want to be able to capture in a database trigger all of the changes
>made to the database schemas by any and all users. As a part of that, I
>can get the user_id, the object name, object type, and schema with no
>problem via sys.dictionary_obj...
>
>I am running both Oracle9i and Oracle8i Enterprise (on separate
>machines).
>
>Does anyone have any ideas on how to capture the actual ddl statement
>(either before or after it is sent to the database)?
>
>PS - I've looked in all of the manuals that I have, so, if you are going
>to send me back to the manuals for further reference checking, please
>point me to the appropriate manual to look at. Thanks.
>
>--------------0EAA5E16632A80E1490EFD32
>Content-Type: text/x-vcard; charset=us-ascii;
> name="JMetz1.vcf"
>Content-Transfer-Encoding: 7bit
>Content-Description: Card for Jerry Metz
>Content-Disposition: attachment;
> filename="JMetz1.vcf"
>
>begin:vcard
>n:Metz;Jerry
>tel;cell:847-431-2409
>tel;home:847-215-4771
>x-mozilla-html:FALSE
>org:G.L. Metz & Associates
>adr:;;;Buffalo Grove;IL;60089;USA
>version:2.1
>email;internet:JMetz1_at_ix.netcom.com
>x-mozilla-cpt:;-5688
>fn:Metz, Jerry
>end:vcard
>
>--------------0EAA5E16632A80E1490EFD32--
>

In 8i, its hard -- you have to "reconstruct" some statements. See http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1380003556171

that also mentions the existence of a new 9i function "ora_sql_txt" which is exactly what you want.

http://download-west.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a88876/adg14evt.htm#1005274

that is available only in 9i and up.
...

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp