Re: How to determine the Oracle session's constraint state?
From: joel garry <joel-garry_at_home.com>
Date: Tue, 9 Feb 2010 10:45:50 -0800 (PST)
Message-ID: <9b55d403-2ff0-47bc-a21f-3713f51bfd7c_at_e19g2000prn.googlegroups.com>
On Feb 8, 10:09 am, "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?
Date: Tue, 9 Feb 2010 10:45:50 -0800 (PST)
Message-ID: <9b55d403-2ff0-47bc-a21f-3713f51bfd7c_at_e19g2000prn.googlegroups.com>
On Feb 8, 10:09 am, "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?
http://forums.oracle.com/forums/thread.jspa?messageID=3575293
Although I personally would have expected the user_constraints.deferred column to reflect that. Is that a bug or documentation insufficiency?
Give some ddl/dml so we can all be sure to be on the same page.
jg
-- _at_home.com is bogus. http://ostatic.com/blog/oracle-cuts-affect-gnome-accessibility-workReceived on Tue Feb 09 2010 - 12:45:50 CST