Re: dynamic value for sequence

From: Karsten Farell <kfarrell_at_medimpact.com>
Date: Tue, 24 Sep 2002 20:00:07 GMT
Message-ID: <bv3k9.1208$Ov5.27002139@newssvr14.news.prodigy.com>

Well now, maybe it's because I rubbed my magic lamp instead of my lucky stone, but here's one way to do it ... depending on whether or not you can pass an anonymous pl/sql block instead of a sql statement:

declare

   starting_with varchar2(5) := 99;
begin

   execute immediate 'create sequence xyz start with '||starting_with; end;
/
select xyz.nextval from dual;

    NEXTVAL

---

         99

Martin Doherty wrote:

> Well, you can wish and wish and wish while rubbing your lucky stone, but it
> doesn't change Oracle SQL syntax :).  The START WITH clause will only accept an
> integer constant, therefore you must determine the value of that integer and
> then construct the CREATE SEQUENCE command accordingly. You will not be able to
> accomplish what you want in a single statement.
> 
> Martin Doherty
> 
> "Ingo R. Kirchner" wrote:
> 
> 

>>Hi Daniel,
>>
>>I'm just developing a vba routine in access to move access-tables to oracle
>>server, so i like to send sql pass-through queries dynamicly to oracle.
>>Therefore wouldn't it be better to have it a in one sql statement?
>>
>>Ingo
>>
>>"Daniel Morgan" <dmorgan_at_exesolutions.com> schrieb im Newsbeitrag
>>news:3D908E6E.9CA85927_at_exesolutions.com...
>>
>>>"Ingo R. Kirchner" wrote:
>>>
>>>
>>>>Hi,
>>>>
>>>>I would like to create a sequence with a start value based on the
>>>
>>maximal
>>
>>>>Value of the table
>>>>
>>>>something like
>>>>
>>>>create sequence software_seq
>>>>start with (select max(softwareid) from software)+1
>>>>increment by 1
>>>>nomaxvalue;
>>>>
>>>>but it doesn't work this way
>>>>
>>>>any help appreciated, thanks in advance
>>>>
>>>>Ingo
>>>
>>>SELECT MAX(column_name) FROM table;
>>>
>>>CREATE SEQUENCE sequence_name START WITH <the number previously returned
>>>here>
>>>
>>>unless you want to put it all together in code with dynamic SQL but I
>>
>>can't
>>
>>>imagine why.
>>>
>>>Daniel Morgan
>>>
>>
Received on Tue Sep 24 2002 - 15:00:07 CDT