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