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(dirname(__FILE__).'/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(dirname(__FILE__).'/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===DONE=== 107<?php exit(0); ?> 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===DONE=== 153
