学着网上的例子写的,提示出错,请高手看看,不胜感谢!!! 建包
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
解决方案 »
- 求一条 sql
- 求助:Oracle表更新但是关联视图没有更新
- oracle10g中使用utl_file.fopen遇到的问题,求教,很急!
- 泰文的操作!!!(急等!)
- 如何调用参数为行级变量的过程?
- 请问这样的sql怎么写?
- 一个关于数据导入的问题,急!!!
- 在线等:怎样在不同版本的oracle中导入导出数据?
- 怎样用拷贝的方法备份数据库,才能使恢复后与原数据库完全相同!
- 当要插入的数据宽度大于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