Re: How to determine the Oracle session's constraint state?
Date: Tue, 9 Feb 2010 08:56:52 -0700
Message-ID: <EdmdnYRc2L7aG-zWnZ2dnUVZ_t2dnZ2d_at_giganews.com>
"ddf" <oratune_at_msn.com> wrote in message news:cf165c3c-5766-45d0-8b41-d5a9f1cf13ce_at_b7g2000pro.googlegroups.com...
> On Feb 8, 1:09 pm, "John Peterson" <j0..._at_comcast.net> wrote:
>> "Mark D Powell" <Mark.Powe..._at_hp.com> wrote in
>> messagenews: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?- Hide quoted text -
>>
>> - Show quoted text -
> > Deferrable constraints are either deferred or immediate and that is > set at the constraint level, which is independent of the session. One > does not execute > > alter session set constraint ... > > one submits > > alter constraint ... > > Why would you think such changes would be at the session level? The > constraint is already listed as DEFERRABLE/IMMEDIATE; setting it to > DEFERRED would not change that. > > > David Fitzjarrell.
Hi David! Please see the other replies in this thread. I'm trying to figure out how to programmatically determine the current constraint "state" (deferred, immediate, or default) when one of the following command examples are issued:
SET CONSTRAINTS ALL DEFERRED; or
ALTER SESSION SET CONSTRAINTS=DEFERRED; I had originally thought that maybe the SYS_CONTEXT function could be used -- but I don't see any applicable options. There *must* be some metadata that Oracle saves to manage this, but I'm not sure whether that's exposed to the user (I *hope* that it is).
Trying to find this via Google or other web searches has been like finding a needle in a haystack. ;-) Received on Tue Feb 09 2010 - 09:56:52 CST