2

here i am trying to insert a record as well as retrive last inserted sequence id but didn't get any success can anybody help me , guide me that how oracle works with php ?

$query = 'INSERT INTO hist_news (id,headline,reportedon,reportedby,loc,story,more,helperjson,refjson,createdt,createdby) VALUES(id.nextval, :headline, :reportedon, :reportedby , :loc , :story , :more , :helper , :ref , sysdate , :createdby) return id.nextval';
        $this->stmt = oci_parse($this->oci,$query);
        oci_bind_by_name($this->stmt,':headline',$headline);
        oci_bind_by_name($this->stmt,':reportedon',$reportedon);
        oci_bind_by_name($this->stmt,':reportedby',$reportedby);
        oci_bind_by_name($this->stmt,':loc',$loc);
        oci_bind_by_name($this->stmt,':story',$story);
        oci_bind_by_name($this->stmt,':more',$more);
        oci_bind_by_name($this->stmt,':helper',$helperjson);
        oci_bind_by_name($this->stmt,':ref',$refjson);
        if($re = oci_execute($this->stmt)){
            return $re;
        } else {
            return false;
        }
ketul shah
  • 413
  • 2
  • 7
  • 17

2 Answers2

4

After the insert statement you can execute select id.currval from dual. This should give you the latest value. Do note that this will only work only in the current session after you have fetched the nextval and cannot be used by itself.

Verma
  • 956
  • 6
  • 21
  • it returns 1 always `if(oci_execute($this->stmt)){ $this->stmt = oci_parse($this->oci,'select id.currval from dual'); return oci_execute($this->stmt); }` – ketul shah Jul 28 '13 at 11:12
  • I am not too familiar with PHP but I am guessing you are seeing the status code of the executed statement. You will have to do `select id.currval into :loc_variable from dual` andthen examine the value of loc_variable. – Verma Jul 28 '13 at 11:17
  • thanx @verma i missed fetch statement. – ketul shah Jul 28 '13 at 11:22
  • @Verma if in the mean time another value from the sequence is fetched than current value of sequence will be incremented and it may result in fetching wrong value. – Harshit Jul 28 '13 at 18:14
  • @harshit since currval is session dependent, this may not be an issue. However I would recommend testing thoroughly to ensure the behavior is as expected. – Verma Jul 29 '13 at 01:05
2

add the following to your insert SQL query:

returning id into :id

Example:

$query = 'INSERT INTO hist_news (id,headline,reportedon,reportedby,loc,story,more,helperjson,refjson,createdt,createdby) VALUES(id.nextval, :headline, :reportedon, :reportedby , :loc , :story , :more , :helper , :ref , sysdate , :createdby) returning id into :id';

And bind this to your statement 

oci_bind_by_name($this->stmt,":ID",$id);

Now $id will have the last inserted ID after oci_execute($this->stmt) is executed;

This method works well with OCI8.

kavehmb
  • 9,822
  • 1
  • 19
  • 22
  • 2 notes about ORACLE and PHP. in @kavehmb answer, some people might not note but the field is `id` but he uses `:ID` on the bind method - for me, Oracle is converting everything to uppercase so pay attention to that. Also, for other type of fields, it seems that the Oracle driver needs to know how many chars you want to get from the returning variable. I got this working by adding 32 as the last parameter for the bind: `oci_bind_by_name($stmt, ':MYFIELD', $myField, 32);`. Hope it helps someone! – Mário Rodrigues Mar 15 '21 at 09:02