00steve schreef:
> On 9 Feb, 16:42, DA Morgan <damor..._at_psoug.org> wrote:
>> 00steve wrote:
>>> Thanks for your reponse. I have used SET ROLE NONE, and can still
>>> select from user_sequences table.
>> I'm with Frank. This is dangerous and guaranteed to fail. The only
>> question is "When?"
>>
>> What is the business case?
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org
>
> Hi, thanks for all replies. I should have really explained; I'm really
> more concerned with learning PL/SQL, the script will never be used by
> other developers. I am just interested in having a procedure call that
> I can use in build scripts to calibrate sequences after table inserts
> (letter management system which ships with some standard templates).
> As the sequence and tables are completely separate within the DBMS
> there is always going to be the risk that the function be misused.
> None-the-less, I still feel that it is a useful procedure and learning
> experience. I have found your comments insightful.
>
Normally, the sequence would have been updated as a result
of your inserts. There is (should be) a trigger that generates
a new, higher ID# by selecting nextval from the sequence. As
a result from that, the sequence is always ahead of your ID's,
by at least one (depends somewhat on the characteristics of the
sequence, like the definition of CACHE (default: 20) and INCREMENT
BY (default: +1))
So - this is an awkward business case to start with. You should
have mentioned it was for exercises, you would have gotten different
answers.
--
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
Received on Sun Feb 11 2007 - 04:25:11 CST
