how to get the function last_insert_id()(used widely in mysql) in oracle?

•

What is DaniWeb IT Discussion Community?

You're currently browsing the Oracle section within the Web Development category of DaniWeb, a massive community of 361,551 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,061 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our Oracle advertiser:

Thread Tools Display Modes

Views: 356 | Replies: 1

•

Join Date: Apr 2008

Posts: 1

Reputation: richardzhangss is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

richardzhangss richardzhangss is offline

Offline

Newbie Poster

how to get the function last_insert_id()(used widely in mysql) in oracle?

Apr 30th, 2008

Hi,
Here is my mysql sentence:

Help with Code Tags

(Toggle Plain Text)

select last_insert_id() from pp;

and the table pp is created by:

Help with Code Tags

(Toggle Plain Text)

create table pp(id int not null auto_increment primary key,name varchar(255) not null);

Now I'd like to change the above into sentences that are right in Oracle.
First I create table:

Help with Code Tags

(Toggle Plain Text)

create table pp(id integer not null primary key,name varchar2(255) not null);

then the sequence:

Help with Code Tags

(Toggle Plain Text)

create sequence pp_sequence  start with 1 increment by 1;

and at last the trigger:

Help with Code Tags

(Toggle Plain Text)

CREATE OR REPLACE TRIGGER pp_TRIGGER
BEFORE INSERT
ON pp
REFERENCING NEW AS NEW
FOR EACH ROW 
BEGIN
SELECT pp_sequence.NEXTVAL INTO :NEW.id FROM DUAL;
END;
/

And what else should I do to change the sentence 'select last_insert_id() from pp;' to be used in Oracle?
I'm a newbie ,
Any help will be greatly appreciated!

•

Join Date: Nov 2004

Location: Netherlands

Posts: 5,576

Reputation: jwenting is a jewel in the rough

Rep Power: 18

Solved Threads: 184

jwenting

Offline

duckman

Re: how to get the function last_insert_id()(used widely in mysql) in oracle?

Apr 30th, 2008

why would you ever need to know the previous value emitted by a sequence?
It's unlikely to remain the same for long enough to be of any use, especially in databases that see a lot of activity.

But if you insist, you can find the answer on page 3-3 of the Oracle 11g SQL language reference.

42 Private messages asking for help will be ignored
In the frozen land of Nador they were forced to eat Steve's iMinstrels, and there was much rejoicing.