Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Execute Immediate DDL & DML

Re: Execute Immediate DDL & DML

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Sun, 11 Feb 2007 11:25:11 +0100
Message-ID: <eqn016$jv0$1@news4.zwoll1.ov.home.nl>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US