Creating Sequences in Oracle

[joeythelips]

Start Your Own Group on
Tek-Tips! Click Here!
My Threadminder
Activity since last visit
Remove forum(s) from list
MIS/IT
Lotus/IBM: Notes
Microsoft: Office
Andrew Tridgell: Samba
-General UNIX discussion
HP: HP-UX
Microsoft: Internet Information Server
Programmers
Microsoft SQL Server: Programming
Microsoft SQL Server: Setup and Administration
Microsoft: Access Forms
Microsoft: Access Modules (VBA Coding)
Microsoft: Access Queries and JET SQL
Microsoft: Access Tables and Relationships
Microsoft: Access Other topics
Microsoft: VFP -General Coding Issues
MySQL AB: MySQL
Oracle: Oracle release - 5, 6, and 7
Oracle: Oracle release - 8 and 8i
Macromedia (Allaire): ColdFusion
Microsoft: Active Server Pages (ASP)
ANSI_SQL
Perl
VBScript
Visual Basic(Microsoft): Version 5 & 6
XML
Business Objects: Crystal Reports 4 Other topics
Server Rack
VERITAS: Backup Exec back-up
Application Management
Application Deployment
Enterprise Applications
Oracle ERP Solutions
Project and Data Management
Data warehousing general discussion
Certification and Testing
Oracle certification and testing
Partners
"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)
Feedback
"...I just wanted to say how much I have enjoyed these forums. I am a new user and have a lot of elementary questions. I get quick answers with a friendly attitude..."
More...
Geography
Where in the world do Tek-Tips members come from?
Click Here To Find Out!
Partners
ASP Alliance
Code Project
Developer Fusion
Developers Dex
devGuru
Planet Source Code
Programmers Heaven
Tek-Tips Forums
Find A Forum Search Posts (Keyword) Thread Number Threads by Handle Search FAQs (Keyword) Search Links (Keyword) Find An Expert Advanced Search

Home > Forums > Programmers > Languages > ANSI_SQL Forum
Creating a Sequence in Oracle
thread220-1070198
Forum Search FAQs Links Jobs

Read
New Posts Reply To
This Thread
E-mail It
Print It Next
Thread

joeythelips (IS/IT--Manageme) 2 Jun 05 4:10
I am try to creata a sequence starting with a value from another table
CREATE SEQUENCE CALL_PRODUCT_SEQ
START WITH (SELECT CURR_VALUE FROM snap_counter where name ='AIB_CALL_PRODUCT_SEQ') INCREMENT BY 1

however it won't allow it, I have also tried to create the sequence starting with 1 and then try and increment using

alter sequence CALL_PRODUCT_SEQ increment by
(SELECT CURR_VALUE FROM snap_counter where name ='AIB_CALL_PRODUCT_SEQ')
this does'nt work either, has anyone any suggestions?

 

[SantaMufasa]

Joey,

Here is code that does what you want:

Section 1 -- Confirmation of sequence states:

SQL> select AIB_CALL_PRODUCT_SEQ.currval from dual;

   CURRVAL
----------
       100

1 row selected.

SQL> select CALL_PRODUCT_SEQ.nextval from dual;
select CALL_PRODUCT_SEQ.nextval from dual
       *
ERROR at line 1:
ORA-02289: sequence does not exist

Section 2 -- Code to produce new sequence:

declare
    seq_hold number;
    create_stm  varchar2(100);
begin
    begin
        select AIB_CALL_PRODUCT_SEQ.currval into seq_hold from dual;
    exception
        when others then
            select AIB_CALL_PRODUCT_SEQ.nextval into seq_hold from dual;
    end;
    create_stm := 'create sequence CALL_PRODUCT_SEQ start with '||seq_hold;
    execute immediate create_stm;
end;
/

PL/SQL procedure successfully completed.

SQL> select CALL_PRODUCT_SEQ.nextval from dual;

   NEXTVAL
----------
       100

1 row selected.

Let us know if this does what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.