DDL Trigger on schema - capturing content

#1

I'm using a trigger like so:
CREATE OR REPLACE
TRIGGER TRG_SCHEMA_CHANGES
AFTER DDL ON "MYSCHEMA".SCHEMA

To capture all schema changes to a table I made. I use these variables
to capture some pertinent data:
ora_login_user;
ora_sysevent;
ora_dict_obj_type;
ora_dict_obj_owner;
ora_dict_obj_name;

I put the above into fields of the table I made. Seems to work fine.

What I would like to know is, how do I capture the ddl statement
itself? I'd like to put the DDL statement itself that fired the
trigger into a clob/blob field.

#2

Luch schrieb:

Quote:

I'm using a trigger like so:
CREATE OR REPLACE
TRIGGER TRG_SCHEMA_CHANGES
AFTER DDL ON "MYSCHEMA".SCHEMA

To capture all schema changes to a table I made. I use these variables
to capture some pertinent data:
ora_login_user;
ora_sysevent;
ora_dict_obj_type;
ora_dict_obj_owner;
ora_dict_obj_name;

I put the above into fields of the table I made. Seems to work fine.

What I would like to know is, how do I capture the ddl statement
itself? I'd like to put the DDL statement itself that fired the
trigger into a clob/blob field.

Have you looked at
http://download.oracle.com/docs/cd/B...s.htm#ADFNS012
in particular on ora_sql_txt ?

Best regards

Maxim

#3

Luch schrieb:

Quote:

I'm using a trigger like so:
CREATE OR REPLACE
TRIGGER TRG_SCHEMA_CHANGES
AFTER DDL ON "MYSCHEMA".SCHEMA

To capture all schema changes to a table I made. I use these variables
to capture some pertinent data:
ora_login_user;
ora_sysevent;
ora_dict_obj_type;
ora_dict_obj_owner;
ora_dict_obj_name;

I put the above into fields of the table I made. Seems to work fine.

What I would like to know is, how do I capture the ddl statement
itself? I'd like to put the DDL statement itself that fired the
trigger into a clob/blob field.

Have you looked at
http://download.oracle.com/docs/cd/B...s.htm#ADFNS012
in particular on ora_sql_txt ?

Best regards

Maxim

#4

Luch schrieb:

Quote:

I'm using a trigger like so:
CREATE OR REPLACE
TRIGGER TRG_SCHEMA_CHANGES
AFTER DDL ON "MYSCHEMA".SCHEMA

To capture all schema changes to a table I made. I use these variables
to capture some pertinent data:
ora_login_user;
ora_sysevent;
ora_dict_obj_type;
ora_dict_obj_owner;
ora_dict_obj_name;

I put the above into fields of the table I made. Seems to work fine.

What I would like to know is, how do I capture the ddl statement
itself? I'd like to put the DDL statement itself that fired the
trigger into a clob/blob field.

Have you looked at
http://download.oracle.com/docs/cd/B...s.htm#ADFNS012
in particular on ora_sql_txt ?

Best regards

Maxim

#5

Luch schrieb:

Quote:

I'm using a trigger like so:
CREATE OR REPLACE
TRIGGER TRG_SCHEMA_CHANGES
AFTER DDL ON "MYSCHEMA".SCHEMA

To capture all schema changes to a table I made. I use these variables
to capture some pertinent data:
ora_login_user;
ora_sysevent;
ora_dict_obj_type;
ora_dict_obj_owner;
ora_dict_obj_name;

I put the above into fields of the table I made. Seems to work fine.

What I would like to know is, how do I capture the ddl statement
itself? I'd like to put the DDL statement itself that fired the
trigger into a clob/blob field.

Have you looked at
http://download.oracle.com/docs/cd/B...s.htm#ADFNS012
in particular on ora_sql_txt ?

Best regards

Maxim

#6

Luch wrote:

Quote:

I'm using a trigger like so:
CREATE OR REPLACE
TRIGGER TRG_SCHEMA_CHANGES
AFTER DDL ON "MYSCHEMA".SCHEMA

To capture all schema changes to a table I made. I use these variables
to capture some pertinent data:
ora_login_user;
ora_sysevent;
ora_dict_obj_type;
ora_dict_obj_owner;
ora_dict_obj_name;

I put the above into fields of the table I made. Seems to work fine.

What I would like to know is, how do I capture the ddl statement
itself? I'd like to put the DDL statement itself that fired the
trigger into a clob/blob field.

Demo in Morgan's Library under DDL event triggers at www.psoug.org.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#7

Luch wrote:

Quote:

I'm using a trigger like so:
CREATE OR REPLACE
TRIGGER TRG_SCHEMA_CHANGES
AFTER DDL ON "MYSCHEMA".SCHEMA

To capture all schema changes to a table I made. I use these variables
to capture some pertinent data:
ora_login_user;
ora_sysevent;
ora_dict_obj_type;
ora_dict_obj_owner;
ora_dict_obj_name;

I put the above into fields of the table I made. Seems to work fine.

What I would like to know is, how do I capture the ddl statement
itself? I'd like to put the DDL statement itself that fired the
trigger into a clob/blob field.

Demo in Morgan's Library under DDL event triggers at www.psoug.org.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#8

Luch wrote:

Quote:

I'm using a trigger like so:
CREATE OR REPLACE
TRIGGER TRG_SCHEMA_CHANGES
AFTER DDL ON "MYSCHEMA".SCHEMA

To capture all schema changes to a table I made. I use these variables
to capture some pertinent data:
ora_login_user;
ora_sysevent;
ora_dict_obj_type;
ora_dict_obj_owner;
ora_dict_obj_name;

I put the above into fields of the table I made. Seems to work fine.

What I would like to know is, how do I capture the ddl statement
itself? I'd like to put the DDL statement itself that fired the
trigger into a clob/blob field.

Demo in Morgan's Library under DDL event triggers at www.psoug.org.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#9

Luch wrote:

Quote:

I'm using a trigger like so:
CREATE OR REPLACE
TRIGGER TRG_SCHEMA_CHANGES
AFTER DDL ON "MYSCHEMA".SCHEMA

To capture all schema changes to a table I made. I use these variables
to capture some pertinent data:
ora_login_user;
ora_sysevent;
ora_dict_obj_type;
ora_dict_obj_owner;
ora_dict_obj_name;

I put the above into fields of the table I made. Seems to work fine.

What I would like to know is, how do I capture the ddl statement
itself? I'd like to put the DDL statement itself that fired the
trigger into a clob/blob field.

Demo in Morgan's Library under DDL event triggers at www.psoug.org.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org