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

Posted on 2006-04-07
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

    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

    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
     '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


    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    The viewer will learn how to count occurrences of each item in an array.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now