1 | <?php |
---|
2 | /* |
---|
3 | V4.80 8 Mar 2006 (c) 2000-2007 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 | ?> |
---|