Annotation of embedaddon/php/ext/oci8/tests/refcur_prefetch_1.phpt, revision 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