Getting the last auto inc. of an insert using PHP & OCI8

Posted on 2006-04-07
Medium Priority
Last Modified: 2013-12-12

In MySQL we have a function (mysql_insert_id) which is used to get the last insert number of the last insert statement. I can't find an equivalent solution for Oracle using OCI8. Assuming that table has an auto increment field, how would you get the autoincredment number of the following query:

$sql = 'INSERT INTO table (field) VALUES ('Bla')
$query = oci_parse($oci_c1, $sql);
oci_execute ($query);

Question by:Sarniait
LVL 33

Accepted Solution

snoyes_jw earned 1000 total points
ID: 16402179
Looks like Oracle doesn't do autoincrement per se, but you can create a sequence , which you access either beforehand
SELECT sequence_name.nextval FROM dual;
INSERT INTO table (id, field) VALUES ($nextval, 'Bla');

or during insert time:
INSERT INTO table (id, field) VALUES (sequence_name.nextval, 'Bla');

LVL 28

Assisted Solution

gamebits earned 1000 total points
ID: 16408428
Sequence numbers.
A SEQUENCE is an Oracle object that generates integers according to a specific pattern. Sequence numbers are commonly utilised to supply auto-generated primary keys. The default behaviour of a SEQUENCE is to increment its current value by one to get the next. You may already have used sequences in MySQL, using the AUTO_INCREMENT attributes. Note however the two differences with Oracle's sequences:
in MySQL, numeric fields using AUTO_INCREMENT need to be declared as such, in Oracle sequences are separate entities
MySQL increments the sequence when required, you do not have to do it explicitly
The following code creates a SEQUENCE that we will then use to insert some more values in the books table.

SELECT book_seq.CURRVAL FROM DUAL  -- shows the current value
SELECT book_seq.NEXTVAL FROM DUAL  -- displays the next value
 'Oliver Twist',
 'Charles Dickens',
Apart from the the Oracle peculiarities we have already discussed, you can re-use most of your knowledge of SQL. You may want for example to experiment with the UPDATE and DELETE statements.

Taken from http://w2.syronex.com/jmr/edu/db/introduction-to-oracle


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month14 days, 10 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question