Microsoft KB Archive/253353

= PRB: Resultset Column Must be a Formal Argument When Calling Oracle Procedure =

Article ID: 253353

Article Last Modified on 12/3/2003

-

APPLIES TO


 * Microsoft Data Access Components 1.5
 * Microsoft Data Access Components 2.0
 * 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

-



This article was previously published under Q253353



SYMPTOMS
When you call an Oracle procedure that returns an ADO recordset through the Microsoft ODBC driver for Oracle, one of the following error messages may occur:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC driver for Oracle][Oracle]ORA-06550: line x, column x: PLS-00306: wrong number or types of arguments in call to 'PROCEDURE_NAME' ORA-06550: line x, column x: PL/SQL: Statement ignored

Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC driver for Oracle]Resultset column must be a formal argument



CAUSE
When you call an Oracle stored procedure that returns an ADO recordset using the Microsoft ODBC Driver for Oracle, you must use a call syntax similar to the following:

SQL = "{call Oracle_Package.Oracle_Procedure ({resultset xx, column1, column2, column3})}" set cmd = CreateObject ("ADODB.Command") set rs = CreateObject ("ADODB.Recordset") with cmd set .ActiveConnection = some_connection_object .CommandText = SQL .CommandType = adCmdText end with set rs = cmd.execute

This call syntax is used to call an Oracle procedure, which is defined as follows:

CREATE OR REPLACE PACKAGE Oracle_package AS TYPE tcolumn1 is TABLE of NUMBER(10) INDEX BY BINARY_INTEGER; TYPE tcolumn1 is TABLE of VARCHAR2(1000) INDEX BY BINARY_INTEGER; TYPE tcolumn3 is TABLE of VARCHAR2(2000) INDEX BY BINARY_INTEGER; PROCEDURE Oracle_Procedure (column1 OUT  tcolumn1, column2   OUT  tcolumn2, column3   OUT  tcolumn3); END Oracle_package; The most common cause of the error messages is that the column list in the call from the ADO code does not match the column list being returned by the Oracle procedure.

This behavior is by design.



RESOLUTION
Make sure that the number of columns returned from the Oracle procedure matches the number of columns that you expect to be returned when you issue the call through ADO.



MORE INFORMATION
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

229919 HOWTO: Retrieve a Recordset from Oracle Using ADO on ASP

176086 HOWTO: Retrieve Recordsets from Oracle Stored Procs Using ADO

Keywords: kbprb KB253353

-

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

© Microsoft Corporation. All rights reserved.