aspose file tools*
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.
 
Don't get me started about those stupid light bulbs.
 
subject: JDBC, Stored Procedure and Rowtype