Re: Help - getting a RowSet from Oracle Function

"The Nomad" <nobody_at_nowhere.com> wrote in message news:5s4z6.40178$z4.6873526_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. 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...)
>
> And how do I get a rowset back from a stored function/procedure in Oracle?
 I
> saw another sample that used the refCursor by making oracle-specific
> references (Oracle.Driver.OracleTypes.CURSOR and other stuff) - I need a
> generic solution that works for all without having a bunch of "SELECT *
 FROM
> <aTable> WHERE ..." in the Java code.
>
> TIA
>
 

> Marc
>
>

Your assertion a stored procedure can't return a resultset is simply untrue. Period.
If I read exactly the same documentation I will be using procedure(c1 in out REF CURSOR)
and that's all there is to it.
Secondly, in your function you use a strongly typed cursor, which you assign to a weakly type cursor in your anonymous block. I expect that won't work exactly because of this reason. Try changing the cursor definition in your package to a REF CURSOR without type.

Hth,.

Sybrand Bakker, Oracle DBA

>
>
>
Received on Thu Apr 05 2001 - 15:47:11 CDT