Re: Execute Immediate DDL & DML

On Feb 13, 5:03 am, "00steve" <steven_macl..._at_hotmail.com> wrote:
> On Feb 13, 12:05 am, DA Morgan <damor..._at_psoug.org> wrote:
>
>
>
> > 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-Hidequoted 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
> > damor..._at_x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text -
>
> > - Show quoted text -
>
> The schema has already been built, with the exception of the example
> letters. This part of the process I had no interaction with. So now I
> am in the situation of having to alter the existing schema. I didnt
> know about the dup_val_on_index exception; I will look into this.

So why do it in a PROCEDURE?

Such admin type one-offs are more direct in a straight SQL script.

Like Daniel said, learning bad practices is a bad idea. It is something my choir director has started reminding us of regularly in practice. Like that old crossing the street safety rule said: STOP WATCH LISTEN.   ED Received on Thu Mar 01 2007 - 20:28:10 CST