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-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
> 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.
Received on Tue Feb 13 2007 - 04:03:36 CST
