xref: /PHP-8.3/ext/oci8/tests/bug40415.phpt (revision a53e5617) 
1--TEST--
2Bug #40415 (Using oci_fetchall with nested cursors)
3--EXTENSIONS--
4oci8
5--SKIPIF--
6<?php
7require_once 'skipifconnectfailure.inc';
8$target_dbs = array('oracledb' => true, 'timesten' => false);  // test runs on these DBs
9require __DIR__.'/skipif.inc';
10?>
11--FILE--
12<?php
13
14require __DIR__."/connect.inc";
15
16// Setup
17
18$create_1 = "CREATE TABLE t1 (id1 INTEGER)";
19$create_2 = "CREATE TABLE t2 (id2 INTEGER)";
20$drop_1 = "DROP TABLE t1";
21$drop_2 = "DROP TABLE t2";
22
23$s1 = oci_parse($c, $drop_1);
24$s2 = oci_parse($c, $drop_2);
25@oci_execute($s1);
26@oci_execute($s2);
27
28$s1 = oci_parse($c, $create_1);
29$s2 = oci_parse($c, $create_2);
30oci_execute($s1);
31oci_execute($s2);
32
33for($i=1; $i < 4; $i++) {
34    $insert = "INSERT INTO t1 VALUES(1".$i.")";
35    $s = oci_parse($c, $insert);
36    oci_execute($s);
37}
38
39for($i=1; $i < 4; $i++) {
40    $insert = "INSERT INTO t2 VALUES(2".$i.")";
41    $s = oci_parse($c, $insert);
42    oci_execute($s);
43}
44
45
46function do_assoc($c)
47{
48    $query = "SELECT t1.*, CURSOR( SELECT * FROM t2 ) AS CURSOR FROM t1";
49
50    $stmt = oci_parse($c, $query);
51    oci_execute($stmt);
52
53    while ($row = oci_fetch_assoc($stmt)) {
54        print "Got row \"".$row['ID1']."\". Now getting nested cursor:\n";
55        var_dump(oci_execute($row['CURSOR']));
56        while ($row_n = oci_fetch_assoc($row['CURSOR']) ) {
57            var_dump($row_n);
58        }
59    }
60}
61
62function do_all($c)
63{
64    $query = "SELECT t1.*, CURSOR( SELECT * FROM t2 ) AS CURSOR FROM t1";
65
66    $stmt = oci_parse($c, $query);
67    oci_execute($stmt);
68
69    $rc1 = oci_fetch_all($stmt, $res);
70
71    echo "Rows returned $rc1\n";
72
73    var_dump($res);
74
75    foreach ($res['CURSOR'] as $cv) {
76        echo "Getting nested cursor\n";
77        var_dump(oci_execute($cv));
78        $rc2 = oci_fetch_all($cv, $res2);
79        var_dump($res2);
80    }
81}
82
83
84
85echo "Test 1: Associate fetch of nested cursor\n";
86do_assoc($c);
87
88echo "\nTest 2: fetchall of nested cursor\n";
89do_all($c);
90
91
92// Cleanup
93$s1 = oci_parse($c, $drop_1);
94$s2 = oci_parse($c, $drop_2);
95@oci_execute($s1);
96@oci_execute($s2);
97
98echo "Done\n";
99?>
100--EXPECTF--
101Test 1: Associate fetch of nested cursor
102Got row "11". Now getting nested cursor:
103bool(true)
104array(1) {
105  ["ID2"]=>
106  string(2) "21"
107}
108array(1) {
109  ["ID2"]=>
110  string(2) "22"
111}
112array(1) {
113  ["ID2"]=>
114  string(2) "23"
115}
116Got row "12". Now getting nested cursor:
117bool(true)
118array(1) {
119  ["ID2"]=>
120  string(2) "21"
121}
122array(1) {
123  ["ID2"]=>
124  string(2) "22"
125}
126array(1) {
127  ["ID2"]=>
128  string(2) "23"
129}
130Got row "13". Now getting nested cursor:
131bool(true)
132array(1) {
133  ["ID2"]=>
134  string(2) "21"
135}
136array(1) {
137  ["ID2"]=>
138  string(2) "22"
139}
140array(1) {
141  ["ID2"]=>
142  string(2) "23"
143}
144
145Test 2: fetchall of nested cursor
146Rows returned 3
147array(2) {
148  ["ID1"]=>
149  array(3) {
150    [0]=>
151    string(2) "11"
152    [1]=>
153    string(2) "12"
154    [2]=>
155    string(2) "13"
156  }
157  ["CURSOR"]=>
158  array(3) {
159    [0]=>
160    resource(%d) of type (oci8 statement)
161    [1]=>
162    resource(%d) of type (oci8 statement)
163    [2]=>
164    resource(%d) of type (oci8 statement)
165  }
166}
167Getting nested cursor
168bool(true)
169array(1) {
170  ["ID2"]=>
171  array(3) {
172    [0]=>
173    string(2) "21"
174    [1]=>
175    string(2) "22"
176    [2]=>
177    string(2) "23"
178  }
179}
180Getting nested cursor
181bool(true)
182array(1) {
183  ["ID2"]=>
184  array(3) {
185    [0]=>
186    string(2) "21"
187    [1]=>
188    string(2) "22"
189    [2]=>
190    string(2) "23"
191  }
192}
193Getting nested cursor
194bool(true)
195array(1) {
196  ["ID2"]=>
197  array(3) {
198    [0]=>
199    string(2) "21"
200    [1]=>
201    string(2) "22"
202    [2]=>
203    string(2) "23"
204  }
205}
206Done
207