2 Prefetch with Nested cursors with INI setting.
4 oci8.default_prefetch=5
6 <?php if (!extension_loaded('oci8')) die("skip no oci8 extension");
7 if (!extension_loaded('oci8')) die("skip no oci8 extension");
8 require(dirname(__FILE__)."/connect.inc");
9 if (preg_match('/Release (11\.2|12)\./', oci_server_version($c), $matches) !== 1) {
10 die("skip expected output only valid when using Oracle 11gR2 or greater databases");
11 } else if (preg_match('/^(11\.2|12)\./', oci_client_version()) != 1) {
12 die("skip test expected to work only with Oracle 11gR2 or greater version of client");
18 require dirname(__FILE__)."/connect.inc";
22 "drop table nescurtest",
23 "create table nescurtest(c1 varchar2(10))"
26 oci8_test_sql_execute($c, $stmtarray);
28 // Insert 500 rows into the table.
29 $insert_sql = "INSERT INTO nescurtest (c1) VALUES (:c1)";
30 if (!($s = oci_parse($c, $insert_sql))) {
31 die("oci_parse(insert) failed!\n");
34 for ($i = 0; $i<=500; $i++) {
36 oci_bind_by_name($s,':c1',$val2);
37 if (!oci_execute($s)) {
38 die("oci_execute(insert) failed!\n");
42 echo"-----------------------------------------------\n";
43 echo "Test with Nested Cursors\n";
44 echo"-----------------------------------------------\n";
45 $cur1 = oci_new_cursor($c);
46 $sqlstmt = "select cursor(select * from nescurtest) curs1 from dual";
47 $s = oci_parse($c,$sqlstmt);
49 $data = oci_fetch_array($s);
50 oci_execute($data['CURS1']);
52 // Calculate round-trips
53 $initial_rt = print_roundtrips($c);
54 for ($i = 0;$i<10;$i++) {
55 echo "Fetch Row using Nested cursor Query\n";
56 var_dump(oci_fetch_row($data['CURS1']));
59 $cnt = (print_roundtrips($c) - $initial_rt);
60 echo "Number of roundtrips made with prefetch count 5 for 10 rows is $cnt\n";
62 function print_roundtrips($c) {
63 $sql_stmt = "select value from v\$mystat a,v\$statname c where
64 a.statistic#=c.statistic# and c.name='SQL*Net roundtrips to/from client'";
65 $s = oci_parse($c,$sql_stmt);
66 oci_define_by_name($s,"VALUE",$value);
75 "drop table nescurtest"
78 oci8_test_sql_execute($c, $stmtarray);
83 -----------------------------------------------
84 Test with Nested Cursors
85 -----------------------------------------------
86 Fetch Row using Nested cursor Query
89 %unicode|string%(%d) "test0"
91 Fetch Row using Nested cursor Query
94 %unicode|string%(%d) "test1"
96 Fetch Row using Nested cursor Query
99 %unicode|string%(%d) "test2"
101 Fetch Row using Nested cursor Query
104 %unicode|string%(%d) "test3"
106 Fetch Row using Nested cursor Query
109 %unicode|string%(%d) "test4"
111 Fetch Row using Nested cursor Query
114 %unicode|string%(%d) "test5"
116 Fetch Row using Nested cursor Query
119 %unicode|string%(%d) "test6"
121 Fetch Row using Nested cursor Query
124 %unicode|string%(%d) "test7"
126 Fetch Row using Nested cursor Query
129 %unicode|string%(%d) "test8"
131 Fetch Row using Nested cursor Query
134 %unicode|string%(%d) "test9"
136 Number of roundtrips made with prefetch count 5 for 10 rows is 3