Microsoft KB Archive/253353

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 12:52, 21 July 2020 by X010 (talk | contribs) (Text replacement - """ to """)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Knowledge Base


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