Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

This article was previously published under Q253353

↑ Back to the top


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

↑ Back to the top


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.

↑ Back to the top


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.

↑ Back to the top


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

↑ Back to the top


Keywords: KB253353, kbprb

↑ Back to the top

Article Info
Article ID : 253353
Revision : 4
Created on : 12/3/2003
Published on : 12/3/2003
Exists online : False
Views : 546