学着网上的例子写的,提示出错,请高手看看,不胜感谢!!! 建包
CREATE OR REPLACE PACKAGE TEST_RETURN_PACK
AS
type ResultData is ref cursor;--定义动态游标
procedure test_getpack_data(user_id in number,rec_value out ResultData );
END;
/ 包体
CREATE OR REPLACE PACKAGE BODY TEST_RETURN_PACK
AS procedure test_getpack_data(user_id in number,rec_value out ResultData) as
begin
open rec_value for SELECT * FROM US_POINT WHERE USER_ID <=user_id;
end;
END;
/ 用VB调试返回结果集的代码:
Option Explicit
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim strconn As String
Dim strcall As String Private Sub Command1_Click()
Dim strcall_test As String
Dim param As Parameter strconn = "Provider=OraOLEDB.Oracle.1;Data Source=db_source;User ID=user_name;Password=psw;Persist Security Info=True;Extended Properties=''"
strcall = "{call TEST_RETURN_PACK.test_getpack_data(?,{resultset 24,rec_value})}" With cmd
.CommandText = strcall
.ActiveConnection = strconn
.CommandType = adCmdText
End With rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
rs.CursorLocation = adUseClient
Set param = cmd.CreateParameter("user_id", adNumeric, adParamInput, 8, 99)
cmd.Parameters.Append param
'Set param = cmd.CreateParameter("rec_value", adUserDefined, adParamOutput)
'cmd.Parameters.Append param ''-------------如果不加上面的二行代码提示出错:ORA-06550: 第 1 行, 第 45 列:
'PLS-00201: 必须说明标识符 'REC_VALUE'
'ORA-06550: 第 1 行, 第 7 列:
'PL/SQL: Statement ignored '由于返回的是游标结果集 ,我不知道在VB里是用什么类型、大小之类的,如果不填那几个参数也不行 '------------如果加上那二行提示出错:多步 OLE DB 操作产生错误。如果可能,请检查每个 OLE DB 状态值。没有工作被完成。
Set rs.Source = cmd
rs.Open
While Not rs.EOF
MsgBox " " & rs(0) & ", " & rs(1) & ", " & rs(2) & ", " & rs(3)
'对结果集的处理在这里增加代码
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
Set cmd = Nothing End Sub
CREATE OR REPLACE PACKAGE TEST_RETURN_PACK
AS
type ResultData is ref cursor;--定义动态游标
procedure test_getpack_data(user_id in number,rec_value out ResultData );
END;
/ 包体
CREATE OR REPLACE PACKAGE BODY TEST_RETURN_PACK
AS procedure test_getpack_data(user_id in number,rec_value out ResultData) as
begin
open rec_value for SELECT * FROM US_POINT WHERE USER_ID <=user_id;
end;
END;
/ 用VB调试返回结果集的代码:
Option Explicit
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim strconn As String
Dim strcall As String Private Sub Command1_Click()
Dim strcall_test As String
Dim param As Parameter strconn = "Provider=OraOLEDB.Oracle.1;Data Source=db_source;User ID=user_name;Password=psw;Persist Security Info=True;Extended Properties=''"
strcall = "{call TEST_RETURN_PACK.test_getpack_data(?,{resultset 24,rec_value})}" With cmd
.CommandText = strcall
.ActiveConnection = strconn
.CommandType = adCmdText
End With rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
rs.CursorLocation = adUseClient
Set param = cmd.CreateParameter("user_id", adNumeric, adParamInput, 8, 99)
cmd.Parameters.Append param
'Set param = cmd.CreateParameter("rec_value", adUserDefined, adParamOutput)
'cmd.Parameters.Append param ''-------------如果不加上面的二行代码提示出错:ORA-06550: 第 1 行, 第 45 列:
'PLS-00201: 必须说明标识符 'REC_VALUE'
'ORA-06550: 第 1 行, 第 7 列:
'PL/SQL: Statement ignored '由于返回的是游标结果集 ,我不知道在VB里是用什么类型、大小之类的,如果不填那几个参数也不行 '------------如果加上那二行提示出错:多步 OLE DB 操作产生错误。如果可能,请检查每个 OLE DB 状态值。没有工作被完成。
Set rs.Source = cmd
rs.Open
While Not rs.EOF
MsgBox " " & rs(0) & ", " & rs(1) & ", " & rs(2) & ", " & rs(3)
'对结果集的处理在这里增加代码
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
Set cmd = Nothing End Sub
解决方案 »
- 我DROP掉一张表后,为什么 USER_TAB_COLS视图没有更新呀?
- DBMS_OUTPUT.put与DBMS_OUTPUT.put_line的区别????
- 请问专家:怎样释放回滚段所占的表空间???我的ROLLBACK表空间已有1.5G,那个慢啊。。。
- 求集群命令
- 懂得Oracle的进来一下咯~~这里出来了个问题呀~~
- 大虾救命!!我的服务器瘫了
- 如何把long类型的数据插入到blob字段中去?
- oracle的登陆问题!
- 简单的查询
- 修改时间请教
- oracle在java存储过程中如何创建和删除 txt文件呢,是否需要授权呢
- 如何向Oracle中添加java程序中用到的jar文件,总是报错!!困惑
Dim strSql As String
Dim strError As String
Dim En As rdoEnvironment
Dim Qr As rdoQuery
Dim Cn As rdoConnection
Dim Rs As rdoResultset
Dim strCon, UserID As String Private Sub Command1_Click() strCon = "DSN=MyOracle;UID=MyUID;PWD=MyPassword;"
strSql = "{call NoTable(?,?)}" Set En = rdoEnvironments(0)
En.CursorDriver = rdUseClientBatch
Set Cn = En.OpenConnection("", rdDriverNoPrompt, _
False, strCon) 'Pause the program to check on number of open cursors.
'Should be 0 (zero) at this point
MsgBox "Check Open Cursors" Set Qr = Cn.CreateQuery("", strSql) Qr.rdoParameters(0).Value = 10
Qr.Execute 'Pause the program to check on number of open cursors.
'Should be three at this point.
MsgBox "Check Open Cursors" Debug.Print "The Output is " & Qr(1)
Qr.Close 'Should be two open cursors at this point.
MsgBox "DONE" End Sub