照网上学的简单例子提示出错? 调用存储过程游标返回结果集的问题！！！

[fourthboy]

学着网上的例子写的，提示出错，请高手看看，不胜感谢！！！

建包
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

[fourthboy]

我找到原因了，原来代码没有问题是Provider有问题

把Provider=OraOLEDB.Oracle.1;

改为Provider=MSDAORA.1; 就可以了

[新鲜鱼排]

什么时候执行了Package?

'
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

[zhpsam109]

不是人家的provider有问题，是你没安装oracle的oledb驱动啊！

[zhpsam109]

oracle provider for oledb