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

From: John Peterson <>
Date: Tue, 9 Feb 2010 17:32:27 -0700
Message-ID: <>

"Malcolm Dew-Jones" <> wrote in message news:4b71d405$
> John Peterson ( wrote:
> : Hello!
> : 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

Thanks, Malcolm! Yeah -- I toyed with the idea of having a utility procedure -- but we have application code that can't be easily retrofitted to call out the new utility procedure. Which is why I was hoping to be able to make the determination via the RDBMS metadata.

But, as I'm discovering, that's all but impossible. :-(   Received on Tue Feb 09 2010 - 18:32:27 CST

Original text of this message