calling stored procedure with an array parameter from Oracle Object for OLE (OO4O)

From: Alexander Smirnov <s_alexander04_at_list.ru>
Date: Wed, 04 Jul 2007 05:38:23 -0700
Message-ID: <1183552703.582625.206640@g4g2000hsf.googlegroups.com>

Hello

I write an application using OO4O to access oracle database and want to execute stored procedure with array parameter (for performance reasons). But I get the following Error: PLS-00306: wrong number or types of arguments. But number of arguments and types are right. For experiment I changed the PL/SQL block in which I call procedure with SQL statement with same parameters and in that case all works fine. The example in Visual Basic that comes with oracle client documentation for OO4O just doesn't work:

Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim EmpnoArray As OraParamArray
Dim EnameArray As OraParamArray

Set OraSession = CreateObject("OracleInProcServer.XOraSession") Set OraDatabase = OraSession.OpenDatabase("exampledb", "scott/tiger",

                                                                 0&)

OraDatabase.Parameters.Add "ArraySize", 3, ORAPARM_INPUT OraDatabase.Parameters.AddTable "EMPNOS", ORAPARM_INPUT, ORATYPE_NUMBER,

                                 3, 22
OraDatabase.Parameters.AddTable "ENAMES", ORAPARM_OUTPUT,
                                 ORATYPE_VARCHAR2, 3, 10

Set EmpnoArray = OraDatabase.Parameters("EMPNOS") Set EnameArray = OraDatabase.Parameters("ENAMES")

'Initialize the newly created input parameter table EMPNOS
EmpnoArray(0) = 7698

EmpnoArray(1) = 7782
EmpnoArray(2) = 7654

'Execute the PLSQL package

OraDatabase.ExecuteSQL ("Begin
Employee.GetEmpNamesInArray(:ArraySize,

                         :EMPNOS, :ENAMES); End;")

How it is correctly to invoke procedure with array parameters? Thanks in advance Received on Wed Jul 04 2007 - 07:38:23 CDT