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

"Mark D Powell" <Mark.Powell2_at_hp.com> wrote in message news:63f31d18-8229-43b2-9fbb-1c79cb5bdefc_at_l26g2000yqd.googlegroups.com...
> On Feb 8, 12:08 pm, "John Peterson" <j0..._at_comcast.net> 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!
>>
>> Kind regards,
>>
>> John Peterson
>
> See the DEFERRABLE and DEFERRED columns in the ALL_, USER_, or
> DBA_CONSTRAINTS views (documented in the Oracle version# Reference
> manual).
>
> By the way making a PK or UK deferrable would require use of a non-
> unique index to support the constraint instead of the standard unique
> index.
>
> HTH -- Mark D Powell --

Thanks, Mark!

Unfortunately, those metadata views don't seem to reflect the current session state.

That is, if I have some FKs that are deferrable (but initially immediate) (e.g., CONSTRAINT_TYPE = 'R'), it will show DEFERRABLE/IMMEDIATE in the DEFERRABLE/DEFERRED columns.

But, after I run:

SET CONSTRAINTS ALL DEFERRED Those columns are still DEFERRABLE/IMMEDIATE in the metadata views.

I had thought maybe I could obtain this information from the SYS_CONTEXT function to get the current session state information, but none of the options seem applicable.

Any other ideas?   Received on Mon Feb 08 2010 - 12:09:34 CST