1<?php 2/* 3V4.80 8 Mar 2006 (c) 2000-2009 John Lim (jlim#natsoft.com). All rights reserved. 4 Released under both BSD license and Lesser GPL library license. 5 Whenever there is any discrepancy between the two licenses, 6 the BSD license will take precedence. 7 Set tabs to 4 for best viewing. 8 9 Latest version is available at http://adodb.sourceforge.net 10*/ 11 12/* 13 Test for Oracle Variable Cursors, which are treated as ADOdb recordsets. 14 15 We have 2 examples. The first shows us using the Parameter statement. 16 The second shows us using the new ExecuteCursor($sql, $cursorName) 17 function. 18 19------------------------------------------------------------------ 20-- TEST PACKAGE YOU NEED TO INSTALL ON ORACLE - run from sql*plus 21------------------------------------------------------------------ 22 23 24-- TEST PACKAGE 25CREATE OR REPLACE PACKAGE adodb AS 26TYPE TabType IS REF CURSOR RETURN tab%ROWTYPE; 27PROCEDURE open_tab (tabcursor IN OUT TabType,tablenames in varchar); 28PROCEDURE data_out(input IN varchar, output OUT varchar); 29 30procedure myproc (p1 in number, p2 out number); 31END adodb; 32/ 33 34CREATE OR REPLACE PACKAGE BODY adodb AS 35PROCEDURE open_tab (tabcursor IN OUT TabType,tablenames in varchar) IS 36 BEGIN 37 OPEN tabcursor FOR SELECT * FROM tab where tname like tablenames; 38 END open_tab; 39 40PROCEDURE data_out(input IN varchar, output OUT varchar) IS 41 BEGIN 42 output := 'Cinta Hati '||input; 43 END; 44 45procedure myproc (p1 in number, p2 out number) as 46begin 47p2 := p1; 48end; 49END adodb; 50/ 51 52------------------------------------------------------------------ 53-- END PACKAGE 54------------------------------------------------------------------ 55 56*/ 57 58include('../adodb.inc.php'); 59include('../tohtml.inc.php'); 60 61 error_reporting(E_ALL); 62 $db = ADONewConnection('oci8'); 63 $db->PConnect('','scott','natsoft'); 64 $db->debug = 99; 65 66 67/* 68*/ 69 70 define('MYNUM',5); 71 72 73 $rs = $db->ExecuteCursor("BEGIN adodb.open_tab(:RS,'A%'); END;"); 74 75 if ($rs && !$rs->EOF) { 76 print "Test 1 RowCount: ".$rs->RecordCount()."<p>"; 77 } else { 78 print "<b>Error in using Cursor Variables 1</b><p>"; 79 } 80 81 print "<h4>Testing Stored Procedures for oci8</h4>"; 82 83 $stid = $db->PrepareSP('BEGIN adodb.myproc('.MYNUM.', :myov); END;'); 84 $db->OutParameter($stid, $myov, 'myov'); 85 $db->Execute($stid); 86 if ($myov != MYNUM) print "<p><b>Error with myproc</b></p>"; 87 88 89 $stmt = $db->PrepareSP("BEGIN adodb.data_out(:a1, :a2); END;",true); 90 $a1 = 'Malaysia'; 91 //$a2 = ''; # a2 doesn't even need to be defined! 92 $db->InParameter($stmt,$a1,'a1'); 93 $db->OutParameter($stmt,$a2,'a2'); 94 $rs = $db->Execute($stmt); 95 if ($rs) { 96 if ($a2 !== 'Cinta Hati Malaysia') print "<b>Stored Procedure Error: a2 = $a2</b><p>"; 97 else echo "OK: a2=$a2<p>"; 98 } else { 99 print "<b>Error in using Stored Procedure IN/Out Variables</b><p>"; 100 } 101 102 103 $tname = 'A%'; 104 105 $stmt = $db->PrepareSP('select * from tab where tname like :tablename'); 106 $db->Parameter($stmt,$tname,'tablename'); 107 $rs = $db->Execute($stmt); 108 rs2html($rs); 109 110 111?>