use of cursor (array)

mserjo · Post by **mserjo** » Fri 16 May 2008 13:09

Hi all,

There is client-server application. Client part is written on C++ Builder 6. Server of Oracle 10.2.
Client application is utillized by the component of Odac 6.25.1.13 (NET) for work with a server.
Whether it is possible, not using OCI, to pass a cursor (or even array) as entry parameter of the kept procedure on a server.
Thanks.

Tried to pass an array:
On a server there is a function which has an entry parameter array.

Code: Select all

create TYPE t_test_rec as object (obj_id number); 
create TYPE AR as table of t_test_rec;  
function test_array(tbl in AR) return number;

At an attempt to cause a function from client application causes an error:

Code: Select all

odacQuery->CreateProcCall("pkg_tst_array.test_array");
odacQuery->Prepare();
odacQuery->ParamByName("tbl")->AsArray->AllocObject("AR"); // "Access Violation"

odacQuery->ParamByName("tbl")->AsArray->ItemAsInteger[0] = 101;
odacQuery->ParamByName("tbl")->AsArray->ItemAsInteger[1] = 102;
odacQuery->ParamByName("tbl")->AsArray->ItemAsInteger[2] = 103;

odacQuery->Execute();
odacQuery->Close();

What is going wrong?

Plash · Post by **Plash** » Sat 17 May 2008 07:20

You get the error because CreateProcCall set DataType of parameter to ftObject. You should change it to ftArray before using AsArray property:

Code: Select all

odacQuery->ParamByName("tbl")->DataType = ftArray;

Note: object and array types are supported only when connecting in OCI mode (TOraSession.Options.Direct = False).

You can pass an array to the stored procedure. But input CURSOR parameters are not supported.

mserjo · Post by **mserjo** » Sat 17 May 2008 09:41

thank you.

and one more question... is support of cursor or array planned at the use direct mode?

Plash · Post by **Plash** » Mon 19 May 2008 06:37

CURSOR parameters are supported in both OCI and Direct modes. But only output CURSOR parameters are supported.

We are not planning to add support of OBJECT, ARRAY and TABLE data types in Direct mode.