When will automatic rollbacks be executed in PHP executing Oracle PL/SQL - OCI8?

I have PHP code that execute a stored procedure 10 times. If one stored procedure call fails, it sh开发者_高级运维ould continue on, and at the end commit the transaction.

It basically looks like this:

$connection = getConn();

foreach($row as $i=>$j) {
  $SQL = "BEGIN MYPROC.EXECUTE(:VAL1, :VAL2); END;";
  $statement = OCIParse($connection, $SQL);

  oci_bind_by_name($statement, 'VAL1', $row[i]['FIRSTVAL']);
  oci_bind_by_name($statement, 'VAL2', $row[i]['SECONDVAL']);

  $success = @OCIExecute($statement, OCI_DEFAULT);
  if(!$success) {
    print 'Exception in stored proc call';
  }
  else {
    print 'Success';
  }

}
oci_commit($connection);

My question is, if there is an exception raised in, say, the 5th stored proc call, will that roll back all the stored proc calls up to that point?

As long as each procedure is executed in the same session, and none of them issue a commit, then the changes they make can be rolled back. You should open the connection outside the loop, then do all your work within that. As it stands now, you're connecting each time through the loop, which is inefficient and won't allow what you want to do. You should also take the commit statement outside the loop.

Something like this, perhaps:

$SQL = "BEGIN MYPROC.EXECUTE(:VAL1, :VAL2); END;";
$connection = getConn();
$statement = OCIParse($connection, $SQL);

foreach($row as $i=>$j) {

  oci_bind_by_name($statement, 'VAL1', $row[i]['FIRSTVAL']);
  oci_bind_by_name($statement, 'VAL2', $row[i]['SECONDVAL']);

  $success = @OCIExecute($statement, OCI_DEFAULT);
  if(!$success) {
    print 'Exception in stored proc call';
    oci_rollback($connection);
    exit processing here... 
  }
  else {
    print 'Success';
  }
}
oci_commit($connection);

I think the PHP driver, and not Oracle, is controlling the commit here. This seems to indicate that as of PHP 5.3.2 (PECL OCI8 1.4), each invocation of the OCIExecute (by default) will commit the statement, regardless of what is in the stored procedure.

Had to do some testing on this recently. When an unhandled exception occurs it seems Oracle does a partial rollback up to the point of the topmost containing begin block or commit for the same session (not always all the way back to the prior commit). Given a table with int id and varchar2 val and proc:

CREATE OR REPLACE PROCEDURE PROC_AUTO_COMMIT_TEST( 
   p_id int, p_val varchar2, p_cmd varchar2
) IS
BEGIN
   if (p_cmd = 'init') then
        delete from TEMP_AUTOCOMMIT_TEST;
        insert into TEMP_AUTOCOMMIT_TEST values(1,'one');
        insert into TEMP_AUTOCOMMIT_TEST values(2,'two');
        insert into TEMP_AUTOCOMMIT_TEST values(3,'three');
        commit;  
   else   
        update TEMP_AUTOCOMMIT_TEST 
           set val = p_val
         where id = p_id;

         if (p_cmd = 'throw') then
            insert into TEMP_AUTOCOMMIT_TEST values(3,'THREE');  -- throws
         end if;
   end if;     
END PROC_AUTO_COMMIT_TEST;

Then executing this:

begin
    PROC_AUTO_COMMIT_TEST(0, null, 'init');   
    begin
        PROC_AUTO_COMMIT_TEST(1, 'ONE', null);
    end;
    begin
        PROC_AUTO_COMMIT_TEST(2, 'TWO', null);
        PROC_AUTO_COMMIT_TEST(3, 'THREE', 'throw');
    end;
end;

rollsback all the way back to the commit within the 'init' (ONE rolled back as well).

Versus excecuting these in order (from either Toad (autocommit off, F9 on each block, f5 for whole thing) or Sqlplus with /'s in between):

begin
    PROC_AUTO_COMMIT_TEST(0, null, 'init');   
end;

begin
    PROC_AUTO_COMMIT_TEST(1, 'ONE', null);
end;

begin
    PROC_AUTO_COMMIT_TEST(2, 'TWO', null);
    PROC_AUTO_COMMIT_TEST(3, 'THREE', 'throw');
end;

The exception ocurring within THREE then rolls back to just after the 'ONE'. However the 'ONE' still needs to either be rolled back or committed since it is holding a row lock (verified with Session Browser in TOAD). Calling this a partial rollback because it doesn't go all the way back to the commit within the 'init' call and leaves a row locked. I am assuming this case is closer to what PHP might be doing and other connectors.