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