Problems in DDL trigger usage

0 pts.
Tags:
SQL
Problems in DDL trigger usage. 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 you please suggest why for the object_type like VIEW, TRIGGER., the sql text is not getting.
ASKED: May 27, 2005  10:18 AM
UPDATED: June 3, 2005  4:18 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

I have recently implemented a similar trigger to track the database event “CREATE”. Although I use “DBA_OBJECTS” view to set the object_type and it seems to work wihtout any problem.

Try to use DBA_OBJECTS instead of “ALL_SOURCE”, “ALL_VIEW”, ..etc.

Amorn

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following