Annotation of embedaddon/php/ext/oci8/tests/refcur_prefetch_2.phpt, revision 1.1.1.1
1.1 misho 1: --TEST--
2: Prefetch with REF cursor. Test No 2
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: // Steps to Fetch from PHP . For every sub-test,the cursor is bound and then executed.
57:
58: $sql1 = "begin refcurpkg.open_ref_cur(:cur1); end;";
59: $s1 = oci_parse($c,$sql1);
60: $cur1 = oci_new_cursor($c);
61: if (!oci_bind_by_name($s1,":cur1",$cur1,-1,SQLT_RSET)) {
62: die("oci_bind_by_name(sql1) failed!\n");
63: }
64:
65:
66: // Steps to Fetch from PL/SQL . For every sub-test,the cursor is bound and then executed.
67:
68: $sql2 = "begin refcurpkg.fetch_ref_cur(:curs1,:c1,:c2); end;";
69: $s2 = oci_parse($c,$sql2);
70: if (!oci_bind_by_name($s2, ":curs1", $cur1, -1, SQLT_RSET)) {
71: die("oci_bind_by_name(sql2) failed!\n");
72: }
73: if (!oci_bind_by_name($s2, ":c1", $c1, -1, SQLT_INT)) {
74: die("oci_bind_by_name(sql2) failed!\n");
75: }
76: if (!oci_bind_by_name($s2, ":c2", $c2, 20, SQLT_CHR)) {
77: die("oci_bind_by_name(sql2) failed!\n");
78: }
79:
80:
81: echo "------Test 1- Check Roundtrips with prefetch 0 and 5 -----------\n";
82: oci_execute($s1);
83: oci_execute($cur1);
84: $initial_rt = print_roundtrips($c);
85: oci_set_prefetch($cur1,0);
86: for ($i = 0;$i<5;$i++) {
87: var_dump(oci_fetch_row($cur1));
88: }
89:
90: $cnt = (print_roundtrips($c) - $initial_rt);
91: echo "Number of roundtrips made with prefetch count 0 for 5 rows is $cnt\n";
92:
93: $initial_rt = print_roundtrips($c);
94: oci_set_prefetch($cur1,5);
95: for ($i = 0;$i<5;$i++) {
96: var_dump(oci_fetch_row($cur1));
97: }
98:
99: $cnt = (print_roundtrips($c) - $initial_rt );
100: echo "Number of roundtrips made with prefetch count 5 for 5 rows is $cnt\n";
101:
102: echo "------Test 2 - Set Prefetch before PL/SQL fetch ----------\n";
103: // Fetch from PHP
104: $cur1 = oci_new_cursor($c);
105: if (!oci_bind_by_name($s1,":cur1",$cur1,-1,SQLT_RSET)) {
106: die("oci_bind_by_name(sql1) failed!\n");
107: }
108:
109: echo "Fetch Row from PHP\n";
110: oci_execute($s1);
111: oci_execute($cur1);
112: var_dump(oci_fetch_row($cur1));
113: oci_set_prefetch($cur1,5);
114:
115: // Fetch from PL/SQL
116: if (!oci_bind_by_name($s2,":curs1",$cur1,-1,SQLT_RSET)) {
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: function print_roundtrips($c) {
125: $sql_stmt = "select value from v\$mystat a,v\$statname c where
126: a.statistic#=c.statistic# and c.name='SQL*Net roundtrips to/from client'";
127: $s = oci_parse($c,$sql_stmt);
128: oci_define_by_name($s,"VALUE",$value);
129: oci_execute($s);
130: oci_fetch($s);
131: return $value;
132: }
133:
134: // Clean up here
135:
136: $stmtarray = array(
137: "drop package refcurpkg",
138: "drop table refcurtest"
139: );
140:
141: oci8_test_sql_execute($c, $stmtarray);
142:
143: echo "Done\n";
144: ?>
145: --EXPECTF--
146: ------Test 1- Check Roundtrips with prefetch 0 and 5 -----------
147: array(2) {
148: [0]=>
149: string(1) "0"
150: [1]=>
151: string(5) "test0"
152: }
153: array(2) {
154: [0]=>
155: string(1) "1"
156: [1]=>
157: string(5) "test1"
158: }
159: array(2) {
160: [0]=>
161: string(1) "2"
162: [1]=>
163: string(5) "test2"
164: }
165: array(2) {
166: [0]=>
167: string(1) "3"
168: [1]=>
169: string(5) "test3"
170: }
171: array(2) {
172: [0]=>
173: string(1) "4"
174: [1]=>
175: string(5) "test4"
176: }
177: Number of roundtrips made with prefetch count 0 for 5 rows is 6
178: array(2) {
179: [0]=>
180: string(1) "5"
181: [1]=>
182: string(5) "test5"
183: }
184: array(2) {
185: [0]=>
186: string(1) "6"
187: [1]=>
188: string(5) "test6"
189: }
190: array(2) {
191: [0]=>
192: string(1) "7"
193: [1]=>
194: string(5) "test7"
195: }
196: array(2) {
197: [0]=>
198: string(1) "8"
199: [1]=>
200: string(5) "test8"
201: }
202: array(2) {
203: [0]=>
204: string(1) "9"
205: [1]=>
206: string(5) "test9"
207: }
208: Number of roundtrips made with prefetch count 5 for 5 rows is 2
209: ------Test 2 - Set Prefetch before PL/SQL fetch ----------
210: Fetch Row from PHP
211: array(2) {
212: [0]=>
213: string(1) "0"
214: [1]=>
215: string(5) "test0"
216: }
217: Fetch Row from PL/SQL
218: int(101)
219: string(%d) "test101"
220: Done
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to refcur_prefetch_2.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