ASP调用ORACLE中包的函数出错，请大家帮我看一下。
set cmd=server.CreateObject("adodb.command")
'set cmdrc=server.CreateObject("adodb.recordset")
'cmdrs.close
cmd.ActiveConnection = Session("adoconn")
cmd.CommandType = 4
cmd.CommandText = "selectbudgettitle(1)"
'cmd.Parameters.Append cmd.CreateParameter("p_depart_id",adInteger,adParamInput,10,1 )
cmdrc=cmd.Execute()
我在oracle中建了一个包，包头是：
create or replace package tinger01 as
type myrctype is ref cursor;
function selectbudgettitle(p_depart_id in d_budget.depart_id%type) return myrctype;
end tinger01;
问题点数：100、回复次数：6Top
1 楼dongxiangjun（）回复于 2002-11-15 16:11:03 得分 0
cmd.CommandText = "tinger01.selectbudgettitle(1)"Top
2 楼tinger（影虎）回复于 2002-11-15 18:23:01 得分 0
不行，还是错误。下面是修改过的代码。
set cn = server.CreateObject("adodb.connection")
cn.open STRDSN
set cmd=server.CreateObject("adodb.command")
'set cmdrc=server.CreateObject("adodb.recordset")
'cmdrs.close
cmd.ActiveConnection = cn
cmd.CommandType = 4
cmd.CommandText = "tinger01.selectbudgettitle(1)"
'cmd.Parameters.Append cmd.CreateParameter("p_depart_id",adInteger,adParamInput,10,1 )
cmd.ExecuteTop
3 楼penitent（只取一瓢）回复于 2002-11-15 18:33:39 得分 0
返回记录集不要用函数，用过程好。
而且，调用返回记录集的过程语句也不是这样的
这是vb调用返回记录集过程的一个例子。
asp和vb相似，你再改改就可以了，其中cmdR就是一个adodb.command
With cmdR
.CommandText = "{call pkgRemote.sp_Get_CandidateImg(?,?,{resultset 0, io_cursor})}"
.CommandType = adCmdText 'adCmdText
Set Pram = .CreateParameter("iRemote_ID_I", adBSTR, adParamInput, , RemoteID)
.Parameters.Append Pram
Set Pram = .CreateParameter("iResponseID", adInteger, adParamInput, , ResponseID)
.Parameters.Append Pram
End With
Top
4 楼tinger（影虎）回复于 2002-11-15 19:10:55 得分 0
Set Pram = .CreateParameter("iRemote_ID_I", adBSTR, adParamInput, , RemoteID)
中的RemoteID是什么意思？
参数的值是什么？应放在什么地方。
返回的参数怎么调用？Top
5 楼penitent（只取一瓢）回复于 2002-11-15 21:55:36 得分 0
RemoteID只是我给你的一个参数的例子。
在我的这个例子中，入口参数有两个
一个是iRemote_ID_I，对应程序中的变量RemoteID
一个是iResponseID，对应程序中的变量ResponseID
如果
Dim rsR As New ADODB.Recordset
rsR.CursorType = adOpenStatic
rsR.LockType = adLockReadOnly
可以
Set cmdR.ActiveConnection = CN 'CN是一个ADO的连接
Set rsR.Source = cmdR
或
set rsR = cmdR.Execute '这是另一种执行方式
rsR.Open
Top
6 楼penitent（只取一瓢）回复于 2002-11-15 22:06:34 得分 100
你再看看这个例子，希望对你有帮助，
也是返回记录集和vb的调用方法
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 <User ID>, <Password>, and <Server> with the
'appropriate parameters.
Conn = "UID=*****;PWD=*****;driver=" _
& "{Microsoft ODBC for Oracle};SERVER=dseOracle;"
Set Cn = New ADODB.Connection
With Cn
.ConnectionString = Conn
.CursorLocation = adUseClient
.Open
End With
QSQL = "{call packperson.allperson({resultset 9, ssn, fname, " _
& "lname})}"
Set CPw1 = New ADODB.Command
With CPw1
Set .ActiveConnection = Cn
.CommandText = QSQL
.CommandType = adCmdText
End With
QSQL = "{call packperson.oneperson(?,{resultset 2, ssn, fname, " _
& "lname})}"
Set CPw2 = New ADODB.Command
With CPw2
Set .ActiveConnection = Cn
.CommandText = QSQL
.CommandType = adCmdText
.Parameters.Append .CreateParameter(, adInteger, adParamInput)
End With
Set Rs = New ADODB.Recordset
With Rs
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With
End Sub
Private Sub Form_Unload(Cancel As Integer)
Cn.Close
Set Cn = Nothing
Set CPw1 = Nothing
Set CPw2 = Nothing
Set Rs = Nothing
End Sub
Go to the Project menu item and select References. Select the "Microsoft Active Data Objects 2.x Library."
Run the project. When you click on the "Get Everyone" button, it executes this query:
QSQL = "{call packperson.allperson({resultset 9, ssn, fname, "_
& "lname})}"
Top
相关问题
关键词
得分解答快速导航
相关链接
广告也精彩
反馈
