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

Workaround for the ORA-4091 error when checking ranges

The Oracle specific problem of not being able to handle access to the current table that a trigger is created for (ORA-4091) creates serious problems for us. Maybe you have an idea for a workaround?

We have a table with three columns: low, high and id. The table defines ranges from low to high, in which the id is valid. No ranges may overlap. To insure this, triggers or something similar are necessary. The calling application cannot perform this check. For insert it works because the Oracle specific problem ORA-4091 does not apply for before insert triggers. For an update, I have to do a check if the updated low or high conflicts with an existing range. Therefor I have to access the table. But due to the Oracle limitation ORA-4091, this is not possible.

So how can this be solved? Changing to a DBMS without this limitation is impossible. Would a call from trigger to a stored procedure help? ould we kill a lock manually? Could we raise an exception and perform an exception trigger, or is there still the limitation? Any ideas?

The best thing to do here is to give up the trigger check and have the check be done by a stored function called from the application you're writing. I don't know if you have control of the application code, but in this case it's the preferred solution.

Do the check in a function called f_check_range that returns a boolean true/false checking if the range given is valid. A call to a stored procedure from a trigger will not work because its RPC, and Oracle knows a trigger was fired on the accessed table.

If a trigger is ABSOLUTELY necessary, then create a COPY of that table that is maintained using Insert/Update/Delete triggers. And have the check done on the COPIED table.

For More Information

Dig Deeper on Oracle error messages

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.