Re: Help! Getting a ResultSet from an Oracle Function not working

Can the JDBC Drivers access PL/SQL Stored Procedures? Yes, both the Oracle JDBC/OCI Driver and the Thin JDBC Driver support execution of PL/SQL stored procedures and anonymous blocks. and anonymous blocks. They support both SQL92 escape syntax and Oracle escape syntax. The following PL/SQL calls are available from both of Oracle JDBC Drivers: SQL92 Syntax
CallableStatement cs1 = conn.prepareCall ("{call proc (?,?)}"); CallableStatement cs2 = conn.prepareCall ("{? = call func (?,?)}");

Oracle Syntax
CallableStatement cs1 = conn.prepareCall ("begin proc (:1,:2); end;"); CallableStatement cs2 = conn.prepareCall ("begin :1 := func (:2,:3); end;");

The Nomad <nobody_at_nowhere.com> wrote in message news:D1Zy6.35334$Lz6.6094484_at_typhoon.tampabay.rr.com...
> Hey all,
>
> I've been tasked with writing Java that accesses Oracle 8i, MSSQL and DB2
> seamlessly. To hide much of the RDBMS logic, I've written many stored
> procedures whose task it is to retrieve rows and return the rows to the
 Java
> methods. Here is what works for DB2/MSSQL:
>
>
> try {
> CallableStatement stmt = conn.prepareCall("{call
> SSSLOG_GETSOURCES}");
> try {
> ResultSet rs = stmt.executeQuery();
> try {
> while (rs.next()) {
> System.out.println(rs.getInt(1) + ", " + rs.getString(2));
> }
> } finally {
> rs.close();
> }
> } finally {
> stmt.close();
> }
>
> } finally {
> conn.close();
> }
>
>
>
> SSSLOG_SOURCES is defined in MSSQL as follows:
>
> CREATE PROC SSSLOG_GETSOURCES
> AS
> SELECT *
> FROM SSSLOGSOURCES
> ORDER BY LOGSOURCEDESC
> GO
>
>
> In Oracle, I understand (by trying to read the documentation and looking
 at
> various posts), that a Stored Procedure can't return a rowset. But, a
> function can. And, the way to get it to return the data is somehow through
 a
> cursor - like:
>
> CREATE OR REPLACE PACKAGE TYPES
> AS
> TYPE CURSORTYPE IS REF CURSOR RETURN SSSLOGSOURCES%ROWTYPE;
> END;
> /
>
> /*
> *****************
> * Get Log Sources
> *****************
> */
> CREATE OR REPLACE FUNCTION SSSLOG_GETSOURCES RETURN TYPES.CURSORTYPE AS
> c1 TYPES.CURSORTYPE;
> BEGIN
> OPEN c1 FOR SELECT * FROM SSSLOGSOURCES ORDER BY LOGSOURCEDESC;
> RETURN c1;
> END;
> /
>
>
>
> However, when I run through the Java code above, it claims that there is
 no
> such procedure or function. If I change the code to call another stored
> procedure I've written that doesn't return a dataset, it is found. What's
> going on? Also, when I simply invoke it from SQL Plus, SQL Plus complains
> it's not there. But if I use this syntax:
>
> variable b refcursor
> exec :b := SSSLOG_GETSOURCES
> print :b
>
> I can see that it works and it's there.
>
> Why can't the Java App above find it? It can find all the other SPs I've
> written (although the others it finds are CREATE PROCEDURE... and not
 CREATE
> FUNCTION...)
>
> TIA
>
> Marc
>
>
Received on Sat Apr 07 2001 - 05:05:55 CEST