00steve 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-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.
Go to Morgan's Library at www.psoug.org
click on Exception Handling
scroll to the bottom for a list of named exceptions.
--
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 Tue Feb 13 2007 - 11:33:02 CST
