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)

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.

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?

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.

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

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.

try:
ResultSet rs;
rs = cs.getResultSet();
while(rs.next()){
//do whatever you want to on the result set
}