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