Problems in DDL trigger usage.
Date: 29 May 2005 23:56:31 -0700
Message-ID: <1117436191.492958.134060_at_g49g2000cwa.googlegroups.com>
[Quoted] Hi ALL,
[Quoted] 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 Received on Mon May 30 2005 - 08:56:31 CEST