OCI application calling a stored function: handling objects.

From: sasfi <fiorinis_at_dsdata.it>
Date: 19 Aug 2002 03:02:05 -0700
Message-ID: <61e1c7f5.0208190202.734debc3@posting.google.com>

Hi all,
I'm investigating the way to work with slightly complex Oracle objects within an OCI application using Oracle8.1.7 for both winNT and HP-UX 11.

The C client application calls a PL/SQL function which return an object instance.
The returned object type (CUSTOMERINFO) has 2 attributes: 1 varchar2 (ID) + 1 varray (VARR_INFO) of object INFO.

---

This is the SQL for the defined Oracle types:

---

create or replace type INFO as object (

   attrib VARCHAR2(256),
   value VARCHAR2(256)
);

create or replace type VARR_INFO as varray(12) of INFO;

create or replace type CUSTOMERINFO as object (

	id      VARCHAR2(20),
	listParams  VARR_INFO

);

---

This is the stored function called from the client application

---

Function testfunc (id varchar2) return CUSTOMERINFO as

resultset_info CUSTOMERINFO;

begin

resultset_info :=CUSTOMERINFO(
'id',
VARR_INFO (

			INFO( 'attrib1', 'value1'),
			INFO( 'attrib2', 'value2')
          )

);

return resultset_info;
end testfunc;

---

This is the way the client application works:

---

/*
statement used to call the stored function:

"BEGIN \
 :customerinfo := testfunc(:sessionId);\ END;";
*/

OCIBindByName for the :sessionId IN parameter

OCIBindByName for the :customerinfo return object OCITypeByName for the type CUSTOMERINFO
OCIBindObject for the :customerinfo.
OCIStmtExecute.

---

The statement is succesfully executed but I can only manage to access the ID attribute (varchar2) of the returned CUSTOMERINFO object not the varray attribute (LISTPARAMS) of the same object.

I've tried to read this attribute using iterators but it does not work causing the application to abort when calling OCICollSize or OCIIterNext.

I've searched through the Oracle application development manuals and the web
but I could not find some clean example illustrating the way to do it.

How can I read the varray attribute of the returned struct ?

Any help/hint is much appreciated,

Thanks,
Sasfi. Received on Mon Aug 19 2002 - 05:02:05 CDT