xref: /PHP-8.1/ext/oci8/tests/imp_res_field.phpt (revision 72f47c0c)
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