Re: Setting Sequence

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 07 Apr 1999 01:30:59 GMT
Message-ID: <370bb52f.4703493@192.86.155.100>

A copy of this was sent to twm_at_tlg.net
(if that email address didn't require changing) On Tue, 06 Apr 1999 20:04:30 GMT, you wrote:

>Is there any way to set a sequence based on the size of a table in a script.
>
>For Instance:
>drop sequence new_foo_id;
>create sequence new_foo_id start with last_foo_id+1;
>
>last_foo_id is a function that returns the max of the id field.
>
>When I tired the above I get an error:
>ORA-01722: invalid number
>

You can do this:

SQL> column maxv new_value m;
SQL> select max(user_id)+1 maxv from all_users;

      MAXV

---

      8024

SQL>
SQL> create sequence my_seq start with &m; old 1: create sequence my_seq start with &m new 1: create sequence my_seq start with 8024

Sequence created.

SQL> column maxv new_value m

will put the value of a column called maxv into a macro variable m when its next selected..

>Thanks,
>Tom
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Apr 06 1999 - 20:30:59 CDT