| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
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_at_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
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =charset=3Diso-8859-1">
<META content=3D"MSHTML 5.00.3017.1000" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#d4d0c8> <DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =class=3D703083716-11092000>An=20
<P><FONT size=3D2><FONT color=3D#0000ff><FONT face=3DArial><SPAN=20 class=3D703083716-11092000>....</SPAN></FONT></FONT></FONT></P> <P><FONT size=3D2><FONT color=3D#0000ff><FONT face=3DArial><SPAN=20class=3D703083716-11092000></SPAN>// Execute the query and get the = result set=20
<BR><BR></DIV></FONT> <DIV><FONT face=3DArial size=3D2>Dear list !</FONT></DIV> <DIV><FONT face=3DArial size=3D2>I need to return a REF CURSOR type =value from a=20
<DIV> </DIV> <DIV><FONT face=3DArial size=3D2>I define : </FONT></DIV> <DIV> </DIV> <DIV><FONT face=3DArial size=3D2>create or replace package types as =<BR>CURSOR=20
<BR>end types ; <BR>/ </FONT></DIV> <DIV> </DIV> <DIV><FONT face=3DArial size=3D2>create or replace procedure =get_topic_some_data (=20
;<BR>END get_topic_some_data ;<BR>/</FONT></DIV> <DIV> </DIV> <DIV> </DIV> <DIV><FONT face=3DArial size=3D2>That is , the Java program that =calls the PL/SQL=20
 |
 |