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

[Alexander Smirnov]

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

[DA Morgan]

Alexander Smirnov wrote:
> Hello
>
> I write an application using OO4O to access oracle database and want
> to execute stored procedure with array parameter (for performance
> reasons).

Performance reasons? And you have metrics that support that passing in
an array is going to make a significant difference over passing in
individual parameters?

Please post the evidence. Thanks.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Alexander Smirnov]

It's not a passing in an array as a parameter. It's passing in an
array of parameters for wich oracle executes procedure many times
though I call ExecSql only one time (that's how I understand it
works).
Yes it's much faster. As I wrote If I specify SQL statement in
ExecSQL (for example INSERT) than I don't get the PLS-00306 error.

[DA Morgan]

Subject: "calling stored procedure with an array parameter"
Response: "It's not a passing in an array as a parameter"

What?

And if it doesn't work how did you determine it is faster?

[Alexander Smirnov]

On Jul 5, 1:57 am, DA Morgan <damor...@psoug.org> wrote:
> Alexander Smirnov wrote:
> > It's not a passing in an array as a parameter. It's passing in an
> > array of parameters for wich oracle executes procedure many times
> > though I call ExecSql only one time (that's how I understand it
> > works).
> > Yes it's much faster. As I wrote If I specify SQL statement in
> > ExecSQL (for example INSERT) than I don't get the PLS-00306 error.
>
> Subject: "calling stored procedure with an array parameter"
> Response: "It's not a passing in an array as a parameter"
>
> What?
>
> And if it doesn't work how did you determine it is faster?
> --
> Daniel A. Morgan
> University of Washington

> damor...@x.washington.edu (replace x with u to respond)

> Puget Sound Oracle Users Groupwww.psoug.org

OraDatabase.ExeceSQL("insert into T(p) values(:p)");// works using
array parameter
OraDatabase.ExecSQL("begin insert_proc(:p); end;"); // doesn't work
using array parameter

[DA Morgan]

One is SQL the other PL/SQL. The rules are different with regard to
array types and permissions. Get out of OLE and run these from the
SQL*Plus command prompt so you can see what is actually going wrong.

--
Daniel A. Morgan
University of Washington

damo...@x.washington.edu (replace x with u to respond)