下面这个包里的过程我已经验证过了是正确的。
create or replace package tinger01 as
type myrctype is ref cursor;
procedure selectbudgettitle(p_depart_id in d_budget.depart_id%type,
myrc out myrctype);
end tinger01;
---------------------------------------------------------------
1
2dim cn,cmd,cmdrc,driver_name,para
3driver_name="Provider=MSDAORA.1;Password=pass;User ID=user;Data Source=afis;Persist Security Info=True"
4set cn = server.CreateObject("adodb.connection")
5cn.open driver_name
6set cmd=server.CreateObject("adodb.command")
7set cmdrc=server.CreateObject("adodb.recordset")
8
9
10cmd.CommandType = 1
11set para = cmd.Parameters
12'这个地方不要出错，一定要写myrc，不能是别的
13cmd.CommandText = "{call tinger01.selectbudgettitle(?,{resultset 0, myrc})}"
14'这个地方你的也有问题,asp是不认识adInteger,adParamInput的.
15para.Append cmd.CreateParameter("p_depart_id",3,1,4,1)
16'以下两句应当可以不要
17cmdrc.CursorType = 3
18cmdrc.LockType = 3
19'设置连接
20set cmd.ActiveConnection = cn
21set cmdrc.Source = cmd
22cmdrc.Open
23'测试是否成功,我这里返回ok
24if cmdrc.EOF then
25response.Write "false"
26else
27response.Write "ok"
28end if
--------------------------------------
附：vb的调用方法
1、
http://www.vbip.com/books/1861001789/chapter_1789_10.asp
2、
DROP TABLE person;
CREATE TABLE person
(ssn NUMBER(9) PRIMARY KEY,
fname VARCHAR2(15),
lname VARCHAR2(20));
INSERT INTO person VALUES(555662222,'Sam','Goodwin');
INSERT INTO person VALUES(555882222,'Kent','Clark');
INSERT INTO person VALUES(666223333,'Jane','Doe');
COMMIT;
/
Create the following package on your Oracle server:
CREATE OR REPLACE PACKAGE packperson
AS
TYPE tssn is TABLE of NUMBER(10)
INDEX BY BINARY_INTEGER;
TYPE tfname is TABLE of VARCHAR2(15)
INDEX BY BINARY_INTEGER;
TYPE tlname is TABLE of VARCHAR2(20)
INDEX BY BINARY_INTEGER;
PROCEDURE allperson
(ssn OUT tssn,
fname OUT tfname,
lname OUT tlname);
PROCEDURE oneperson
(onessn IN NUMBER,
ssn OUT tssn,
fname OUT tfname,
lname OUT tlname);
END packperson;
/
Create the following package body on your Oracle server:
CREATE OR REPLACE PACKAGE BODY packperson
AS
PROCEDURE allperson
(ssn OUT tssn,
fname OUT tfname,
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn, fname, lname
FROM person;
percount NUMBER DEFAULT 1;
BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;
PROCEDURE oneperson
(onessn IN NUMBER,
ssn OUT tssn,
fname OUT tfname,
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn, fname, lname
FROM person
WHERE ssn = onessn;
percount NUMBER DEFAULT 1;
BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;
END;
/
Open a new project in Visual Basic 5.0 or 6.0 Enterprise edition. Form1 is created by default.
Place the following controls on the form:
Control Name Text/Caption
-----------------------------------------
Button cmdGetEveryone Get Everyone
Button cmdGetOne Get One
From the Tools menu, select the Options item. Click the "Default Full Module View" option and then click OK. This will allow you to view all of the code for this project.
Paste the following code into your code window:
Option Explicit
Dim Cn As ADODB.Connection
Dim CPw1 As ADODB.Command
Dim CPw2 As ADODB.Command
Dim Rs As ADODB.Recordset
Dim Conn As String
Dim QSQL As String
Dim inputssn As Long
Private Sub cmdGetEveryone_Click()
Set Rs.Source = CPw1
Rs.Open
While Not Rs.EOF
MsgBox "Person data: " & Rs(0) & ", " & Rs(1) & ", " & Rs(2)
Rs.MoveNext
Wend
Rs.Close
End Sub
Private Sub cmdGetOne_Click()
Set Rs.Source = CPw2
inputssn = InputBox("Enter the SSN you wish to retrieve:")
CPw2(0) = inputssn
Rs.Open
MsgBox "Person data: " & Rs(0) & ", " & Rs(1) & ", " & Rs(2)
Rs.Close
End Sub
Private Sub Form_Load()
'Replace
1<user id="">, <password>, and <server> with the
2'appropriate parameters.
3Conn = "UID=*****;PWD=*****;driver=" _
4& "{Microsoft ODBC for Oracle};SERVER=dseOracle;"
5
6Set Cn = New ADODB.Connection
7With Cn
8.ConnectionString = Conn
9.CursorLocation = adUseClient
10.Open
11End With
12
13QSQL = "{call packperson.allperson({resultset 9, ssn, fname, " _
14& "lname})}"
15
16Set CPw1 = New ADODB.Command
17With CPw1
18Set .ActiveConnection = Cn
19.CommandText = QSQL
20.CommandType = adCmdText
21End With
22
23QSQL = "{call packperson.oneperson(?,{resultset 2, ssn, fname, " _
24& "lname})}"
25
26Set CPw2 = New ADODB.Command
27With CPw2
28Set .ActiveConnection = Cn
29.CommandText = QSQL
30.CommandType = adCmdText
31.Parameters.Append .CreateParameter(, adInteger, adParamInput)
32End With
33
34Set Rs = New ADODB.Recordset
35With Rs
36.CursorType = adOpenStatic
37.LockType = adLockReadOnly
38End With
39
40End Sub
41
42Private Sub Form_Unload(Cancel As Integer)
43
44Cn.Close
45Set Cn = Nothing
46Set CPw1 = Nothing
47Set CPw2 = Nothing
48Set Rs = Nothing
49
50End Sub
51Go to the Project menu item and select References. Select the "Microsoft Active Data Objects 2.x Library."
52
53Run the project. When you click on the "Get Everyone" button, it executes this query:
54
55QSQL = "{call packperson.allperson({resultset 9, ssn, fname, "_
56& "lname})}"</server></password></user>