Member Avatar for richardzhangss

Hi,
Here is my mysql sentence:

select last_insert_id() from pp;

and the table pp is created by:

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:

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

then the sequence:

create sequence pp_sequence  start with 1 increment by 1;

and at last the trigger:

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!

  • 2 Contributors
  • 1 Reply
  • 262 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by jwenting

Dani AI

Generated

Short answer: Oracle doesn’t have a direct LAST_INSERT_ID() function because Oracle uses sequences. With the sequence+trigger approach you already set up, two practical, reliable options give you the “last inserted id” in the same session:

  • Use the sequence’s CURRVAL right after the INSERT (the trigger’s NEXTVAL call makes CURRVAL available for that session).
  • Capture the value immediately with INSERT … RETURNING INTO (this works even if your BEFORE INSERT trigger assigns the id).

These are safer than doing a table scan like SELECT MAX(id) (which races under concurrency). is right to warn that sequences are global objects and advance independently of table rows — rely on session-scoped values, not global table queries.

Example: capture with RETURNING (PL/SQL)

DECLARE
  l_id pp.id%TYPE;
BEGIN
  INSERT INTO pp(name) VALUES('some name')
    RETURNING id INTO l_id;
  -- l_id now holds the generated id (set by your trigger if you leave it)
END;
/

Example: read the sequence CURRVAL after the insert (SQL)

INSERT INTO pp(name) VALUES('another name');

SELECT pp_sequence.CURRVAL FROM DUAL;

Notes and cautions:

  • CURRVAL raises an error if NEXTVAL hasn’t been referenced in the current session yet. Because your trigger calls NEXTVAL, CURRVAL will be defined after the insert from that same session.
  • For multi-row inserts use RETURNING … BULK COLLECT INTO to collect all generated ids.
  • In pooled or framework-managed connections, make sure you read CURRVAL / use RETURNING on the same DB connection that performed the INSERT.

If you want, I can show a JDBC example (getGeneratedKeys vs. RETURNING) or a RETURNING … BULK COLLECT sample for multi-row inserts.

Member Avatar for jwenting

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.