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