title: Oracle ORA-06550 identifier must be declared when called from ADO Command Object
body: <p>I am trying to use Access / VBA to retrieve a resultset from my Oracle stored procedure, using an ADO Command object.</p>
  
  <p>I keep receiving the error: </p>
  
  <pre><code>[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
  </code></pre>
  
  <p>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.  </p>
  
  <p>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.
  <a href="http://support.microsoft.com/kb/176086" rel="nofollow noreferrer">http://support.microsoft.com/kb/176086</a></p>
  
  <p>When I <em>Run PL/SQL</em> in Oracle SQL Developer, the return value is listed in the <em>Output Variables</em> window.  This tells me that both the stored procedure is working, and the necessary permissions have been granted.</p>
  
  <p><strong>Oracle Version:</strong></p>
  
  <p>SELECT * FROM v$version</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>Oracle Package Definition:</strong></p>
  
  <pre><code>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;
  </code></pre>
  
  <p><strong>MS Access VBA Code</strong></p>
  
  <pre><code>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 "&gt;&gt;&gt; Return Value: " &amp; oraNum
  
    ' Close the Recordset &amp; Connection Objects
    oraRetSet.Close
    oraConn.Close
  
    ' Clean Up
    Set oraConn = Nothing
    Set oraCmd = Nothing
    Set oraRetSet = Nothing
  
  End Sub
  </code></pre>
  
  <p>I am really drawing blanks - which means its probably something small that I missed.  Any help would be appreciated.</p>
  
  <p><strong>Update Aug 29</strong>
  I have made no progress.   I tried to switch from ODBC to the OLEDB Provider, following another stackoverflow question / answer as a guide:</p>
  
  <p><a href="https://stackoverflow.com/questions/4558714/how-do-i-call-an-oracle-stored-procedure-from-an-excel-vba-script">How do I call an Oracle stored procedure from an Excel VBA script?</a></p>
  
  <pre><code>' Connect to the Oracle Server
  oraConn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=oradsn;" &amp; _
                   "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
  </code></pre>
  
  <p>This gets rid of the undeclared identifier, but it does not return any recordset ..  oraRetSet.Fields.Count equals 0</p>