1--TEST--
2Prefetch with Nested cursors with INI setting.
3--INI--
4oci8.default_prefetch=5
5--EXTENSIONS--
6oci8
7--SKIPIF--
8<?php require(__DIR__."/connect.inc");
9preg_match('/.*Release ([[:digit:]]+)\.([[:digit:]]+)\.([[:digit:]]+)\.([[:digit:]]+)\.([[:digit:]]+)*/', oci_server_version($c), $matches);
10if (!(isset($matches[0]) &&
11      (($matches[1] == 11 && $matches[2] >= 2) ||
12       ($matches[1] >= 12)
13       ))) {
14        die("skip expected output only valid when using Oracle 11gR2 or greater database server");
15}
16preg_match('/^([[:digit:]]+)\.([[:digit:]]+)\.([[:digit:]]+)\.([[:digit:]]+)\.([[:digit:]]+)/', oci_client_version(), $matches);
17if (!(isset($matches[0]) &&
18    (($matches[1] == 11 && $matches[2] >= 2) ||
19     ($matches[1] >= 12)
20     ))) {
21    die("skip test expected to work only with Oracle 11gR2 or greater version of client");
22}
23?>
24--FILE--
25<?php
26require __DIR__."/connect.inc";
27
28//Create tables here
29$stmtarray = array(
30    "drop table nescurtest",
31    "create table nescurtest(c1 varchar2(10))"
32);
33
34oci8_test_sql_execute($c, $stmtarray);
35
36// Insert 500 rows into the table.
37$insert_sql = "INSERT INTO nescurtest (c1) VALUES (:c1)";
38if (!($s = oci_parse($c, $insert_sql))) {
39    die("oci_parse(insert) failed!\n");
40}
41
42for ($i = 0; $i<=500; $i++) {
43    $val2 = 'test'.$i;
44    oci_bind_by_name($s,':c1',$val2);
45    if (!oci_execute($s)) {
46        die("oci_execute(insert) failed!\n");
47    }
48}
49
50echo"-----------------------------------------------\n";
51echo "Test with Nested Cursors\n";
52echo"-----------------------------------------------\n";
53$cur1 = oci_new_cursor($c);
54$sqlstmt = "select cursor(select * from nescurtest) curs1 from dual";
55$s = oci_parse($c,$sqlstmt);
56oci_execute($s);
57$data = oci_fetch_array($s);
58oci_execute($data['CURS1']);
59
60// Calculate round-trips
61$initial_rt = print_roundtrips($c);
62for ($i = 0;$i<10;$i++) {
63    echo "Fetch Row using Nested cursor Query\n";
64    var_dump(oci_fetch_row($data['CURS1']));
65}
66
67$cnt = (print_roundtrips($c) - $initial_rt);
68echo "Number of roundtrips made with prefetch count 5 for 10 rows is  $cnt\n";
69
70function  print_roundtrips($c) {
71    $sql_stmt = "select value from v\$mystat a,v\$statname c where
72         a.statistic#=c.statistic# and c.name='SQL*Net roundtrips to/from client'";
73    $s = oci_parse($c,$sql_stmt);
74    oci_define_by_name($s,"VALUE",$value);
75    oci_execute($s);
76    oci_fetch($s);
77    return $value;
78}
79
80// Clean up  here
81
82$stmtarray = array(
83    "drop table nescurtest"
84);
85
86oci8_test_sql_execute($c, $stmtarray);
87
88echo "Done\n";
89?>
90--EXPECTF--
91-----------------------------------------------
92Test with Nested Cursors
93-----------------------------------------------
94Fetch Row using Nested cursor Query
95array(1) {
96  [0]=>
97  string(%d) "test0"
98}
99Fetch Row using Nested cursor Query
100array(1) {
101  [0]=>
102  string(%d) "test1"
103}
104Fetch Row using Nested cursor Query
105array(1) {
106  [0]=>
107  string(%d) "test2"
108}
109Fetch Row using Nested cursor Query
110array(1) {
111  [0]=>
112  string(%d) "test3"
113}
114Fetch Row using Nested cursor Query
115array(1) {
116  [0]=>
117  string(%d) "test4"
118}
119Fetch Row using Nested cursor Query
120array(1) {
121  [0]=>
122  string(%d) "test5"
123}
124Fetch Row using Nested cursor Query
125array(1) {
126  [0]=>
127  string(%d) "test6"
128}
129Fetch Row using Nested cursor Query
130array(1) {
131  [0]=>
132  string(%d) "test7"
133}
134Fetch Row using Nested cursor Query
135array(1) {
136  [0]=>
137  string(%d) "test8"
138}
139Fetch Row using Nested cursor Query
140array(1) {
141  [0]=>
142  string(%d) "test9"
143}
144Number of roundtrips made with prefetch count 5 for 10 rows is  3
145Done
146