Re: Execute Immediate DDL & DML

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 12 Feb 2007 16:05:02 -0800
Message-ID: <1171325101.781821@bubbleator.drizzle.com>

00steve wrote:

> On 11 Feb, 18:16, DA Morgan <damor..._at_psoug.org> wrote:

>> 00steve wrote:

>>> 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.

>> I agree with what Frank wrote but I want to play devil's advocate for a
>> moment.

>>

>> What is the value in learning bad practices?

>>

>> You could easily spend the next ten years of your life learning things
>> of importance to managing databases. Why spend five minutes on something
>> that will not work except in a stand-alone, single-user, environment?

>>

>> If you want to reset a sequence ...http://www.psoug.org/reference/sequences.html
>> and scroll to "Sequence Resets"
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -

>>

>> - Show quoted text -

> 
> Ok, here's the situation. I have a coldfusion script which extracts a
> build script from the data occupying 11 tables. I wrote this quickly
> to save me the time involved in manually coding the INSERT build
> script which will populate the system with the standard letter
> templates (the letter templates were created through the same system,
> although development and production systems are completely separate,
> hence the need for a script to populate the production system) The
> thing is, that once the script is executed, the sequences must be
> incremented until they reach the value defined in the build script, to
> make future additions (user-created templates function correctly)

It's a build script. Why not just create the sequence with the correct START value?

But if you must ... you could always do something like this: (and it is not intended to be real code)

LOOP
   BEGIN
     INSERT
   EXCEPTION

     WHEN dup_val_on_index THEN
       INCREMENT the sequence

   END;
END LOOP;

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Feb 12 2007 - 18:05:02 CST