getting ResultSet from Java Stored Procedure

[A Sundaram]

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]

Your stored proc needs to return a cursor. I usually do something like:
package

type GenericCurTyp is ref cursor;

package body

function get_record(p_record_id in records.record_id%TYPE)
  return GenericCurTyp is
  gc GenericCurTyp;
begin
  open gc for
    select *
      from records
      where record_id = p_record_id;
  return gc;
end get_record;

HTH

[A Sundaram]

But in my Stored Procedure Class, I'd return a resultset object?

[Avi Abrami]

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]

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]

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]

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]

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]

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]

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]

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]

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)