Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> calling stored procedure with an array parameter using Oracle Object for OLE (OO4O)
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, 10Set 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