Annotation of embedaddon/php/ext/oci8/tests/refcur_prefetch_1.phpt, revision 1.1.1.1
1.1 misho 1: --TEST--
2: Prefetch 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");
5: if (!extension_loaded('oci8')) die("skip no oci8 extension");
6: require(dirname(__FILE__)."/connect.inc");
7: if (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
15: require(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:
39: oci8_test_sql_execute($c, $stmtarray);
40:
41: // Insert 500 rows into the table.
42: $insert_sql = "INSERT INTO refcurtest (c1, c2) VALUES (:c1,:c2)";
43: if (!($s = oci_parse($c, $insert_sql))) {
44: die("oci_parse(insert) failed!\n");
45: }
46:
47: for ($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);
58: foreach($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:
70: function 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:
85: function 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:
110: oci8_test_sql_execute($c, $stmtarray);
111:
112: echo "Done\n";
113: ?>
114: --EXPECTF--
115: -----------------------------------------------
116: Test with Prefetch value set to 0
117: -----------------------------------------------
118: Fetch Row from PHP
119: array(2) {
120: [0]=>
121: string(%d) "0"
122: [1]=>
123: string(%d) "test0"
124: }
125: Fetch Row from PL/SQL
126: int(1)
127: string(%d) "test1"
128: -----------------------------------------------
129: Test with Prefetch value set to 1
130: -----------------------------------------------
131: Fetch Row from PHP
132: array(2) {
133: [0]=>
134: string(%d) "0"
135: [1]=>
136: string(%d) "test0"
137: }
138: Fetch Row from PL/SQL
139: int(2)
140: string(%d) "test2"
141: -----------------------------------------------
142: Test with Prefetch value set to 501
143: -----------------------------------------------
144: Fetch Row from PHP
145: array(2) {
146: [0]=>
147: string(%d) "0"
148: [1]=>
149: string(%d) "test0"
150: }
151:
152: Warning: oci_execute(): ORA-01002: %s
153: ORA-06512: at "%s.REFCURPKG", line %d
154: ORA-06512: at line %d in %s on line %d
155: Fetch Row from PL/SQL
156: int(0)
157: NULL
158: -----------------------------------------------
159: Test with Prefetch value set to 499
160: -----------------------------------------------
161: Fetch Row from PHP
162: array(2) {
163: [0]=>
164: string(%d) "0"
165: [1]=>
166: string(%d) "test0"
167: }
168: Fetch Row from PL/SQL
169: int(500)
170: string(%d) "test500"
171: -----------------------------------------------
172: Test with Prefetch value set to 250
173: -----------------------------------------------
174: Fetch Row from PHP
175: array(2) {
176: [0]=>
177: string(%d) "0"
178: [1]=>
179: string(%d) "test0"
180: }
181: Fetch Row from PL/SQL
182: int(251)
183: string(%d) "test251"
184: -----------------------------------------------
185: Test with Prefetch value set to 12345
186: -----------------------------------------------
187: Fetch Row from PHP
188: array(2) {
189: [0]=>
190: string(%d) "0"
191: [1]=>
192: string(%d) "test0"
193: }
194:
195: Warning: oci_execute(): ORA-01002: %s
196: ORA-06512: at "%s.REFCURPKG", line %d
197: ORA-06512: at line %d in %s on line %d
198: Fetch Row from PL/SQL
199: int(0)
200: NULL
201: -----------------------------------------------
202: Test with Prefetch value set to -12345
203: -----------------------------------------------
204:
205: Warning: oci_set_prefetch(): Number of rows to be prefetched has to be greater than or equal to 0 in %s on line %d
206: Fetch Row from PHP
207: array(2) {
208: [0]=>
209: string(%d) "0"
210: [1]=>
211: string(%d) "test0"
212: }
213: Fetch Row from PL/SQL
214: int(101)
215: string(%d) "test101"
216: -----------------------------------------------
217: Test with Prefetch value set to -1
218: -----------------------------------------------
219:
220: Warning: oci_set_prefetch(): Number of rows to be prefetched has to be greater than or equal to 0 in %s on line %d
221: Fetch Row from PHP
222: array(2) {
223: [0]=>
224: string(%d) "0"
225: [1]=>
226: string(%d) "test0"
227: }
228: Fetch Row from PL/SQL
229: int(101)
230: string(%d) "test101"
231: Done
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to refcur_prefetch_1.phpt CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / php / ext / oci8 / tests