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