Re: Creating sequences.
Date: 1996/03/08
Message-ID: <NEWTNews.826351661.26937.elric_dm_at_dialup.netvision.net.il>#1/1
In Article<4hgqlu$21f_at_salomon.zfe.siemens.de>, <guenther_at_P2.MCH1.SIEMENS.x400scn.gw.sni.de> write:
> Path: news.NetVision.net.il!psinntp!psinntp!psinntp!howland.reston.ans.net!Germany.EU.net!Dortmund.Germany.EU.net!news.mch.sni.de!news.mchp.siemens.de!news
> From: guenther_at_P2.MCH1.SIEMENS.x400scn.gw.sni.de (Christian Guenther)
> Newsgroups: comp.databases.oracle
> Subject: Re: Creating sequences.
> Date: Tue, 05 Mar 1996 07:33:15 GMT
> Organization: SBS IP32M
> Lines: 36
> Message-ID: <4hgqlu$21f_at_salomon.zfe.siemens.de>
> References: <4gu0fd$hii_at_lec.okcu.edu> <4h1fr6$s2r_at_news.dot.gov> <Pine.NXT.3.90.960301152308.28636C-100000_at_iluvatar>
> NNTP-Posting-Host: potd1935.zfe.siemens.de
> X-Newsreader: Forte Free Agent 1.0.82
>
> 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
> Siemens AG RK M/Pdb32
> Tel. 089/63643965
>
why not try:
spool thanks.sql
select 'create sequence job_sequence_num
increment by 1
maxvalue (' job_num_yy||job_num_mm||job_num_dd
from job_log_tbl
where rownum = 1 ' ||
'minvalue 1
cycle; '
/
spool off
_at_thanks
p.s. Your original calculation of the maxvalue was diffrent so ... good luck Received on Fri Mar 08 1996 - 00:00:00 CET