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:
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