calling PL/SQL func with OUT parameter from OCI

From: sasfi <fiorinis_at_dsdata.it>
Date: 23 Aug 2002 01:14:35 -0700
Message-ID: <61e1c7f5.0208230014.64b91c91@posting.google.com>

Hi all,
I'm trying to call a PL/SQL function within an OCI application using Oracle8.1.7.

The C client application calls a PL/SQL function which return an object.

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 ?

Can you please help?

Thanks,
Sasfi. Received on Fri Aug 23 2002 - 03:14:35 CDT