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


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);

SarniaitAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

snoyes_jwCommented:
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');

http://joseph.randomnetworks.com/archives/2006/03/23/sequence-vs-auto_incrementidentity/
http://jen.fluxcapacitor.net/geek/autoincr.html

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gamebitsCommented:
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.

CREATE SEQUENCE book_seq
/
SELECT book_seq.CURRVAL FROM DUAL  -- shows the current value
/
SELECT book_seq.NEXTVAL FROM DUAL  -- displays the next value
/
INSERT INTO books VALUES
(
 'Oliver Twist',
 'Charles Dickens',
 book_seq.NEXTVAL,
 '12-SEP-1839'
)
/
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

Gamebits
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.