[Sommaire] [Imprimer]

   1  <?php
   2  /* 
   3  V4.80 8 Mar 2006  (c) 2000-2006 John Lim (jlim#natsoft.com.my). 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
  25  CREATE OR REPLACE PACKAGE adodb AS
  26  TYPE TabType IS REF CURSOR RETURN tab%ROWTYPE;
  27  PROCEDURE open_tab (tabcursor IN OUT TabType,tablenames in varchar);
  28  PROCEDURE data_out(input IN varchar, output OUT varchar); 
  29  
  30  procedure myproc (p1 in number, p2 out number);
  31  END adodb;
  32  /
  33  
  34  CREATE OR REPLACE PACKAGE BODY adodb AS
  35  PROCEDURE 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      
  40  PROCEDURE data_out(input IN varchar, output OUT varchar) IS
  41      BEGIN
  42          output := 'Cinta Hati '||input;
  43      END;
  44      
  45  procedure myproc (p1 in number, p2 out number) as
  46  begin
  47  p2 := p1;
  48  end;
  49  END adodb;
  50  /
  51  
  52  ------------------------------------------------------------------
  53  -- END PACKAGE
  54  ------------------------------------------------------------------
  55  
  56  */
  57  
  58  include ('../adodb.inc.php');
  59  include ('../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  ?>