1--TEST-- 2Oracle Database 12c Implicit Result Sets: Commit modes 3--EXTENSIONS-- 4oci8 5--SKIPIF-- 6<?php 7require_once 'skipifconnectfailure.inc'; 8$target_dbs = array('oracledb' => true, 'timesten' => false); // test runs on these DBs 9require __DIR__.'/skipif.inc'; 10preg_match('/.*Release ([[:digit:]]+)\.([[:digit:]]+)\.([[:digit:]]+)\.([[:digit:]]+)\.([[:digit:]]+)*/', oci_server_version($c), $matches); 11if (!(isset($matches[0]) && $matches[1] >= 12)) { 12 die("skip expected output only valid when using Oracle Database 12c or greater"); 13} 14preg_match('/^[[:digit:]]+/', oci_client_version(), $matches); 15if (!(isset($matches[0]) && $matches[0] >= 12)) { 16 die("skip works only with Oracle 12c or greater version of Oracle client libraries"); 17} 18?> 19--FILE-- 20<?php 21 22require __DIR__.'/connect.inc'; 23 24// Initialization 25 26$c2 = oci_new_connect($user, $password, $dbase); 27 28$stmtarray = array( 29 "drop table imp_res_insert_tab", 30 "create table imp_res_insert_tab (c1 number)", 31 32 "create or replace procedure imp_res_insert_proc_nc (p1 in number) as 33 c1 sys_refcursor; 34 begin 35 execute immediate 'insert into imp_res_insert_tab values ('||p1||')'; 36 open c1 for select * from imp_res_insert_tab order by 1; 37 dbms_sql.return_result(c1); 38 end;", 39 40 "create or replace procedure imp_res_insert_proc_c (p1 in number) as 41 c1 sys_refcursor; 42 begin 43 execute immediate 'insert into imp_res_insert_tab values ('||p1||')'; 44 commit; 45 open c1 for select * from imp_res_insert_tab order by 1; 46 dbms_sql.return_result(c1); 47 end;" 48 49); 50 51oci8_test_sql_execute($c, $stmtarray); 52 53// Run Test 54 55echo "Test 1 - No commit in procedure, OCI_COMMIT_ON_SUCCESS mode\n"; 56$s = oci_parse($c, "begin imp_res_insert_proc_nc(111); end;"); 57oci_execute($s, OCI_COMMIT_ON_SUCCESS); 58while (($row = oci_fetch_row($s)) !== false) 59 echo $row[0], "\n"; 60$s2 = oci_parse($c2, "select * from imp_res_insert_tab order by 1"); 61oci_execute($s2, OCI_NO_AUTO_COMMIT); 62oci_fetch_all($s2, $res); 63var_dump($res['C1']); 64 65echo "\nTest 2 - No commit in procedure, OCI_NO_AUTO_COMMIT mode\n"; 66$s = oci_parse($c, "begin imp_res_insert_proc_nc(222); end;"); 67oci_execute($s, OCI_NO_AUTO_COMMIT); 68while (($row = oci_fetch_row($s)) !== false) 69 echo $row[0], "\n"; 70// The 2nd connection won't see the newly inserted data 71$s2 = oci_parse($c2, "select * from imp_res_insert_tab order by 1"); 72oci_execute($s2, OCI_NO_AUTO_COMMIT); 73oci_fetch_all($s2, $res); 74var_dump($res['C1']); 75 76echo "\nTest 3 - Commit in procedure, OCI_COMMIT_ON_SUCCESS mode\n"; 77$s = oci_parse($c, "begin imp_res_insert_proc_c(333); end;"); 78oci_execute($s, OCI_COMMIT_ON_SUCCESS); 79// The 2nd connection will now see the previously uncommitted data inserted in the previous test 80while (($row = oci_fetch_row($s)) !== false) 81 echo $row[0], "\n"; 82$s2 = oci_parse($c2, "select * from imp_res_insert_tab order by 1"); 83oci_execute($s2, OCI_NO_AUTO_COMMIT); 84oci_fetch_all($s2, $res); 85var_dump($res['C1']); 86 87echo "\nTest 4 - Commit in procedure, OCI_NO_AUTO_COMMIT mode\n"; 88$s = oci_parse($c, "begin imp_res_insert_proc_c(444); end;"); 89oci_execute($s, OCI_NO_AUTO_COMMIT); 90while (($row = oci_fetch_row($s)) !== false) 91 echo $row[0], "\n"; 92$s2 = oci_parse($c2, "select * from imp_res_insert_tab order by 1"); 93oci_execute($s2, OCI_NO_AUTO_COMMIT); 94oci_fetch_all($s2, $res); 95var_dump($res['C1']); 96 97// Clean up 98 99$stmtarray = array( 100 "drop procedure imp_res_insert_proc_nc", 101 "drop procedure imp_res_insert_proc_c", 102 "drop table imp_res_insert_tab", 103); 104 105oci8_test_sql_execute($c, $stmtarray); 106 107?> 108--EXPECT-- 109Test 1 - No commit in procedure, OCI_COMMIT_ON_SUCCESS mode 110111 111array(1) { 112 [0]=> 113 string(3) "111" 114} 115 116Test 2 - No commit in procedure, OCI_NO_AUTO_COMMIT mode 117111 118222 119array(1) { 120 [0]=> 121 string(3) "111" 122} 123 124Test 3 - Commit in procedure, OCI_COMMIT_ON_SUCCESS mode 125111 126222 127333 128array(3) { 129 [0]=> 130 string(3) "111" 131 [1]=> 132 string(3) "222" 133 [2]=> 134 string(3) "333" 135} 136 137Test 4 - Commit in procedure, OCI_NO_AUTO_COMMIT mode 138111 139222 140333 141444 142array(4) { 143 [0]=> 144 string(3) "111" 145 [1]=> 146 string(3) "222" 147 [2]=> 148 string(3) "333" 149 [3]=> 150 string(3) "444" 151} 152
