File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes getting ResultSet from Java Stored Procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "getting ResultSet from Java Stored Procedure" Watch "getting ResultSet from Java Stored Procedure" New topic
Author

getting ResultSet from Java Stored Procedure

A Sundaram
Greenhorn

Joined: Jan 08, 2003
Posts: 7
Hi, I'm running a Java Stored Procedure on an Oracle Database. I want to call the Stored Proc. from a Java application.
I'm able to do so and return values, however I want to be able to return a result set. Is this possible in Java Stored Procedures?
If so, do i have to use Oracle Cursors?
I thought about having my stored proc. simply return a result set, but then how would i publish it (what sql type does a result set map to)?
Thanks.
Jason Menard
Sheriff

Joined: Nov 09, 2000
Posts: 6450
Your stored proc needs to return a cursor. I usually do something like:
package

package body

HTH
A Sundaram
Greenhorn

Joined: Jan 08, 2003
Posts: 7
But in my Stored Procedure Class, I'd return a resultset object?
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Hi Sundaram,
Jason has given you PL/SQL code for a database stored procedure that returns a "result set". A more complete example is available here:
http://osi.oracle.com/~tkyte/ResultSets/index.html
Hope this helps you.
Good Luck,
Avi.
A Sundaram
Greenhorn

Joined: Jan 08, 2003
Posts: 7
Hey Avi, thanks. I've actually already looked at that website. I understand that when i publish the procedure, I should return an Oracle cursor. The issue that I'm having is that I'm using a Java Stored Procedure:
public static ResultSet spResultSet()
{
ResultSet rset;
try
{
Connection conn =
DriverManager.getConnection("jdbc efault:connection:");
String sql = "select * from mytable_name";
Statement stmt = conn.createStatement();
rset = stmt.executeQuery(sql);
return rset;

}catch (SQLException e)
{
System.err.println(e.getMessage());
return null;
}
}
Is my code correct for returning a resultset? Im using Oracle 8i, i read somewhere that Java Stored Procs can only return cursors/resultsets in 9i. Is that true? thanks a lot for your help.
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
From the java client side you can pick up either as resultset type?:

stmt.registerOutParameter(x,oracle.jdbc.driver.OracleTypes.CURSOR);
ResultSet rs = (ResultSet) stmt.getObject(x);
What error message do you get?
Jason Menard
Sheriff

Joined: Nov 09, 2000
Posts: 6450
Originally posted by A Sundaram:
Hey Avi, thanks. I've actually already looked at that website. I understand that when i publish the procedure, I should return an Oracle cursor. The issue that I'm having is that I'm using a Java Stored Procedure:
public static ResultSet spResultSet()
{
ResultSet rset;
try
{
Connection conn =
DriverManager.getConnection("jdbc efault:connection:");
String sql = "select * from mytable_name";
Statement stmt = conn.createStatement();
rset = stmt.executeQuery(sql);
return rset;

}catch (SQLException e)
{
System.err.println(e.getMessage());
return null;
}
}
Is my code correct for returning a resultset? Im using Oracle 8i, i read somewhere that Java Stored Procs can only return cursors/resultsets in 9i. Is that true? thanks a lot for your help.

A "Java Stored Procedure" is something different. Java Stored Procedures are Java code stored in the database that is accessed like a stored procedure. In 99% of situations, I would say a normal PL/SQL stored procedure is the way to go.
In your Java app of course you must write code to call that stored proc, be it a normal stored procedure or a Java stored procedure. The code I gave you is the PL/SQL code for a stored procedure which returns a cursor (as far as your concerned, a result set). The code you gave looks like code for your java application which will call a stored proc in oracle, reading the result of that stored proc into a ResultSet.
Additionally, you have the code in a method which returns the ResultSet. There is nothing wrong with having a method in your java app which returns a result set, but it is not necessary. It would just depend on how you wanted to architect your app.
A Sundaram
Greenhorn

Joined: Jan 08, 2003
Posts: 7
The code i gave was actually the Java Stored Procedure itself.
I've published it as follows:
CREATE OR REPLACE PACKAGE types
AS
TYPE rs_cursor IS REF CURSOR;
END;
CREATE OR REPLACE FUNCTION CURRENCIES_SPRESULTSET
return rs_cursor
AS LANGUAGE JAVA
NAME 'Currencies.spResultSet() return java.sql.ResultSet';
It publishes fine.
Then i call the stored proc from my java application as follows:
cs = db_connection.prepareCall("{? = call CURRENCIES_PACKAGE.CURRENCIES_SPRESULTSET()}");
cs.registerOutParameter(1,OracleTypes.CURSOR);

cs.execute();
ResultSet rs = (ResultSet)cs.getObject(1);
I get a fatal error when running cs.execute. I'm about to try it on Oracle 9i, i'll let you know what happens. but is what I'm trying ok?
Jason Menard
Sheriff

Joined: Nov 09, 2000
Posts: 6450
Sorry I wasn't understanding you at first. Check out the links off of these pages and see if anything points you in the right direction.
Java Stored Procedures- Sample Corner
Java Center - OracleJVM and Java Stored Procedures
A Sundaram
Greenhorn

Joined: Jan 08, 2003
Posts: 7
thanks for the links. none of the documentation i find out there gives me examples of what I need to do. I ran it on Oracle 9i and i get mismatched types error because my Java stored procedure returns a ResultSet and my java app says:
cs.registerOutParameter(1,OracleTypes.CURSOR);
So apparently my stored procedure should be returning something else. but what? I'm still not sure if it's even possible to return a result set from a java Stored Proc. because I haven't seen it done elsewhere yet.
Jason Menard
Sheriff

Joined: Nov 09, 2000
Posts: 6450
Just as a style note, from what I understand it is preferable to use normal PL/SQL based stored procs over java stored procs when possible. We had initially started using java stored procs in certain places on one project I had worked on, but decided it was more trouble than it was worth and kept the Java on the application side and used regular PL/SQL stored procs on the Oracle side.
A Sundaram
Greenhorn

Joined: Jan 08, 2003
Posts: 7
I want to use Java Stored Procs. so that it doesn't matter whether I'm using Oracle or Sybase, my actual Java code stays the same (as opposed to PL/SQL and Transact SQL)
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: getting ResultSet from Java Stored Procedure
 
Similar Threads
Oracle stored procedures
Resultset returned by stored proc not forward only
Stored Procedure Synchronization
getting the resultset of a stored procedure?
Oracle Java Stored Procedure ... Urgent help needed.