Help! Getting a ResultSet from an Oracle Function not working
Date: Thu, 05 Apr 2001 11:54:11 GMT
Message-ID: <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:
SSSLOG_SOURCES is defined in MSSQL as follows:
CREATE PROC SSSLOG_GETSOURCES
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();
}
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;
/
/*
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
I can see that it works and it's there.
exec :b := SSSLOG_GETSOURCES
print :b
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 Thu Apr 05 2001 - 13:54:11 CEST