Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> calling stored procedure with an array parameter using Oracle Object for OLE (OO4O)

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

From: Alexander Smirnov <s_alexander04_at_list.ru>
Date: Wed, 04 Jul 2007 06:36:18 -0700
Message-ID: <1183556178.051110.38300@n2g2000hse.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 the same parameters and in
this 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 an array parameters? Thanks in advance Received on Wed Jul 04 2007 - 08:36:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US