Re: Help - getting a RowSet from Oracle Function

The description below is just the way it works. If you don't like it, stick to sqlserver.

Regards,

Sybrand Bakker, Oracle DBA

"Gary J. Walker" <gjw1992_at_hotmail.com> wrote in message news:3aed8190$1_at_news.star.co.uk...
>
> 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:32:45 CDT