wood burning stoves 2.0*
The moose likes JDBC and the fly likes JDBC, Stored Procedure and Rowtype Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC, Stored Procedure and Rowtype" Watch "JDBC, Stored Procedure and Rowtype" New topic
Author

JDBC, Stored Procedure and Rowtype

Wai Iu
Ranch Hand

Joined: Apr 04, 2000
Posts: 39
I use jdbc to connect to Oracle 8.1.5 database. In my program,
I have a sentence:
CallableStatement cstmta=conna.prepareCall("begin selectall(?); end;");
It use a callablestatement to call a stored procedure-selectall.
The procedure selectall is:
create or replace procedure
selectall (allrow OUT employee_types%ROWTYPE)
as
begin select * into allrow from employee_types;
employee_types is a table in database.
My question are:
1)how could I associate datatype with the OUT parameter of the stored procedure? What will be xxxx in
"cstmta.registerOutParameter(1,xxxx);"?
2)how could I retrieve a Resultset from the CallableStatement?
Thanks!
Wai

Carl Trusiak
Sheriff

Joined: Jun 13, 2000
Posts: 3340
You can try
cstmta.registerOutParameter(1,java.sql.Types.OTHER);
....
ResultSet rs = (ResultSet)cstmta.getObject(1);
Hope this helps.


I Hope This Helps
Carl Trusiak, SCJP2, SCWCD
parthasarathy madhira
Ranch Hand

Joined: Aug 31, 2001
Posts: 41
Hi...I used the same
and that doesnt work any suggestions on the same.
Thanks
Partha
Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
Dear Madhira.
I've just crawled through a page that answers your (and also my) problem with Procedures that returns//accept rowTypes.
I attach you the part that worths:
(taken from Akadia)

Stored Function with Record Set
The PL/SQL package java_demo defines the procedure list_emp(), using the IN parameter p_job and the result parameter as ResultSet, analogous to the attributes of the table EMP (schema scott/tiger). The ResultSet contains all the employees matching the defined job.

This example shows the easy and smart way how to open and to use a cursor defined in
the PL/SQL package. Important in this case is the definition of the public cursor type:
TYPE myreftype IS REF CURSOR RETURN emp%ROWTYPE;
FUNCTION list_emp(p_job VARCHAR2) RETURN myreftype;
[ December 13, 2002: Message edited by: Angel Lacal L�pez ]
[ December 13, 2002: Message edited by: Angel Lacal L�pez ]
Rickson gracie
Greenhorn

Joined: May 19, 2004
Posts: 1
But why wouldn't the example work where Types.other and casting it as a resultset using (ResultSet) getObject(1) work??? Do you HAVE to use OracleTypes.CURSOR? Thanks
Rashid Darvesh
Ranch Hand

Joined: Feb 13, 2004
Posts: 189
is there a possibility to return only first three rows in the cursor. Looks like in the above example the entire cursor is returned.
 
wood burning stoves
 
subject: JDBC, Stored Procedure and Rowtype