Re: How to determine the Oracle session's constraint state?

: First time poster in this forum -- please forgive me if this is the wrong
: place for my question. I've exhausted a web search on this issue, and was
: hoping that this might be a more targeted approach.

: I am trying to programmatically determine the current session's constraint
: state (immediate, deferred, or default).

: I have a procedure that I'd like to implement which would essentially
: temporarily set the constraints "deferred" (e.g. SET CONSTRAINTS ALL
: DEFERRED), perform some work, and then restore the constraint setting to the
: original state (e.g., immediate). However, I'm having a difficult time
: identifying how to determine the current state of the constraints.

: Any help would be very much appreciated!

Assuming that the developer always use the ALL keyword when the state is set/unset, you could try using a flag table with a deferable unique constraint and then write a non-unique value into that table. An exception shows that constraints are not deferred (and handling the exception prevents it from rolling anything else back so the test is safe to use). If there's no exception then delete the value from the flag table and continue as normal.

If the purpose is to set and restore the original setting within a single application then perhaps it would be easier and just as useful for an application to simply use a utility procedure that uses its own session variable to track the state and restore it after an equal number of sets and unsets.

$0.10 Received on Tue Feb 09 2010 - 14:30:45 CST