Re: Problems in DDL trigger usage.

1. 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_at_x.washington.edu
(replace x with u to respond)