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

On Jul 4, 8:38 am, Alexander Smirnov <s_alexande..._at_list.ru> wrote:
> 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

What does the parameter list for the procedure GetEmpNamesInArray look like? From the error message I would suspect that the mismatch is between your code and the procedure definition.

HTH -- Mark D Powell -- Received on Wed Jul 04 2007 - 09:11:30 CDT