Message-Id: <10616.116646@fatcity.com> From: Diana Duncan Date: Mon, 11 Sep 2000 12:37:36 -0400 Subject: RE: Returning REF cursor type variable to a calling Java program This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C01C0E.980AE1C6 Content-Type: text/plain; charset="iso-8859-1" An example below - the PL/SQL function is defined as follows: type groupRecType is record ( group_id groups.id%type, group_name groups.name%type, group_desc groups.description%type ); type userGroupsCurType is ref cursor return groupRecType; function getUserGroups (userID in number, domainID in number, hierarchy in integer DEFAULT 0) return userGroupsCurType; The Java: String stmt = ("begin ? :=groupPkg.getUserGroups(?,?,1); end;"); // Prepare the statement and bind the variables that go in the where clause cs = c.prepareCall(stmt); cs.registerOutParameter (1, OracleTypes.CURSOR); .... // Execute the query and get the result set back cs.execute(); rs = ((OracleCallableStatement)cs).getCursor(1); Hope this helps, Diana -----Original Message----- From: Andrey Bronfin [mailto:bronfin@visualtop.com] Sent: Sunday, September 10, 2000 1:26 PM To: Multiple recipients of list ORACLE-L Subject: Returning REF cursor type variable to a calling Java program Dear list ! I need to return a REF CURSOR type value from a PL/SQL stored procedure to a calling Java program . We problem is that we don't know what datatype should be used in the calling Java program ? I define : create or replace package types as CURSOR get_topic_some_data_c1 is select topic_id,topic_name,resource_id from topic ; TYPE RefCurs_GTSD IS REF CURSOR RETURN get_topic_some_data_c1%ROWTYPE ; end types ; / create or replace procedure get_topic_some_data ( v_parent_id in number , v_out_RefCurs_GTSD out TYPES.RefCurs_GTSD ) as BEGIN open v_out_RefCurs_GTSD for select topic_id,topic_name,resource_id from topic where PARENT_ID = v_parent_id ; END get_topic_some_data ; / That is , the Java program that calls the PL/SQL stored proc , should be able to "understand" the 2nd parameter of the stored proc , that is , the parameter of data type : TYPES.RefCurs_GTSD . How can we implement this ? How should be the data type of " v_out_RefCurs_GTSD out TYPES.RefCurs_GTSD " defined in the Java program that calls the Pl/SQL ? Thanks a lot in advance . Andrey Bronfin VisualTop.com +972-3-5275757. ------_=_NextPart_001_01C01C0E.980AE1C6 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

An=20 example below - the PL/SQL function is defined as = follows:

type groupRecType is record=20 (
  group_id groups.id%type,
  group_name= groups.name%type,
  group_desc groups.descriptio= n%type
);
type=20 userGroupsCurType is ref cursor return = groupRecType;

function getUserGroups (userID in = number,=20 domainID in number,
hierarchy in integer DEFAULT = 0) return=20 userGroupsCurType;

The=20 Java:

String stmt =3D ("begin = ?=20 :=3DgroupPkg.getUserGroups(?,?,1); end;");

// Prepare the statement = and bind the=20 variables that go in the where clause

cs =3D = c.prepareCall(stmt);

cs.registerOutParameter = (1,=20 OracleTypes.CURSOR);

....

// Execute the query and get the = result set=20 back

cs.execute();

rs =3D=20 ((OracleCallableStatement)cs).getCursor(1);

Hope=20 this helps,

Diana

-----Original Message-----
From: Andrey Bronfin=20 [mailto:bronfin@visualtop.com]
Sent: Sunday, September 10, = 2000 1:26=20 PM
To: Multiple recipients of list = ORACLE-L
Subject:=20 Returning REF cursor type variable to a calling Java program=20

Dear list !

I need to return a REF CURSOR type = value from a=20 PL/SQL stored procedure to a calling Java program .

We problem is that we don't know = what datatype=20 should be used in the calling Java program ?

I define :

create or replace package types as =
CURSOR=20 get_topic_some_data_c1 is select topic_id,topic_name,resource_id from = topic=20 ;
TYPE RefCurs_GTSD IS REF CURSOR RETURN = get_topic_some_data_c1%ROWTYPE ;=20
end types ;
/

create or replace procedure = get_topic_some_data (=20 v_parent_id in number , v_out_RefCurs_GTSD out TYPES.RefCurs_GTSD ) =
as=20
BEGIN
open v_out_RefCurs_GTSD for select=20 topic_id,topic_name,resource_id from topic where PARENT_ID =3D = v_parent_id=20 ;
END get_topic_some_data ;
/

That is , the Java program that = calls the PL/SQL=20 stored proc , should be able to "understand" the

2nd parameter of the stored proc , = that is , the=20 parameter of data type : TYPES.RefCurs_GTSD .

How can we implement this = ?

How should be the data type = of "=20 v_out_RefCurs_GTSD out TYPES.RefCurs_GTSD " defined in the Java = program that=20 calls the Pl/SQL ?

Thanks a lot in advance = .

Andrey=20