Re: Creating sequences.

John Jones <john_at_iluvatar.tip.duke.edu> wrote:

>> : I am trying to reinitialize a sequence number, depending on the change
>> : of a date. The following is the code I am using, however it keeps erroring
>> : out on the select statement. Can anyone help???
 

>> : create sequence job_sequence_num
>> : increment by 1
>> : maxvalue (select distinct job_num_yy||job_num_mm||job_num_dd
>> : from job_log_tbl
>> : order byh job_num_yy||job_num_mm||job_num_dd;)
>> : minvalue 1
>> : cycle;
>>
 

> As far as I know, you can not do this. I have tried before and even
>call Oracle Tech Support and they were unable to help. Even though the
>select is returning a number it is treated like a varchar() since it is
>in the select. If any knows how to do this, please let me know also.

You could use the package DBMS_SQL for dynamic SQL in stored procedure. Make a select into a variable and then build up the create sequence-command.

Chris
>--------------------------------------------------------------------
>John Jones | my views are my own.........
>Oracle Consultant | no matter what company they are
>john_at_iluvatar.tip.duke.edu | coming from.
>--------------------------------------------------------------------

Christian Günther                   dbbetr_at_ztivax.zfe.siemens.de