File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC, Stored Procedure and Rowtype

 
Wai Iu
Ranch Hand
Posts: 39
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3340
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can try
cstmta.registerOutParameter(1,java.sql.Types.OTHER);
....
ResultSet rs = (ResultSet)cstmta.getObject(1);
Hope this helps.
 
parthasarathy madhira
Ranch Hand
Posts: 41
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi...I used the same
and that doesnt work any suggestions on the same.
Thanks
Partha
 
Anonymous
Ranch Hand
Posts: 18944
  • 0
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 189
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
is there a possibility to return only first three rows in the cursor. Looks like in the above example the entire cursor is returned.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic