Re: possible to 'reset' a sequence by a stored procedure?

On Fri, 19 Dec 2003 13:32:49 +0100, Stefan Röhle <roehle_at_uni-mainz.de> wrote:

>Hi,
>
>I need to 'reset' a stored procedure each time the year changes (2003->2004).
>So what I usually do is a manual
>
>drop sequence seq_vorgangs_nr_neu;
>
>create sequence seq_vorgangs_nr_neu
>increment by 1
>maxvalue 2999
>start with 201
>nocycle
>nocache;
>
>Since it is not possible to do this in a stored procedure (or is it possible now?) I am looking for
>a way to get this done automatially.
>Is there any other way than using a scripting language?
>
>Stefan

It has been possible to do DDL in stored procedures for ages. Use dbms_sql (pre 8i) or execute immediate (8i and higher). It may been undesirable as DDL commits automatically.

--
Sybrand Bakker, Senior Oracle DBA