Microsoft KB Archive/293802

= PRB: ADO Refresh Method May Fail with Oracle Stored Procedure =

Article ID: 293802

Article Last Modified on 5/8/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.1 Service Pack 1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.5 Service Pack 1
 * 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 &quot;package.procedure&quot; 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, &quot;server.database.schema.object&quot;) 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 &quot;package.procedure&quot; 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.



Steps to Reproduce Behavior
 Create a new Visual Basic standard EXE project. Form1 is created by default. Set a reference to Microsoft ActiveX Data Objects (ADO).  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 &quot;Provider=MSDASQL;DSN=MyOracleDSN;UID=user;PWD=password&quot; Set cmd = New ADODB.Command With cmd Set .ActiveConnection = cn   .CommandType = adCmdStoredProc .CommandText = &quot;MyPackage.MyStoredProc&quot; 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 &quot;Parameters.Refresh&quot; call.

Additional query words: 3265 -2147217900 (80040e14)

Keywords: kboracle kbdatabase kbprb KB293802

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.