vba 从 ADO 命令对象调用时必须声明 Oracle ORA-06550 标识符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18492561/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle ORA-06550 identifier must be declared when called from ADO Command Object
提问by DHW
I am trying to use Access / VBA to retrieve a resultset from my Oracle stored procedure, using an ADO Command object.
我正在尝试使用 Access/VBA 从我的 Oracle 存储过程中检索结果集，使用 ADO 命令对象。
I keep receiving the error:
我不断收到错误：
[Oracle][ODBC][Ora]ORA-06550: line 1, column 19:
PLS-00201: identifier 'RETURNDATA' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
I've read a number of articles on the net about how its done - and have tried different ways of doing it (ie. using execute instead of a command object) but I just cannot seem to get it to work.
我在网上阅读了许多关于它是如何完成的文章 - 并尝试了不同的方法（即使用执行而不是命令对象），但我似乎无法让它工作。
Microsoft had a good knowledge base article on how to perform this - but despite following their example - I cannot seem to get it to work. http://support.microsoft.com/kb/176086
Microsoft 有一篇关于如何执行此操作的良好知识库文章 - 但尽管遵循了他们的示例 - 我似乎无法让它工作。 http://support.microsoft.com/kb/176086
When I Run PL/SQLin Oracle SQL Developer, the return value is listed in the Output Variableswindow. This tells me that both the stored procedure is working, and the necessary permissions have been granted.
当我在 Oracle SQL Developer 中运行 PL/SQL时，返回值列在输出变量窗口中。这告诉我存储过程正在运行，并且已授予必要的权限。
Oracle Version:
甲骨文版本：
SELECT * FROM v$version
SELECT * FROM v$version
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
"CORE 9.2.0.8.0 Production"
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
Oracle Package Definition:
Oracle 包定义：
CREATE OR REPLACE
PACKAGE test AUTHID DEFINER AS
PROCEDURE testSP (returnData OUT NUMBER);
END test;
/
CREATE OR REPLACE
PACKAGE BODY test AS
PROCEDURE testSP (returnData OUT NUMBER) IS
BEGIN
returnData := 7;
END;
END test;
MS Access VBA Code
MS Access VBA 代码
Public Sub testSP()
Dim oraConn As ADODB.Connection
Dim oraCmd As ADODB.Command
Dim oraRetSet As ADODB.Recordset
Dim oraNum As Integer
Set oraConn = New ADODB.Connection
' Discovered I need to set this to Read/Write or the SP cannot do any INSERT operations
oraConn.Mode = adModeReadWrite
' Connect to the Oracle Server
oraConn.Open "Driver={Oracle in OraHome102};dbq=oradsn;Uid=username;Pwd=password"
' Set up our Command.
Set oraCmd = New ADODB.Command
Set oraCmd.ActiveConnection = oraConn
oraCmd.CommandType = adCmdText
oraCmd.Parameters.Append oraCmd.CreateParameter("returnData", adNumeric, adParamOutput)
oraCmd.CommandText = "{CALL test.testSP({resultset 1, returnData})}"
' Set up our Recordset
Set oraRetSet = New ADODB.Recordset
oraRetSet.CursorType = adOpenStatic
' Attach our Recordset to our Command
Set oraRetSet.Source = oraCmd
' Open our Recordset
oraRetSet.Open
' Retreive our Stored Procedure Result
oraNum = oraRetSet.Fields(0).Value
' Display Result to user
Debug.Print ">>> Return Value: " & oraNum
' Close the Recordset & Connection Objects
oraRetSet.Close
oraConn.Close
' Clean Up
Set oraConn = Nothing
Set oraCmd = Nothing
Set oraRetSet = Nothing
End Sub
I am really drawing blanks - which means its probably something small that I missed. Any help would be appreciated.
我真的在画空白 - 这意味着它可能是我错过的一些小东西。任何帮助，将不胜感激。
Update Aug 29I have made no progress. I tried to switch from ODBC to the OLEDB Provider, following another stackoverflow question / answer as a guide:
8 月 29 日更新我没有取得任何进展。我尝试从 ODBC 切换到 OLEDB 提供程序，以另一个 stackoverflow 问题/答案为指导：
How do I call an Oracle stored procedure from an Excel VBA script?
如何从 Excel VBA 脚本调用 Oracle 存储过程？
' Connect to the Oracle Server
oraConn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=oradsn;" & _
"User ID=username;Password=password;"
oraConn.Open
Set oraCmd = New ADODB.Command
Set oraCmd.ActiveConnection = oraConn
oraCmd.Parameters.Append oraCmd.CreateParameter("returnData", adSmallInt, adParamOutput)
oraCmd.Properties("PLSQLRSet") = True
oraCmd.CommandText = "{CALL test.testSP(?)}"
Set oraRetSet = oraCmd.Execute
oraCmd.Properties("PLSQLRSet") = False
oraNum = oraRetSet.Fields(0).Value
This gets rid of the undeclared identifier, but it does not return any recordset .. oraRetSet.Fields.Count equals 0
这摆脱了未声明的标识符，但它不返回任何记录集 .. oraRetSet.Fields.Count 等于 0
采纳答案by DHW
Finally figured out what was going on.
终于明白是怎么回事了。
This website is what finally made the light turn on: http://www.oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets.php
这个网站最终让灯亮了：http: //www.oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets.php
I was trying to return a specific value - an error code. Using the recordset in ADO is more designed at returning a data set.
我试图返回一个特定的值 - 一个错误代码。在 ADO 中使用记录集更适合返回数据集。
If you wish to return a cursor ref, as a recordset you need to set an OUT parameter to a cursor ref data type such as SYS_REFCURSOR.
如果您希望将游标引用作为记录集返回，您需要将 OUT 参数设置为游标引用数据类型，例如 SYS_REFCURSOR。
create or replace
PROCEDURE list_staff (r_stafflist OUT SYS_REFCURSOR) AS
BEGIN
OPEN r_stafflist FOR
SELECT name,
telephone,
department
FROM stafflist
ORDER BY name;
END list_staff;
Then ADO will automatically bind to the ref cursor when the command is issued. You don't add the cursor to the ADO Command object parameters.
然后当命令发出时，ADO 会自动绑定到 ref 游标。您不会将光标添加到 ADO 命令对象参数。
