• Post Reply Bookmark Topic Watch Topic
  • New Topic

JDBC, Stored Procedure and Rowtype

 
Wai Iu
Ranch Hand
Posts: 39
  • 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: 3341
  • 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
  • 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
  • Mark post as helpful
  • 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
  • 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
  • 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.
 
What are you doing? You are supposed to be reading this tiny ad!
the new thread boost feature brings a LOT of attention to your favorite threads
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!