Re: Handling of Sequences in Oracle

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 9 Jul 2001 06:30:36 -0700
Message-ID: <a20d28ee.0107090530.24ba33bd_at_posting.google.com>


manoj_usa_at_yahoo.com (Manoj) wrote in message news:<2fb90e4c.0107090039.6fad58b4_at_posting.google.com>...
> In Oracle, as per my current understanding- which could be incomplete,
> is that suppose I have table Employee(EmpId, Name) where I want EmpId
> as a sequence then how I would do it is-
>
> CREATE TABLE Employee(EmpId int, name varchar(50));
> CREATE SEQUQNCE EmpSeq;
> insert into employee values(EmpSeq.nextval, 'Manoj');
>
> So the application explicitly needs to get a nextval and insert it
> into the table.
>
>
> How its handled in SQL Server is-
> CREATE TABLE Employee(EmpId int IDENTITY, name varchar(50));
> insert into employee(name) values('Manoj');
>
> In this insert the sequencse field EmpId will automatically get
> populated with sequencial numbers. So no coding needs to be done for
> handling sequences.
>
>
> Question- Is there any way in which I can get the SQL Server type
> sequence behaviour (i.e. I don't explicitly need to handle sequences
> and its done automatically) in Oracle?
>
> Thanks for your time,
> - Manoj

No, this is not possible.

The best you can do is design a before row insert trigger on that table

create or replace trigger emp_bri before insert on emp for each row begin
select empseq.nextval into :new.empid;
end;
/

and you won't need to use your explicit call ever. This particular issue is adressed in the Oracle Application Developers Guide.

Actually, the Oracle implementation is -IMO- better as a sequence is not bound
to a particular table. I have been in situations more than once where this was advantegeous.
One of the worst things you could do is try to force Oracle to behave like Sqlserver. I know most people who have sqlserver experience try to do that. They shouldn't. Why, if you replace a Volkswagen car by an Alfa Romeo, try to let the Alfa Romeo drive like the Volkswagen?

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Mon Jul 09 2001 - 15:30:36 CEST

Original text of this message