Annotation of embedaddon/php/ext/oci8/tests/refcur_prefetch_4.phpt, revision 1.1
1.1 ! misho 1: --TEST--
! 2: Prefetch with REF cursor. Test No 4
! 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:
! 21: "CREATE TABLE refcurtest (c1 NUMBER, c2 VARCHAR(20))",
! 22:
! 23: "CREATE or REPLACE PACKAGE refcurpkg is
! 24: type refcursortype is ref cursor;
! 25: procedure open_ref_cur(cur1 out refcursortype);
! 26: procedure fetch_ref_cur(cur1 in refcursortype, c1 out number, c2 out varchar2);
! 27: end refcurpkg;",
! 28:
! 29: "CREATE or REPLACE PACKAGE body refcurpkg is
! 30: procedure open_ref_cur(cur1 out refcursortype) is
! 31: begin
! 32: open cur1 for select * from refcurtest order by c1;
! 33: end open_ref_cur;
! 34: procedure fetch_ref_cur(cur1 in refcursortype, c1 out number, c2 out varchar2) is
! 35: begin
! 36: fetch cur1 into c1,c2;
! 37: end fetch_ref_cur;
! 38: end refcurpkg;"
! 39: );
! 40:
! 41: oci8_test_sql_execute($c, $stmtarray);
! 42:
! 43: // Insert 500 rows into the table.
! 44: $insert_sql = "INSERT INTO refcurtest (c1, c2) VALUES (:c1,:c2)";
! 45: if (!($s = oci_parse($c, $insert_sql))) {
! 46: die("oci_parse(insert) failed!\n");
! 47: }
! 48:
! 49: for ($i = 0; $i <= 500; $i++) {
! 50: $val2 = 'test'.$i;
! 51: oci_bind_by_name($s,':c1',$i);
! 52: oci_bind_by_name($s,':c2',$val2);
! 53: if (!oci_execute($s)) {
! 54: die("oci_execute(insert) failed!\n");
! 55: }
! 56: }
! 57:
! 58: // Steps to Fetch from PHP . For every sub-test,the cursor is bound and then executed.
! 59:
! 60: $sql1 = "begin refcurpkg.open_ref_cur(:cur1); end;";
! 61: $s1 = oci_parse($c,$sql1);
! 62: $cur1 = oci_new_cursor($c);
! 63: if (!oci_bind_by_name($s1,":cur1",$cur1,-1,SQLT_RSET)) {
! 64: die("oci_bind_by_name(sql1) failed!\n");
! 65: }
! 66:
! 67:
! 68: // Steps to Fetch from PL/SQL . For every sub-test,the cursor is bound and then executed.
! 69:
! 70: $sql2 = "begin refcurpkg.fetch_ref_cur(:curs1,:c1,:c2); end;";
! 71: $s2 = oci_parse($c,$sql2);
! 72: if (!oci_bind_by_name($s2, ":curs1", $cur1, -1, SQLT_RSET)) {
! 73: die("oci_bind_by_name(sql2) failed!\n");
! 74: }
! 75: if (!oci_bind_by_name($s2, ":c1", $c1, -1, SQLT_INT)) {
! 76: die("oci_bind_by_name(sql2) failed!\n");
! 77: }
! 78: if (!oci_bind_by_name($s2, ":c2", $c2, 20, SQLT_CHR)) {
! 79: die("oci_bind_by_name(sql2) failed!\n");
! 80: }
! 81:
! 82:
! 83: echo "------Test 1 - Set Prefetch after PL/SQL fetch ----------\n";
! 84: $cur1 = oci_new_cursor($c);
! 85: // Fetch from PL/SQL
! 86: if (!oci_bind_by_name($s2,":curs1",$cur1,-1,SQLT_RSET)) {
! 87: die("oci_bind_by_name(sql2) failed!\n");
! 88: }
! 89: oci_execute($s2);
! 90: echo "Fetch Row from PL/SQL\n";
! 91: var_dump($c1);
! 92: var_dump($c2);
! 93:
! 94: // Fetch from PHP
! 95: echo "Fetch Row from PHP\n";
! 96: if (!oci_bind_by_name($s1,":cur1",$cur1,-1,SQLT_RSET)) {
! 97: die("oci_bind_by_name(sql1) failed!\n");
! 98: }
! 99: oci_set_prefetch($cur1,5);
! 100: oci_execute($s1);
! 101: oci_execute($cur1);
! 102: var_dump(oci_fetch_row($cur1));
! 103:
! 104: echo "------Test 2- Overwrite prefetch-----------\n";
! 105: // Fetch from PHP
! 106: $cur1 = oci_new_cursor($c);
! 107: if (!oci_bind_by_name($s1,":cur1",$cur1,-1,SQLT_RSET)) {
! 108: die("oci_bind_by_name(sql1) failed!\n");
! 109: }
! 110: echo "Fetch Row from PHP\n";
! 111: oci_execute($s1);
! 112: oci_execute($cur1);
! 113: var_dump(oci_fetch_row($cur1));
! 114: oci_set_prefetch($cur1,5);
! 115: oci_set_prefetch($cur1,0);
! 116: oci_set_prefetch($cur1,100);
! 117:
! 118: // Fetch from PL/SQL
! 119: if (!oci_bind_by_name($s2,":curs1",$cur1,-1,SQLT_RSET)) {
! 120: die("oci_bind_by_name(sql2) failed!\n");
! 121: }
! 122: oci_execute($s2);
! 123: echo "Fetch Row from PL/SQL\n";
! 124: var_dump($c1);
! 125: var_dump($c2);
! 126:
! 127:
! 128: function print_roundtrips($c) {
! 129: $sql_stmt = "select value from v\$mystat a,v\$statname c where
! 130: a.statistic#=c.statistic# and c.name='SQL*Net roundtrips to/from client'";
! 131: $s = oci_parse($c,$sql_stmt);
! 132: oci_define_by_name($s,"VALUE",$value);
! 133: oci_execute($s);
! 134: oci_fetch($s);
! 135: return $value;
! 136: }
! 137:
! 138: // Clean up here
! 139:
! 140: $stmtarray = array(
! 141: "drop package refcurpkg",
! 142: "drop table refcurtest"
! 143: );
! 144:
! 145: oci8_test_sql_execute($c, $stmtarray);
! 146:
! 147: echo "Done\n";
! 148: ?>
! 149: --EXPECTF--
! 150: ------Test 1 - Set Prefetch after PL/SQL fetch ----------
! 151:
! 152: Warning: oci_execute(): ORA-01001: %s
! 153: ORA-06512: at "SYSTEM.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: Fetch Row from PHP
! 159: array(2) {
! 160: [0]=>
! 161: string(1) "0"
! 162: [1]=>
! 163: string(5) "test0"
! 164: }
! 165: ------Test 2- Overwrite prefetch-----------
! 166: Fetch Row from PHP
! 167: array(2) {
! 168: [0]=>
! 169: string(1) "0"
! 170: [1]=>
! 171: string(5) "test0"
! 172: }
! 173: Fetch Row from PL/SQL
! 174: int(101)
! 175: string(%d) "test101"
! 176: Done
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to refcur_prefetch_4.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