Re: Help - getting a RowSet from Oracle Function

I too wanted a generic answer, but started from sql server.

The reasons I chose to use stored procedures was firstly to hide the details of tables and columns which might change in the future, and secondly to hide differences in syntax - as far as I know ora 8i doesn't support ansi outer joins.

My mistake was that I half remembered using ref cursors from oracle some time ago, but didn't recall the detail.

I have tried an Ora8i function that returns a ref cursor - appears to work in sqlplus. But isn't handled by the oracle odbc driver.

Obviously the use of SPs for dml should mean procedures with similar signatures in both sql7 and ora8i - but the resultset issue is a bit of a stopper.

Cheers
GJW "Jim Kennedy" <kennedy-family_at_home.com> wrote in message news:KbmH6.86195$xN4.5935463_at_news1.sttls1.wa.home.com...
> I think his problem is that he wants to be very generic, as I am sure you
> understand. Part of his problem is that he started with sqlserver and its
> concurrency model leaves much to be desired (which is why he is using
 stored
> procedures) in comparison to Oracle's concurrency model. So the real
> problem is that he is interpreting "generic" as sqlserver and DB2
 specific.
> (DB2 suffers from a bad concurrency model also.)
>
> If he really wanted to be generic he would have used ansi sql. (select ...
> from ...)
> Jim
>
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> news:ter18tdvti523c_at_beta-news.demon.nl...
> > 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 Tue May 01 2001 - 03:50:51 CDT