PRB: ADO Refresh Method May Fail with Oracle Stored Procedure (293802)


The information in this article applies to:

  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.1 (GA)
  • Microsoft Data Access Components 2.1 SP1
  • Microsoft Data Access Components 2.1 SP2
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.5 SP1
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
This article was previously published under Q293802

SYMPTOMS

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:
Run-time error '3265':
Item cannot be found in the collection corresponding to the requested name or ordinal.
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:
Runtime error '-2147217900 (80040e14)':
ORA-06650: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'STORED_PROC_NAME'
ORA-06650: line 1, column 7:
PL/SQL: Statement ignored

CAUSE

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.

RESOLUTION

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.

MORE INFORMATION

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.
Modification Type:MajorLast Reviewed:5/8/2003
Keywords:kbDatabase kbOracle kbprb KB293802
©2002 Microsoft Corporation. All rights reserved.