Re: Help - getting a RowSet from Oracle Function

I'm trying to do a similar thing - return a resultset from an oracle stored procedure/function.

The problem I see with using 'proc1|(c1 in out ref cursor)' is that: 1) I have a different definition than an ms msql/db2 procedure (where I just need proc1())
2)I have to pass in a ref cursor that gets returned - again something oracle specific.

I need to do the equivalent of {call proc1()} and get back a result set.

By changing the odbc driver I want to be able to use either sql7 or oracle8i .

I'm sure I am missing something obvious. But I ask, how is this achieved in oracle?

Cheers
Gary

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:tcpsvhc93l3vbf_at_beta-news.demon.nl...
>
> "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 Mon Apr 30 2001 - 10:11:55 CDT