1--TEST--
2Oracle Database 12c Implicit Result Sets: oci_get_implicit_resultset: interleaved with DBMS_OUTPUT
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_get_dbmsoutput_tab_1",
27    "create table imp_res_get_dbmsoutput_tab_1 (c1 number, c2 varchar2(10))",
28    "insert into imp_res_get_dbmsoutput_tab_1 values (1, 'abcde')",
29    "insert into imp_res_get_dbmsoutput_tab_1 values (2, 'fghij')",
30    "insert into imp_res_get_dbmsoutput_tab_1 values (3, 'klmno')",
31
32    "drop table imp_res_get_dbmsoutput_tab_2",
33    "create table imp_res_get_dbmsoutput_tab_2 (c3 varchar2(1))",
34    "insert into imp_res_get_dbmsoutput_tab_2 values ('t')",
35    "insert into imp_res_get_dbmsoutput_tab_2 values ('u')",
36    "insert into imp_res_get_dbmsoutput_tab_2 values ('v')",
37
38    "create or replace procedure imp_res_get_dbmsoutput_proc as
39      c1 sys_refcursor;
40    begin
41      dbms_output.put_line('Line 1');
42      open c1 for select * from imp_res_get_dbmsoutput_tab_1 order by 1;
43      dbms_sql.return_result(c1);
44      dbms_output.put_line('Line 2');
45      open c1 for select * from imp_res_get_dbmsoutput_tab_2 order by 1;
46      dbms_sql.return_result(c1);
47      dbms_output.put_line('Line 3');
48      open c1 for select * from dual;
49      dbms_sql.return_result (c1);
50    end;"
51);
52
53oci8_test_sql_execute($c, $stmtarray);
54
55// Turn DBMS_OUTPUT on
56function setserveroutputon($c)
57{
58    $s = oci_parse($c, "begin dbms_output.enable(null); end;");
59    oci_execute($s);
60}
61
62function getdbmsoutput_do($c)
63{
64    $s = oci_parse($c, "begin dbms_output.get_line(:ln, :st); end;");
65    oci_bind_by_name($s, ":ln", $ln, 100);
66    oci_bind_by_name($s, ":st", $st, -1, SQLT_INT);
67    $res = [];
68    while (($succ = oci_execute($s)) && !$st) {
69        $res[] = $ln;  // append each line to the array
70    }
71    return $res;
72}
73
74setserveroutputon($c);
75
76// Run Test
77
78echo "Test 1\n";
79
80$s = oci_parse($c, "begin imp_res_get_dbmsoutput_proc(); end;");
81oci_execute($s);
82
83var_dump(getdbmsoutput_do($c));
84
85while (($s1 = oci_get_implicit_resultset($s))) {
86    while (($row = oci_fetch_array($s1, OCI_ASSOC+OCI_RETURN_NULLS)) != false) {
87        foreach ($row as $item) {
88            echo "  ".$item;
89        }
90        echo "\n";
91    }
92}
93
94echo "Test 2\n";
95
96$s = oci_parse($c, "begin imp_res_get_dbmsoutput_proc(); end;");
97oci_execute($s);
98
99while (($s1 = oci_get_implicit_resultset($s))) {
100    while (($row = oci_fetch_array($s1, OCI_ASSOC+OCI_RETURN_NULLS)) != false) {
101        foreach ($row as $item) {
102            echo "  ".$item;
103        }
104        echo "\n";
105    }
106}
107
108var_dump(getdbmsoutput_do($c));
109
110// Clean up
111
112$stmtarray = array(
113    "drop procedure imp_res_get_dbmsoutput_proc",
114    "drop table imp_res_get_dbmsoutput_tab_1",
115    "drop table imp_res_get_dbmsoutput_tab_2"
116);
117
118oci8_test_sql_execute($c, $stmtarray);
119
120?>
121--EXPECT--
122Test 1
123array(3) {
124  [0]=>
125  string(6) "Line 1"
126  [1]=>
127  string(6) "Line 2"
128  [2]=>
129  string(6) "Line 3"
130}
131  1  abcde
132  2  fghij
133  3  klmno
134  t
135  u
136  v
137  X
138Test 2
139  1  abcde
140  2  fghij
141  3  klmno
142  t
143  u
144  v
145  X
146array(3) {
147  [0]=>
148  string(6) "Line 1"
149  [1]=>
150  string(6) "Line 2"
151  [2]=>
152  string(6) "Line 3"
153}
154