1--TEST--
2Prefetch with REF cursor. Test different values for prefetch with oci_set_prefetch().
3--EXTENSIONS--
4oci8
5--SKIPIF--
6<?php
7require_once 'skipifconnectfailure.inc';
8require __DIR__.'/connect.inc';
9preg_match('/.*Release ([[:digit:]]+)\.([[:digit:]]+)\.([[:digit:]]+)\.([[:digit:]]+)\.([[:digit:]]+)*/', oci_server_version($c), $matches);
10if (!(isset($matches[0]) &&
11      ($matches[1] >= 10))) {
12        die("skip expected output only valid when using Oracle 10g or greater database server");
13}
14preg_match('/^([[:digit:]]+)\.([[:digit:]]+)\.([[:digit:]]+)\.([[:digit:]]+)\.([[:digit:]]+)/', oci_client_version(), $matches);
15if (!(isset($matches[0]) &&
16    (($matches[1] == 11 && $matches[2] >= 2) ||
17     ($matches[1] >= 12)
18     ))) {
19    die("skip test expected to work only with Oracle 11gR2 or greater version of client");
20}
21?>
22--FILE--
23<?php
24require __DIR__.'/connect.inc';
25
26// Creates the necessary package and tables.
27$stmtarray = array(
28       "DROP TABLE refcurtest",
29       "CREATE TABLE refcurtest (c1 NUMBER, c2 VARCHAR(20))",
30           "CREATE or REPLACE PACKAGE refcurpkg is
31           type refcursortype is ref cursor;
32           procedure open_ref_cur(cur1 out refcursortype);
33           procedure fetch_ref_cur(cur1 in refcursortype, c1 out number,c2 out varchar2);
34           end refcurpkg;",
35          "CREATE or REPLACE PACKAGE body refcurpkg is
36        procedure open_ref_cur(cur1 out refcursortype) is
37              begin
38            open cur1 for select * from refcurtest order by c1;
39          end open_ref_cur;
40         procedure fetch_ref_cur(cur1 in refcursortype, c1 out number,
41        c2 out varchar2) is
42          begin
43            fetch cur1 into c1,c2;
44        end fetch_ref_cur;
45         end refcurpkg;"
46    );
47
48oci8_test_sql_execute($c, $stmtarray);
49
50// Insert 500 rows into the table.
51$insert_sql = "INSERT INTO refcurtest (c1, c2) VALUES (:c1,:c2)";
52if (!($s = oci_parse($c, $insert_sql))) {
53    die("oci_parse(insert) failed!\n");
54}
55
56for ($i = 0; $i<=500; $i++) {
57    $val2 = 'test'.$i;
58    oci_bind_by_name($s,':c1',$i);
59    oci_bind_by_name($s,':c2',$val2);
60    if (!oci_execute($s)) {
61        die("oci_execute(insert) failed!\n");
62    }
63}
64
65// Various values for prefetch
66$pref = array(0,1,501,499,250,12345);
67foreach($pref as $value) {
68    echo"-----------------------------------------------\n";
69    echo "Test with Prefetch value set to $value \n";
70    echo"-----------------------------------------------\n";
71    $cur1 = oci_new_cursor($c);
72    fetch_frm_php($c,$cur1,$value);
73    fetch_frm_plsql($c,$cur1);
74}
75
76// Various invalid values for prefetch
77$pref = array(-12345,-1);
78foreach($pref as $value) {
79    try {
80        echo "-----------------------------------------------\n";
81        echo "Test with Prefetch (invalid) value set to $value \n";
82        echo "-----------------------------------------------\n";
83        $cur1 = oci_new_cursor($c);
84        fetch_frm_php($c,$cur1,$value);
85        fetch_frm_plsql($c,$cur1);
86    } catch(ValueError $e) {
87        echo $e->getMessage(), "\n";
88    }
89}
90
91
92// This function sets the prefetch count to the given $value and fetches one row .
93
94function fetch_frm_php($c,$cur1,$value) {
95    $sql1 = "begin refcurpkg.open_ref_cur(:cur1); end;";
96    $s1 = oci_parse($c,$sql1);
97    if (!oci_bind_by_name($s1,":cur1",$cur1,-1,SQLT_RSET)) {
98        die("oci_bind_by_name(sql1) failed!\n");
99    }
100    oci_execute($s1);
101    oci_set_prefetch($cur1,$value);
102    oci_execute($cur1);
103    echo "Fetch Row from PHP\n";
104    var_dump(oci_fetch_row($cur1));
105}
106
107// This function calls the fetch_ref_cur procedure to get the values from the REF cur.
108
109function fetch_frm_plsql($c,$cur1) {
110    $sql2 = "begin refcurpkg.fetch_ref_cur(:curs1,:c1,:c2); end;";
111    $s2 = oci_parse($c,$sql2);
112    if (!oci_bind_by_name($s2,":curs1",$cur1,-1,SQLT_RSET)) {
113        die("oci_bind_by_name(sql2) failed!\n");
114    }
115    if (!oci_bind_by_name($s2,":c1",$c1,-1,SQLT_INT)) {
116        die("oci_bind_by_name(sql2) failed!\n");
117    }
118    if (!oci_bind_by_name($s2,":c2",$c2,20,SQLT_CHR)) {
119        die("oci_bind_by_name(sql2) failed!\n");
120    }
121    oci_execute($s2);
122    echo "Fetch Row from PL/SQL\n";
123    var_dump($c1);
124    var_dump($c2);
125}
126
127// Clean up  here
128
129$stmtarray = array(
130    "drop package refcurpkg",
131    "drop table refcurtest"
132);
133
134oci8_test_sql_execute($c, $stmtarray);
135
136echo "Done\n";
137?>
138--EXPECTF--
139-----------------------------------------------
140Test with Prefetch value set to 0
141-----------------------------------------------
142Fetch Row from PHP
143array(2) {
144  [0]=>
145  string(%d) "0"
146  [1]=>
147  string(%d) "test0"
148}
149Fetch Row from PL/SQL
150int(1)
151string(%d) "test1"
152-----------------------------------------------
153Test with Prefetch value set to 1
154-----------------------------------------------
155Fetch Row from PHP
156array(2) {
157  [0]=>
158  string(%d) "0"
159  [1]=>
160  string(%d) "test0"
161}
162Fetch Row from PL/SQL
163int(2)
164string(%d) "test2"
165-----------------------------------------------
166Test with Prefetch value set to 501
167-----------------------------------------------
168Fetch Row from PHP
169array(2) {
170  [0]=>
171  string(%d) "0"
172  [1]=>
173  string(%d) "test0"
174}
175
176Warning: oci_execute(): ORA-01002: %s
177ORA-06512: at "%s.REFCURPKG", line %d
178ORA-06512: at line %d in %s on line %d
179Fetch Row from PL/SQL
180int(0)
181NULL
182-----------------------------------------------
183Test with Prefetch value set to 499
184-----------------------------------------------
185Fetch Row from PHP
186array(2) {
187  [0]=>
188  string(%d) "0"
189  [1]=>
190  string(%d) "test0"
191}
192Fetch Row from PL/SQL
193int(500)
194string(%d) "test500"
195-----------------------------------------------
196Test with Prefetch value set to 250
197-----------------------------------------------
198Fetch Row from PHP
199array(2) {
200  [0]=>
201  string(%d) "0"
202  [1]=>
203  string(%d) "test0"
204}
205Fetch Row from PL/SQL
206int(251)
207string(%d) "test251"
208-----------------------------------------------
209Test with Prefetch value set to 12345
210-----------------------------------------------
211Fetch Row from PHP
212array(2) {
213  [0]=>
214  string(%d) "0"
215  [1]=>
216  string(%d) "test0"
217}
218
219Warning: oci_execute(): ORA-01002: %s
220ORA-06512: at "%s.REFCURPKG", line %d
221ORA-06512: at line %d in %s on line %d
222Fetch Row from PL/SQL
223int(0)
224NULL
225-----------------------------------------------
226Test with Prefetch (invalid) value set to -12345
227-----------------------------------------------
228oci_set_prefetch(): Argument #2 ($rows) must be greater than or equal to 0
229-----------------------------------------------
230Test with Prefetch (invalid) value set to -1
231-----------------------------------------------
232oci_set_prefetch(): Argument #2 ($rows) must be greater than or equal to 0
233Done
234