xref: /PHP-7.3/ext/oci8/tests/imp_res_1.phpt (revision a2c0f872)
1--TEST--
2Oracle Database 12c Implicit Result Sets: basic test
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_1_tab_1",
26    "create table imp_res_1_tab_1 (c1 number, c2 varchar2(10))",
27    "insert into imp_res_1_tab_1 values (1, 'abcde')",
28    "insert into imp_res_1_tab_1 values (2, 'fghij')",
29    "insert into imp_res_1_tab_1 values (3, 'klmno')",
30
31    "drop table imp_res_1_tab_2",
32    "create table imp_res_1_tab_2 (c3 varchar2(1))",
33    "insert into imp_res_1_tab_2 values ('t')",
34    "insert into imp_res_1_tab_2 values ('u')",
35    "insert into imp_res_1_tab_2 values ('v')",
36
37    "create or replace procedure imp_res_1_proc as
38      c1 sys_refcursor;
39    begin
40      open c1 for select * from imp_res_1_tab_1 order by 1;
41      dbms_sql.return_result(c1);
42
43      open c1 for select * from imp_res_1_tab_2 where rownum < 3 order by 1;
44      dbms_sql.return_result(c1);
45
46      open c1 for select 99 from dual;
47      dbms_sql.return_result (c1);
48
49      open c1 for select NULL, 'Z' from dual;
50      dbms_sql.return_result (c1);
51
52      open c1 for select * from imp_res_1_tab_1 order by 1;
53      dbms_sql.return_result(c1);
54    end;"
55);
56
57oci8_test_sql_execute($c, $stmtarray);
58
59// Run Test
60
61echo "Test 1 - oci_fetch_assoc\n";
62$s = oci_parse($c, "begin imp_res_1_proc(); end;");
63oci_execute($s);
64while (($row = oci_fetch_assoc($s)) != false)
65    var_dump($row);
66
67echo "\nTest 2 - oci_fetch_object\n";
68$s = oci_parse($c, "begin imp_res_1_proc(); end;");
69oci_execute($s);
70while (($row = oci_fetch_object($s)) != false)
71    var_dump($row);
72
73echo "\nTest 3 - oci_fetch_row\n";
74$s = oci_parse($c, "begin imp_res_1_proc(); end;");
75oci_execute($s);
76while (($row = oci_fetch_row($s)) != false)
77    var_dump($row);
78
79echo "\nTest 4 - oci_fetch_array(OCI_ASSOC+OCI_RETURN_NULLS)\n";
80$s = oci_parse($c, "begin imp_res_1_proc(); end;");
81oci_execute($s);
82while (($row = oci_fetch_array($s, OCI_ASSOC+OCI_RETURN_NULLS)) != false)
83    var_dump($row);
84
85echo "\nTest 5 - oci_fetch_array(OCI_ASSOC)\n";
86$s = oci_parse($c, "begin imp_res_1_proc(); end;");
87oci_execute($s);
88while (($row = oci_fetch_array($s, OCI_ASSOC)) != false)
89    var_dump($row);
90
91echo "\nTest 6 - oci_fetch_array(OCI_NUM)\n";
92$s = oci_parse($c, "begin imp_res_1_proc(); end;");
93oci_execute($s);
94while (($row = oci_fetch_array($s, OCI_NUM)) != false)
95    var_dump($row);
96
97echo "\nTest 7 - oci_fetch_array(OCI_BOTH)\n";
98$s = oci_parse($c, "begin imp_res_1_proc(); end;");
99oci_execute($s);
100while (($row = oci_fetch_array($s, OCI_BOTH)) != false)
101    var_dump($row);
102
103echo "\nTest 8 - oci_fetch_array(OCI_BOTH+OCI_RETURN_NULLS)\n";
104$s = oci_parse($c, "begin imp_res_1_proc(); end;");
105oci_execute($s);
106while (($row = oci_fetch_array($s, OCI_BOTH+OCI_RETURN_NULLS)) != false)
107    var_dump($row);
108
109// Clean up
110
111$stmtarray = array(
112    "drop procedure imp_res_1_proc",
113    "drop table imp_res_1_tab_1",
114    "drop table imp_res_1_tab_2"
115);
116
117oci8_test_sql_execute($c, $stmtarray);
118
119?>
120===DONE===
121<?php exit(0); ?>
122--EXPECTF--
123Test 1 - oci_fetch_assoc
124array(2) {
125  ["C1"]=>
126  string(1) "1"
127  ["C2"]=>
128  string(5) "abcde"
129}
130array(2) {
131  ["C1"]=>
132  string(1) "2"
133  ["C2"]=>
134  string(5) "fghij"
135}
136array(2) {
137  ["C1"]=>
138  string(1) "3"
139  ["C2"]=>
140  string(5) "klmno"
141}
142array(1) {
143  ["C3"]=>
144  string(1) "t"
145}
146array(1) {
147  ["C3"]=>
148  string(1) "u"
149}
150array(1) {
151  [99]=>
152  string(2) "99"
153}
154array(2) {
155  ["NULL"]=>
156  NULL
157  ["'Z'"]=>
158  string(1) "Z"
159}
160array(2) {
161  ["C1"]=>
162  string(1) "1"
163  ["C2"]=>
164  string(5) "abcde"
165}
166array(2) {
167  ["C1"]=>
168  string(1) "2"
169  ["C2"]=>
170  string(5) "fghij"
171}
172array(2) {
173  ["C1"]=>
174  string(1) "3"
175  ["C2"]=>
176  string(5) "klmno"
177}
178
179Test 2 - oci_fetch_object
180object(stdClass)#%d (2) {
181  ["C1"]=>
182  string(1) "1"
183  ["C2"]=>
184  string(5) "abcde"
185}
186object(stdClass)#%d (2) {
187  ["C1"]=>
188  string(1) "2"
189  ["C2"]=>
190  string(5) "fghij"
191}
192object(stdClass)#%d (2) {
193  ["C1"]=>
194  string(1) "3"
195  ["C2"]=>
196  string(5) "klmno"
197}
198object(stdClass)#%d (1) {
199  ["C3"]=>
200  string(1) "t"
201}
202object(stdClass)#%d (1) {
203  ["C3"]=>
204  string(1) "u"
205}
206object(stdClass)#%d (1) {
207  [99]=>
208  string(2) "99"
209}
210object(stdClass)#%d (2) {
211  ["NULL"]=>
212  NULL
213  ["'Z'"]=>
214  string(1) "Z"
215}
216object(stdClass)#%d (2) {
217  ["C1"]=>
218  string(1) "1"
219  ["C2"]=>
220  string(5) "abcde"
221}
222object(stdClass)#%d (2) {
223  ["C1"]=>
224  string(1) "2"
225  ["C2"]=>
226  string(5) "fghij"
227}
228object(stdClass)#%d (2) {
229  ["C1"]=>
230  string(1) "3"
231  ["C2"]=>
232  string(5) "klmno"
233}
234
235Test 3 - oci_fetch_row
236array(2) {
237  [0]=>
238  string(1) "1"
239  [1]=>
240  string(5) "abcde"
241}
242array(2) {
243  [0]=>
244  string(1) "2"
245  [1]=>
246  string(5) "fghij"
247}
248array(2) {
249  [0]=>
250  string(1) "3"
251  [1]=>
252  string(5) "klmno"
253}
254array(1) {
255  [0]=>
256  string(1) "t"
257}
258array(1) {
259  [0]=>
260  string(1) "u"
261}
262array(1) {
263  [0]=>
264  string(2) "99"
265}
266array(2) {
267  [0]=>
268  NULL
269  [1]=>
270  string(1) "Z"
271}
272array(2) {
273  [0]=>
274  string(1) "1"
275  [1]=>
276  string(5) "abcde"
277}
278array(2) {
279  [0]=>
280  string(1) "2"
281  [1]=>
282  string(5) "fghij"
283}
284array(2) {
285  [0]=>
286  string(1) "3"
287  [1]=>
288  string(5) "klmno"
289}
290
291Test 4 - oci_fetch_array(OCI_ASSOC+OCI_RETURN_NULLS)
292array(2) {
293  ["C1"]=>
294  string(1) "1"
295  ["C2"]=>
296  string(5) "abcde"
297}
298array(2) {
299  ["C1"]=>
300  string(1) "2"
301  ["C2"]=>
302  string(5) "fghij"
303}
304array(2) {
305  ["C1"]=>
306  string(1) "3"
307  ["C2"]=>
308  string(5) "klmno"
309}
310array(1) {
311  ["C3"]=>
312  string(1) "t"
313}
314array(1) {
315  ["C3"]=>
316  string(1) "u"
317}
318array(1) {
319  [99]=>
320  string(2) "99"
321}
322array(2) {
323  ["NULL"]=>
324  NULL
325  ["'Z'"]=>
326  string(1) "Z"
327}
328array(2) {
329  ["C1"]=>
330  string(1) "1"
331  ["C2"]=>
332  string(5) "abcde"
333}
334array(2) {
335  ["C1"]=>
336  string(1) "2"
337  ["C2"]=>
338  string(5) "fghij"
339}
340array(2) {
341  ["C1"]=>
342  string(1) "3"
343  ["C2"]=>
344  string(5) "klmno"
345}
346
347Test 5 - oci_fetch_array(OCI_ASSOC)
348array(2) {
349  ["C1"]=>
350  string(1) "1"
351  ["C2"]=>
352  string(5) "abcde"
353}
354array(2) {
355  ["C1"]=>
356  string(1) "2"
357  ["C2"]=>
358  string(5) "fghij"
359}
360array(2) {
361  ["C1"]=>
362  string(1) "3"
363  ["C2"]=>
364  string(5) "klmno"
365}
366array(1) {
367  ["C3"]=>
368  string(1) "t"
369}
370array(1) {
371  ["C3"]=>
372  string(1) "u"
373}
374array(1) {
375  [99]=>
376  string(2) "99"
377}
378array(1) {
379  ["'Z'"]=>
380  string(1) "Z"
381}
382array(2) {
383  ["C1"]=>
384  string(1) "1"
385  ["C2"]=>
386  string(5) "abcde"
387}
388array(2) {
389  ["C1"]=>
390  string(1) "2"
391  ["C2"]=>
392  string(5) "fghij"
393}
394array(2) {
395  ["C1"]=>
396  string(1) "3"
397  ["C2"]=>
398  string(5) "klmno"
399}
400
401Test 6 - oci_fetch_array(OCI_NUM)
402array(2) {
403  [0]=>
404  string(1) "1"
405  [1]=>
406  string(5) "abcde"
407}
408array(2) {
409  [0]=>
410  string(1) "2"
411  [1]=>
412  string(5) "fghij"
413}
414array(2) {
415  [0]=>
416  string(1) "3"
417  [1]=>
418  string(5) "klmno"
419}
420array(1) {
421  [0]=>
422  string(1) "t"
423}
424array(1) {
425  [0]=>
426  string(1) "u"
427}
428array(1) {
429  [0]=>
430  string(2) "99"
431}
432array(1) {
433  [1]=>
434  string(1) "Z"
435}
436array(2) {
437  [0]=>
438  string(1) "1"
439  [1]=>
440  string(5) "abcde"
441}
442array(2) {
443  [0]=>
444  string(1) "2"
445  [1]=>
446  string(5) "fghij"
447}
448array(2) {
449  [0]=>
450  string(1) "3"
451  [1]=>
452  string(5) "klmno"
453}
454
455Test 7 - oci_fetch_array(OCI_BOTH)
456array(4) {
457  [0]=>
458  string(1) "1"
459  ["C1"]=>
460  string(1) "1"
461  [1]=>
462  string(5) "abcde"
463  ["C2"]=>
464  string(5) "abcde"
465}
466array(4) {
467  [0]=>
468  string(1) "2"
469  ["C1"]=>
470  string(1) "2"
471  [1]=>
472  string(5) "fghij"
473  ["C2"]=>
474  string(5) "fghij"
475}
476array(4) {
477  [0]=>
478  string(1) "3"
479  ["C1"]=>
480  string(1) "3"
481  [1]=>
482  string(5) "klmno"
483  ["C2"]=>
484  string(5) "klmno"
485}
486array(2) {
487  [0]=>
488  string(1) "t"
489  ["C3"]=>
490  string(1) "t"
491}
492array(2) {
493  [0]=>
494  string(1) "u"
495  ["C3"]=>
496  string(1) "u"
497}
498array(2) {
499  [0]=>
500  string(2) "99"
501  [99]=>
502  string(2) "99"
503}
504array(2) {
505  [1]=>
506  string(1) "Z"
507  ["'Z'"]=>
508  string(1) "Z"
509}
510array(4) {
511  [0]=>
512  string(1) "1"
513  ["C1"]=>
514  string(1) "1"
515  [1]=>
516  string(5) "abcde"
517  ["C2"]=>
518  string(5) "abcde"
519}
520array(4) {
521  [0]=>
522  string(1) "2"
523  ["C1"]=>
524  string(1) "2"
525  [1]=>
526  string(5) "fghij"
527  ["C2"]=>
528  string(5) "fghij"
529}
530array(4) {
531  [0]=>
532  string(1) "3"
533  ["C1"]=>
534  string(1) "3"
535  [1]=>
536  string(5) "klmno"
537  ["C2"]=>
538  string(5) "klmno"
539}
540
541Test 8 - oci_fetch_array(OCI_BOTH+OCI_RETURN_NULLS)
542array(4) {
543  [0]=>
544  string(1) "1"
545  ["C1"]=>
546  string(1) "1"
547  [1]=>
548  string(5) "abcde"
549  ["C2"]=>
550  string(5) "abcde"
551}
552array(4) {
553  [0]=>
554  string(1) "2"
555  ["C1"]=>
556  string(1) "2"
557  [1]=>
558  string(5) "fghij"
559  ["C2"]=>
560  string(5) "fghij"
561}
562array(4) {
563  [0]=>
564  string(1) "3"
565  ["C1"]=>
566  string(1) "3"
567  [1]=>
568  string(5) "klmno"
569  ["C2"]=>
570  string(5) "klmno"
571}
572array(2) {
573  [0]=>
574  string(1) "t"
575  ["C3"]=>
576  string(1) "t"
577}
578array(2) {
579  [0]=>
580  string(1) "u"
581  ["C3"]=>
582  string(1) "u"
583}
584array(2) {
585  [0]=>
586  string(2) "99"
587  [99]=>
588  string(2) "99"
589}
590array(4) {
591  [0]=>
592  NULL
593  ["NULL"]=>
594  NULL
595  [1]=>
596  string(1) "Z"
597  ["'Z'"]=>
598  string(1) "Z"
599}
600array(4) {
601  [0]=>
602  string(1) "1"
603  ["C1"]=>
604  string(1) "1"
605  [1]=>
606  string(5) "abcde"
607  ["C2"]=>
608  string(5) "abcde"
609}
610array(4) {
611  [0]=>
612  string(1) "2"
613  ["C1"]=>
614  string(1) "2"
615  [1]=>
616  string(5) "fghij"
617  ["C2"]=>
618  string(5) "fghij"
619}
620array(4) {
621  [0]=>
622  string(1) "3"
623  ["C1"]=>
624  string(1) "3"
625  [1]=>
626  string(5) "klmno"
627  ["C2"]=>
628  string(5) "klmno"
629}
630===DONE===
631