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(__DIR__."/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(__DIR__."/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);
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// Various invalid values for prefetch
75$pref = array(-12345,-1);
76foreach($pref as $value) {
77    try {
78        echo "-----------------------------------------------\n";
79        echo "Test with Prefetch (invalid) value set to $value \n";
80        echo "-----------------------------------------------\n";
81        $cur1 = oci_new_cursor($c);
82        fetch_frm_php($c,$cur1,$value);
83        fetch_frm_plsql($c,$cur1);
84    } catch(ValueError $e) {
85        echo $e->getMessage(), "\n";
86    }
87}
88
89
90// This function sets the prefetch count to the given $value and fetches one row .
91
92function fetch_frm_php($c,$cur1,$value) {
93    $sql1 = "begin refcurpkg.open_ref_cur(:cur1); end;";
94    $s1 = oci_parse($c,$sql1);
95    if (!oci_bind_by_name($s1,":cur1",$cur1,-1,SQLT_RSET)) {
96        die("oci_bind_by_name(sql1) failed!\n");
97    }
98    oci_execute($s1);
99    oci_set_prefetch($cur1,$value);
100    oci_execute($cur1);
101    echo "Fetch Row from PHP\n";
102    var_dump(oci_fetch_row($cur1));
103}
104
105// This function calls the fetch_ref_cur procedure to get the values from the REF cur.
106
107function fetch_frm_plsql($c,$cur1) {
108    $sql2 = "begin refcurpkg.fetch_ref_cur(:curs1,:c1,:c2); end;";
109    $s2 = oci_parse($c,$sql2);
110    if (!oci_bind_by_name($s2,":curs1",$cur1,-1,SQLT_RSET)) {
111        die("oci_bind_by_name(sql2) failed!\n");
112    }
113    if (!oci_bind_by_name($s2,":c1",$c1,-1,SQLT_INT)) {
114        die("oci_bind_by_name(sql2) failed!\n");
115    }
116    if (!oci_bind_by_name($s2,":c2",$c2,20,SQLT_CHR)) {
117        die("oci_bind_by_name(sql2) failed!\n");
118    }
119    oci_execute($s2);
120    echo "Fetch Row from PL/SQL\n";
121    var_dump($c1);
122    var_dump($c2);
123}
124
125// Clean up  here
126
127$stmtarray = array(
128    "drop package refcurpkg",
129    "drop table refcurtest"
130);
131
132oci8_test_sql_execute($c, $stmtarray);
133
134echo "Done\n";
135?>
136--EXPECTF--
137-----------------------------------------------
138Test with Prefetch value set to 0
139-----------------------------------------------
140Fetch Row from PHP
141array(2) {
142  [0]=>
143  string(%d) "0"
144  [1]=>
145  string(%d) "test0"
146}
147Fetch Row from PL/SQL
148int(1)
149string(%d) "test1"
150-----------------------------------------------
151Test with Prefetch value set to 1
152-----------------------------------------------
153Fetch Row from PHP
154array(2) {
155  [0]=>
156  string(%d) "0"
157  [1]=>
158  string(%d) "test0"
159}
160Fetch Row from PL/SQL
161int(2)
162string(%d) "test2"
163-----------------------------------------------
164Test with Prefetch value set to 501
165-----------------------------------------------
166Fetch Row from PHP
167array(2) {
168  [0]=>
169  string(%d) "0"
170  [1]=>
171  string(%d) "test0"
172}
173
174Warning: oci_execute(): ORA-01002: %s
175ORA-06512: at "%s.REFCURPKG", line %d
176ORA-06512: at line %d in %s on line %d
177Fetch Row from PL/SQL
178int(0)
179NULL
180-----------------------------------------------
181Test with Prefetch value set to 499
182-----------------------------------------------
183Fetch Row from PHP
184array(2) {
185  [0]=>
186  string(%d) "0"
187  [1]=>
188  string(%d) "test0"
189}
190Fetch Row from PL/SQL
191int(500)
192string(%d) "test500"
193-----------------------------------------------
194Test with Prefetch value set to 250
195-----------------------------------------------
196Fetch Row from PHP
197array(2) {
198  [0]=>
199  string(%d) "0"
200  [1]=>
201  string(%d) "test0"
202}
203Fetch Row from PL/SQL
204int(251)
205string(%d) "test251"
206-----------------------------------------------
207Test with Prefetch value set to 12345
208-----------------------------------------------
209Fetch Row from PHP
210array(2) {
211  [0]=>
212  string(%d) "0"
213  [1]=>
214  string(%d) "test0"
215}
216
217Warning: oci_execute(): ORA-01002: %s
218ORA-06512: at "%s.REFCURPKG", line %d
219ORA-06512: at line %d in %s on line %d
220Fetch Row from PL/SQL
221int(0)
222NULL
223-----------------------------------------------
224Test with Prefetch (invalid) value set to -12345
225-----------------------------------------------
226oci_set_prefetch(): Argument #2 ($rows) must be greater than or equal to 0
227-----------------------------------------------
228Test with Prefetch (invalid) value set to -1
229-----------------------------------------------
230oci_set_prefetch(): Argument #2 ($rows) must be greater than or equal to 0
231Done
232