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.