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?
 
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
0
 
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.