PRB: ADO Refresh Method May Fail with Oracle Stored Procedure

If an ActiveX Data Objects (ADO) application connects to an Oracle database using Microsoft ODBC for Oracle, creates a Command object of type adCmdStoredProc with its CommandText property set to the name of the Oracle package and stored procedure in the "package.procedure" format, and then calls the Refresh method of the Command object's Parameters collection, this call fails with the following error message when the application first tries to refer to one of the subsequent parameters: The same call also fails on the Refresh line of code when you use the Microsoft OLE DB Provider for Oracle, and the following multiple-line Oracle error message is returned:

When parsing a multiple-part database object name (for example, "server.database.schema.object") to obtain a parameter list, the ADO and OLE DB components break this name into its individual elements and pass each element separately. As a result, in the case of an Oracle stored procedure that is contained within an Oracle package, OLE DB passes the procedure name alone where in fact the "package.procedure" syntax is necessary. Thus, the list of parameters is not retrieved.

When you use ADO with Oracle stored procedures that are contained within Oracle packages, you must explicitly create parameters that the stored procedure requires and append these parameters to the Parameters collection of the ADO Command object. You cannot use the Refresh method to retrieve the list of expected parameters.

Steps to Reproduce Behavior

1. Create a new Visual Basic standard EXE project. Form1 is created by default.
2. Set a reference to Microsoft ActiveX Data Objects (ADO).
3. Paste the following code in the Form_Load or in a command button's Click event procedure:

     Dim cn As ADODB.Connection
     Dim cmd As ADODB.Command
     Set cn = New ADODB.Connection
     cn.Open "Provider=MSDASQL;DSN=MyOracleDSN;UID=user;PWD=password"
     Set cmd = New ADODB.Command
     With cmd
       Set .ActiveConnection = cn
       .CommandType = adCmdStoredProc
       .CommandText = "MyPackage.MyStoredProc"
     End With
     cmd.Parameters.Refresh
     cmd.Parameters(0).Value = 123
   					

The above-mentioned error occurs on the last line in the preceding code. If you switch the connection string to use the Microsoft OLE DB Provider for Oracle (MSDAORA), the error occurs on the preceding "Parameters.Refresh" call.