1--TEST-- 2Oracle Database 12c Implicit Result Sets: field tests 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$stmtarray = array( 25 "drop table imp_res_field_tab_1", 26 "create table imp_res_field_tab_1 (c1_number number, c2_varchar210 varchar2(10))", 27 "insert into imp_res_field_tab_1 values (1111, 'abcde')", 28 29 "drop table imp_res_field_tab_2", 30 "create table imp_res_field_tab_2 (c3_varchar21 varchar2(4))", 31 "insert into imp_res_field_tab_2 values ('tttt')", 32 33 "drop table imp_res_field_tab_3", 34 "create table imp_res_field_tab_3 (c4_number52 number(5,2))", 35 "insert into imp_res_field_tab_3 values (33)", 36 "insert into imp_res_field_tab_3 values (NULL)", 37 38 "create or replace procedure imp_res_field_proc as 39 c1 sys_refcursor; 40 begin 41 open c1 for select * from imp_res_field_tab_1 order by 1; 42 dbms_sql.return_result(c1); 43 44 open c1 for select * from imp_res_field_tab_2 order by 1; 45 dbms_sql.return_result(c1); 46 47 open c1 for select * from imp_res_field_tab_3 order by 1; 48 dbms_sql.return_result(c1); 49 end;" 50); 51 52oci8_test_sql_execute($c, $stmtarray); 53 54function print_fields($s) 55{ 56 echo "num fields : " . oci_num_fields($s) . "\n"; 57 for ($i = 1; $i <= oci_num_fields($s); $i++) { 58 $is_null = oci_field_is_null($s, $i) ? "T" : "F"; 59 $name = oci_field_name($s, $i); 60 $precision = oci_field_precision($s, $i); 61 $scale = oci_field_scale($s, $i); 62 $size = oci_field_size($s, $i); 63 $typeraw = oci_field_type_raw($s, $i); 64 $type = oci_field_type($s, $i); 65 echo "$name\t: is_null $is_null, precision $precision, scale $scale, size $size, typeraw $typeraw, type $type\n"; 66 } 67} 68 69// Run Test 70 71echo "Test 1 - can't get IRS fields from parent\n"; 72$s = oci_parse($c, "begin imp_res_field_proc(); end;"); 73oci_execute($s); 74print_fields($s); 75 76echo "\nTest 2 - can't get IRS fields from parent when fetching\n"; 77$s = oci_parse($c, "begin imp_res_field_proc(); end;"); 78oci_execute($s); 79while (($r = oci_fetch_row($s))) { 80 var_dump($r); 81 print_fields($s); 82} 83 84echo "\nTest 3 - get IRS fields\n"; 85$s = oci_parse($c, "begin imp_res_field_proc(); end;"); 86oci_execute($s); 87while (($s1 = oci_get_implicit_resultset($s))) { 88 print_fields($s1); 89} 90 91echo "\nTest 4 - get IRS fields before fetching rows\n"; 92$s = oci_parse($c, "begin imp_res_field_proc(); end;"); 93oci_execute($s); 94$i = 0; 95while (($s1 = oci_get_implicit_resultset($s))) { 96 echo "===> Result set ".++$i."\n"; 97 print_fields($s1); 98 while (($r = oci_fetch_row($s1)) !== false) { 99 var_dump($r); 100 } 101} 102 103echo "\nTest 5 - get IRS fields when fetching rows\n"; 104$s = oci_parse($c, "begin imp_res_field_proc(); end;"); 105oci_execute($s); 106$i = 0; 107while (($s1 = oci_get_implicit_resultset($s))) { 108 echo "===> Result set ".++$i."\n"; 109 while (($r = oci_fetch_row($s1)) !== false) { 110 var_dump($r); 111 print_fields($s1); 112 } 113} 114 115// Clean up 116 117$stmtarray = array( 118 "drop procedure imp_res_field_proc", 119 "drop table imp_res_field_tab_1", 120 "drop table imp_res_field_tab_2", 121 "drop table imp_res_field_tab_3" 122); 123 124oci8_test_sql_execute($c, $stmtarray); 125 126?> 127===DONE=== 128<?php exit(0); ?> 129--EXPECTF-- 130Test 1 - can't get IRS fields from parent 131num fields : 0 132 133Test 2 - can't get IRS fields from parent when fetching 134array(2) { 135 [0]=> 136 string(4) "1111" 137 [1]=> 138 string(5) "abcde" 139} 140num fields : 0 141array(1) { 142 [0]=> 143 string(4) "tttt" 144} 145num fields : 0 146array(1) { 147 [0]=> 148 string(2) "33" 149} 150num fields : 0 151array(1) { 152 [0]=> 153 NULL 154} 155num fields : 0 156 157Test 3 - get IRS fields 158num fields : 2 159C1_NUMBER : is_null F, precision 0, scale -127, size 22, typeraw 2, type NUMBER 160C2_VARCHAR210 : is_null F, precision 0, scale 0, size 10, typeraw 1, type VARCHAR2 161num fields : 1 162C3_VARCHAR21 : is_null F, precision 0, scale 0, size 4, typeraw 1, type VARCHAR2 163num fields : 1 164C4_NUMBER52 : is_null F, precision 5, scale 2, size 22, typeraw 2, type NUMBER 165 166Test 4 - get IRS fields before fetching rows 167===> Result set 1 168num fields : 2 169C1_NUMBER : is_null F, precision 0, scale -127, size 22, typeraw 2, type NUMBER 170C2_VARCHAR210 : is_null F, precision 0, scale 0, size 10, typeraw 1, type VARCHAR2 171array(2) { 172 [0]=> 173 string(4) "1111" 174 [1]=> 175 string(5) "abcde" 176} 177===> Result set 2 178num fields : 1 179C3_VARCHAR21 : is_null F, precision 0, scale 0, size 4, typeraw 1, type VARCHAR2 180array(1) { 181 [0]=> 182 string(4) "tttt" 183} 184===> Result set 3 185num fields : 1 186C4_NUMBER52 : is_null F, precision 5, scale 2, size 22, typeraw 2, type NUMBER 187array(1) { 188 [0]=> 189 string(2) "33" 190} 191array(1) { 192 [0]=> 193 NULL 194} 195 196Test 5 - get IRS fields when fetching rows 197===> Result set 1 198array(2) { 199 [0]=> 200 string(4) "1111" 201 [1]=> 202 string(5) "abcde" 203} 204num fields : 2 205C1_NUMBER : is_null F, precision 0, scale -127, size 22, typeraw 2, type NUMBER 206C2_VARCHAR210 : is_null F, precision 0, scale 0, size 10, typeraw 1, type VARCHAR2 207===> Result set 2 208array(1) { 209 [0]=> 210 string(4) "tttt" 211} 212num fields : 1 213C3_VARCHAR21 : is_null F, precision 0, scale 0, size 4, typeraw 1, type VARCHAR2 214===> Result set 3 215array(1) { 216 [0]=> 217 string(2) "33" 218} 219num fields : 1 220C4_NUMBER52 : is_null F, precision 5, scale 2, size 22, typeraw 2, type NUMBER 221array(1) { 222 [0]=> 223 NULL 224} 225num fields : 1 226C4_NUMBER52 : is_null T, precision 5, scale 2, size 22, typeraw 2, type NUMBER 227===DONE=== 228
