Problem solve Get help with specific problems with your technologies, process and projects.

Determining COMPILE property

I have a database level trigger and I am doing some activity based on the Oracle system event. Basically I am restricting all the ALTER commands being issued. I am checking like this:

If ora_sysevent = 'ALTER' then restricted message etc. and it stops. But, I need ALTER PROCEDURE proc_name COMPILE to work and it should not be stopped. For that I need to know, is there any system event to tell me that the action being initiated is "COMPILE" with the ALTER DDL command? Is there any system event to tell this? We have so many system events like ora_obj_type, ora_sysevent etc. etc.

I need it to modify my database trigger. Please tell me how I can do this or if there's any workaround to achieve the same.
I don't know of any way to determine the COMPILE property other than to use the ora_sql_txt attribute to retrieve the SQL text of the triggering statement. Then you'd have to parse the statement to see if the COMPILE option was used. Check the Application Developer's Guide for more info on ora_sql_txt.

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.