Re: Handling of Sequences in Oracle
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