On Feb 8, 10:09*am, "John Peterson" <j0...@comcast.net> wrote:
> "Mark D Powell" <Mark.Powe...@hp.com> wrote in messagenews:63f31d18-8229-43b2-9fbb-1c79cb5bdefc@l26g2000yqd.googlegroups.com...
>
>
>
> > On Feb 8, 12:08 pm, "John Peterson" <j0...@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/thre...sageID=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
--
@home.com is bogus.
http://ostatic.com/blog/oracle-cuts-...ssibility-work

John Peterson (j0hnp@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!

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

"joel garry" <joel-garry@home.com> wrote in message
news:9b55d403-2ff0-47bc-a21f-3713f51bfd7c@e19g2000prn.googlegroups.com...
> On Feb 8, 10:09 am, "John Peterson" <j0...@comcast.net> wrote:
>> "Mark D Powell" <Mark.Powe...@hp.com> wrote in
>> messagenews:63f31d18-8229-43b2-9fbb-1c79cb5bdefc@l26g2000yqd.googlegroups.com...
>>
>>
>>
>> > On Feb 8, 12:08 pm, "John Peterson" <j0...@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/thre...sageID=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
> --
> @home.com is bogus.
> http://ostatic.com/blog/oracle-cuts-...ssibility-work

AHA! That's *exactly* what I'm looking for! Thank you!

Can I create a view based off of this x$ view, that I can then grant to a
"standard" user of the system? I'm guessing so...but I guess I'll play
around. :-)

"John Peterson" <j0hnp@comcast.net> wrote in message
news:SM-dnf2DM_dAV-zWnZ2dnUVZ_t6dnZ2d@giganews.com...
>
> "joel garry" <joel-garry@home.com> wrote in message
> news:9b55d403-2ff0-47bc-a21f-3713f51bfd7c@e19g2000prn.googlegroups.com...
>> On Feb 8, 10:09 am, "John Peterson" <j0...@comcast.net> wrote:
>>> "Mark D Powell" <Mark.Powe...@hp.com> wrote in
>>> messagenews:63f31d18-8229-43b2-9fbb-1c79cb5bdefc@l26g2000yqd.googlegroups.com...
>>>
>>>
>>>
>>> > On Feb 8, 12:08 pm, "John Peterson" <j0...@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/thre...sageID=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
>> --
>> @home.com is bogus.
>> http://ostatic.com/blog/oracle-cuts-...ssibility-work
>
> AHA! That's *exactly* what I'm looking for! Thank you!
>
> Can I create a view based off of this x$ view, that I can then grant to a
> "standard" user of the system? I'm guessing so...but I guess I'll play
> around. :-)

Well, nuts. Upon further review, this isn't *quite* what I'm looking for
(it's close!).

As it turns out, this works when the ALTER SESSION syntax is used.

However, it *doesn't* work when the SET CONSTRAINTS syntax is used. <sigh>

Ideally I would be able to determine *both* aspects. I wonder if there's an
x$ view that deals with transactions? When I review the list here:

http://yong321.freeshell.org/computer/x$table.html

I don't see anything that's immediately obvious.

Why does this have to be so hard!

"Malcolm Dew-Jones" <yf110@vtn1.victoria.tc.ca> wrote in message
news:4b71d405$1@news.victoria.tc.ca...
> John Peterson (j0hnp@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!
>
> 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. :-(

On Feb 9, 4:31*pm, "John Peterson" <j0...@comcast.net> wrote:
> "John Peterson" <j0...@comcast.net> wrote in message
>
> news:SM-dnf2DM_dAV-zWnZ2dnUVZ_t6dnZ2d@giganews.com...
>
>
>
>
>
> > "joel garry" <joel-ga...@home.com> wrote in message
> >news:9b55d403-2ff0-47bc-a21f-3713f51bfd7c@e19g2000prn.googlegroups.com....
> >> On Feb 8, 10:09 am, "John Peterson" <j0...@comcast.net> wrote:
> >>> "Mark D Powell" <Mark.Powe...@hp.com> wrote in
> >>> messagenews:63f31d18-8229-43b2-9fbb-1c79cb5bdefc@l26g2000yqd.googlegroups.com...
>
> >>> > On Feb 8, 12:08 pm, "John Peterson" <j0...@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 inthe
> >>> 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/thre...sageID=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
> >> --
> >> @home.com is bogus.
> >>http://ostatic.com/blog/oracle-cuts-...ssibility-work
>
> > AHA! *That's *exactly* what I'm looking for! *Thank you!
>
> > Can I create a view based off of this x$ view, that I can then grant toa
> > "standard" user of the system? *I'm guessing so...but I guess I'll play
> > around. *:-)
>
> Well, nuts. *Upon further review, this isn't *quite* what I'm looking for
> (it's close!).
>
> As it turns out, this works when the ALTER SESSION syntax is used.
>
> However, it *doesn't* work when the SET CONSTRAINTS syntax is used. *<sigh>

Well, that's probably because the difference isn't trivial, the
session can go over many transactions, but the set constraints is just
a transaction.

>
> Ideally I would be able to determine *both* aspects. *I wonder if there's an
> x$ view that deals with transactions? *When I review the list here:
>
> http://yong321.freeshell.org/computer/x$table.html
>
> I don't see anything that's immediately obvious.
>
> Why does this have to be so hard!

Presumably, your code knows when it is in a transaction, rather than
having to figure it out. I don't deal with this, because the language
I use has a built-in to do it - so I know it is possible, but no clue
how, and it quite possibly is just tracking it with its own
variables. But I have to deal with loops unwinding levels of pseudo-
transactions until a rollback will actually work, so it's always
something.

jg
--
@home.com is bogus.
http://latimesblogs.latimes.com/.a/6...a80b970c-800wi