Problems in DDL trigger usage.

[A. Sriram]

Hi ALL,

I have a problem in the usage of DDL trigger. I created a DDL trigger
on a user schema on "AFTER CREATE" event to catch the creation script
of the all database objects using the system variables ora_sysevent,
ora_dict_obj_name, ora_dict_obj_type,ora_dict_obj_owner. And I am
storing all the collected information in a audit table.

For ora_dict_obj_type = 'TABLE', I used all_tab_columns and all_tables
to generate the create script.

For ora_dict_obj_type = 'VIEW', I used all_views to generate the create
script.

For ora_dict_obj_type = 'TRIGGER', I used all_triggers to generate the
create script.

For ora_dict_obj_type IN ('PROCEDURE','PACKAGE','FUNCITON','PACKAGE
BODY'), I used all_source to generate the create script.

But the problem is creation script is getting generated only when the
ora_dict_obj_type = 'TABLE' and for all the other objects,
no_data_found execption is being raised.
i.e. if a view is crated,
ALL_VEIWS is not getting the text for the below query:
select text
into l_extra
from all_views
where view_name = ora_dict_obj_name
and owner=user;
But same query is giving correct output when executed at sql prompt.

Can anyone please suggest why for the object_type like VIEW,
TRIGGER...etc, the sql text is not populated.

Thanks in Advance.

Srirama Chandra Murthy A

[Mark C. Stock]

"A. Sriram" <asrcmurthy@gmail.com> wrote in message
news:1117436191.492958.134060@g49g2000cwa.googlegroups.com... Hi ALL, I have a problem in the usage of DDL trigger. I created a DDL trigger on a user schema on "AFTER CREATE" event to catch the creation script of the all database objects using the system variables ora_sysevent, ora_dict_obj_name, ora_dict_obj_type,ora_dict_obj_owner. And I am storing all the collected information in a audit table. For ora_dict_obj_type = 'TABLE', I used all_tab_columns and all_tables to generate the create script. For ora_dict_obj_type = 'VIEW', I used all_views to generate the create script. For ora_dict_obj_type = 'TRIGGER', I used all_triggers to generate the create script. For ora_dict_obj_type IN ('PROCEDURE','PACKAGE','FUNCITON','PACKAGE BODY'), I used all_source to generate the create script. But the problem is creation script is getting generated only when the ora_dict_obj_type = 'TABLE' and for all the other objects, no_data_found execption is being raised. i.e. if a view is crated, ALL_VEIWS is not getting the text for the below query: select text into l_extra from all_views where view_name = ora_dict_obj_name and owner=user; But same query is giving correct output when executed at sql prompt. Can anyone please suggest why for the object_type like VIEW, TRIGGER...etc, the sql text is not populated. Thanks in Advance. Srirama Chandra Murthy A

have you considered just using ora_sql_text?

From the Application Developer's Guide - Fundamentals:

ora_sql_txt (sql_text out ora_name_list_t)

returns BINARY_INTEGER

Returns the SQL text of the triggering statement in the OUT parameter. If
the statement is long, it is broken up into multiple PL/SQL table elements.
The function return value specifies how many elements are in the PL/SQL
table.

++ mcs

[DA Morgan]

A. Sriram wrote: Hi ALL, I have a problem in the usage of DDL trigger. I created a DDL trigger on a user schema on "AFTER CREATE" event to catch the creation script of the all database objects using the system variables ora_sysevent, ora_dict_obj_name, ora_dict_obj_type,ora_dict_obj_owner. And I am storing all the collected information in a audit table. For ora_dict_obj_type = 'TABLE', I used all_tab_columns and all_tables to generate the create script. For ora_dict_obj_type = 'VIEW', I used all_views to generate the create script. For ora_dict_obj_type = 'TRIGGER', I used all_triggers to generate the create script. For ora_dict_obj_type IN ('PROCEDURE','PACKAGE','FUNCITON','PACKAGE BODY'), I used all_source to generate the create script. But the problem is creation script is getting generated only when the ora_dict_obj_type = 'TABLE' and for all the other objects, no_data_found execption is being raised. i.e. if a view is crated, ALL_VEIWS is not getting the text for the below query: select text into l_extra from all_views where view_name = ora_dict_obj_name and owner=user; But same query is giving correct output when executed at sql prompt. Can anyone please suggest why for the object_type like VIEW, TRIGGER...etc, the sql text is not populated. Thanks in Advance. Srirama Chandra Murthy A

Try using an AUTONOMOUS_TRANSACTION to emulate your SQL*Plus query.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)