Re: Startup Trigger Can't Do DDL ...

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Sun, 02 May 2004 10:38:55 +0200
Message-ID: <c72c30$idp$1@news.BelWue.DE>

Domenic wrote:
<rearanged, don't top post>

> Holger Baer <holger.baer_at_science-computing.de> wrote in message news:<c6t489$j3h$1_at_news.BelWue.DE>...
> 

>>Howard J. Rogers wrote:
>>
>>>And never mind the loss of numbers when an instance crashes: what about
>>>the loss of numbers that will happen when somebody rolls back their
>>>transaction (or suffers a blue screen of death in mid-transaction and
>>>has PMON roll it back for them)? Or when the junior DBA wanting to know
>>>what the last sequence number issued was happens to select from currval?
>>>
>>
>>Just curious (or lazy if you wan't, because I didn't dig into the
>>documentation just now): What do you think that happens?
>>Selecting from currval returns the last retrieved number for the *CURRENT*
>>session - if it had never been retreived you get an
>>
>>select sequencename.currval from dual;
>>
>>ORA-08002: sequence <sequencename> is not yet defined in this session
>>
>>
>>This is what a quick test on 10g confirmed.
>>
>>Regards,
>>
>>Holger

> And what would that be?  You didn't answer the question -- the docs
> say that an autonomous trigger can perform DDL -- and it doesn't work.
>  It should regardless of whether I want to drop a sequence or not. 
> What good are these advanced features if they don't work!!
> 
> Domenic.
> 
> 

I was merely commenting on Howards last sentence in that paragraph, my post had nothing to do with anything you wrote. OTH Chris gave you a metalink note which you can either look up or not. It's up to you.

However, what you try to achive, and this is where Howards post comes into play, is completely useless because you don't loose sequence values *ONLY* during shutdown/startup, but also by means of rollbacked transactions, or by someone querying sequence.nextval just to determine the current value of the sequence.

This all not withstanding the fact that autonomous triggers don't work as expected, which the mentioned metalink note seems to document.

Regards,
Holger Received on Sun May 02 2004 - 03:38:55 CDT