Is it possible to return a result set from a Java Stored Procedure?
In Oracle release 8.1.7 and earlier you cannot return a result set back from a Java stored procedure, though you can do so from a PL/SQL stored procedure. Oracle release 9.0.1 and later permit you to return a result set from a Java Stored Procedure. You have to open the result set by executing a SQL statement. (There is an Oracle-specific API to enable this server-side functionality – please refer to the JDBC Developer’s Guide and Reference.) You can optionally retrieve rows from it in the server-side Java code. Finally, you pass the result set with the remaining rows on it from the Java stored procedure as a REF CURSOR OUT parameter or return. REF CURSORs returned from Stored Procedures lose scrollability “An Oracle Stored Procedure returns a REF CURSOR to client Java code as a java.sql.ResultSet. The problem is that the scrollability of the ResultSet is lost – it becomes a TYPE_FORWARD_ONLY ResultSet.” Result set scrollability is not supported on result sets returned from a stored pr
