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