aspose 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


Win a copy of Java 8 in Action this week in the Java 8 forum!
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
Oracle Java Stored Procedure ... Urgent help needed.
getting the resultset of a stored procedure?
Stored Procedure Synchronization