Re: pl/sql refcursor

Jeff Smith wrote:

> My point is that to return result sets from stored procedures, Oracle's
> documentation indicates REF CURSORS.
>
> You and I know there may be a number of ways to accomplish many things. But
> nowhere that I am aware of does Oracle say "do not use REF CURSORS".
>
> Jeff
> "Daniel Morgan" <damorgan@
> xesolutions.com> wrote in message news:3D141AF9.8A4D8B91_at_exesolutions.com...
> > Jeff Smith wrote:
> >
> > > I don't necessarily like it, but result sets from stored procedures and
> > > functions are achieved by Ref Cursors says Oracle. Below is an excerpt
> from
> > > their document. I know there are performance penalties.
> > > I would be very interested in hearing how you say it should be done.
> > >
> > > Regards,
> > > Jeff
> > >
> > > Oracle8i JDBC Developer's Guide and Reference
> > > Release 3 (8.1.7)
> > > Part Number A83724-01
> > >
> > >
> http://gwynne.cs.ualberta.ca/~oracle/817doc/java.817/a83724/samapp5.htm#10
> > > 16411 )
> > >
> > > REF CURSORs--RefCursorExample.java
> > > This sample program shows Oracle JDBC REF CURSOR functionality, creating
> a
> > > PL/SQL package that includes a stored function that returns a REF CURSOR
> > > type. The sample retrieves the REF CURSOR into a result set object. For
> > > information on REF CURSORs, see "Oracle REF CURSOR Type Category".
> > >
> > > /*
> > > * This sample shows how to call a PL/SQL function that opens
> > > * a cursor and get the cursor back as a Java ResultSet.
> > > */
> > >
> > > import java.sql.*;
> > > import java.io.*;
> > > import oracle.jdbc.driver.*;
> > >
> > > class RefCursorExample
> > > {
> > > public static void main (String args [])
> > > throws SQLException
> > > {
> > > // Load the driver
> > > DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
> > >
> > > // Connect to the database
> > > // You can put a database name after the @ sign in the connection
> URL.
> > > Connection conn =
> > > DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott",
> "tiger");
> > >
> > > // Create the stored procedure
> > > init (conn);
> > >
> > > // Prepare a PL/SQL call
> > > CallableStatement call =
> > > conn.prepareCall ("{ ? = call java_refcursor.job_listing (?)}");
> > >
> > > // Find out all the SALES person
> > > call.registerOutParameter (1, OracleTypes.CURSOR);
> > > call.setString (2, "SALESMAN");
> > > call.execute ();
> > > ResultSet rset = (ResultSet)call.getObject (1);
> > >
> > > // Dump the cursor
> > > while (rset.next ())
> > > System.out.println (rset.getString ("ENAME"));
> > >
> > > // Close all the resources
> > > rset.close();
> > > call.close();
> > > conn.close();
> > >
> > > }
> > >
> > > // Utility function to create the stored procedure
> > > static void init (Connection conn)
> > > throws SQLException
> > > {
> > > Statement stmt = conn.createStatement ();
> > >
> > > stmt.execute ("create or replace package java_refcursor as " +
> > > " type myrctype is ref cursor return EMP%ROWTYPE; " +
> > > " function job_listing (j varchar2) return myrctype;
> " +
> > > "end java_refcursor;");
> > >
> > > stmt.execute ("create or replace package body java_refcursor as " +
> > > " function job_listing (j varchar2) return myrctype is "
> +
> > > " rc myrctype; " +
> > > " begin " +
> > > " open rc for select * from emp where job = j; " +
> > > " return rc; " +
> > > " end; " +
> > > "end java_refcursor;");
> > > stmt.close();
> > > }
> > > }
> > > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> > > news:itrugus0f2m15i0g8tn87r5t97or3triad_at_4ax.com...
> > > > On Tue, 18 Jun 2002 09:47:52 -0400, "Sylvain Dumont"
> > > > <sylvain_dumont_at_karat.com> wrote:
> > > >
> > > > >do something like that.!!
> > > > >
> > > > >CREATE OR REPLACE PACKAGE KSP_PLSQLRSETPKG
> > > > > AS
> > > > >TYPE RCT1 IS REF CURSOR;
> > > > >END;
> > > > >;
> > > > >
> > > > >the first parameter "oraclebug" is to bypass the oracle bug doesn't
> > > accept a
> > > > >string for the first parameters
> > > > >when in the odbc tab sqlserver migration is on
> > > > >
> > > > >CREATE OR REPLACE PROCEDURE KSP_TEST
> > > > > ( ORACLEBUG INT DEFAULT 0,
> > > > >RC1 IN OUT KSP_PLSQLRSETPkg.RCT1,
> > > > >RC2 IN OUT KSP_PLSQLRSETPkg.RCT1
> > > >
> > > >
> > > > REF CURSORs should be OUT parameters.
> > > > Do you think when you know so little about Oracle, you are justified
> > > > to shout 'bug' in all your posts. This is just crappy code and nothing
> > > > more.
> > > > If you think it is a bug, why don't you submit an Itar? No Support
> > > > Contract, I guess. I have been wading on your behalf through Metalink
> > > > and couldn't find any reference to your 'bug'.
> > > > I now see you are just running crappy code. A ref cursor is a cursor,
> > > > and you can't assign it a string parameter. PERIOD.
> > > > Please stop posting your unfounded accusations and start learning
> > > > Oracle.
> > > >
> > > > Regards
> > > >
> > > >
> > > > Sybrand Bakker, Senior Oracle DBA
> > > >
> > > > To reply remove -verwijderdit from my e-mail address
> >
> > I would have to know much more to make such a recommendation. I don't like
> > REF CURSORS in systems with many simultaneous users. But don't like
> doesn't
> > mean they aren't the right tool for the job in many cases.
> >
> > I'd try OPEN FOR and then return the REF CURSOR if no performance problems
> > ensued. Otherwise SELECT COUNT and report back when more than one record
> was
> > retrieved as an error.
> >
> > Or, perhaps better yet, clean up the referential integrity so multiple
> records
> > can not exist.
> >
> > Daniel Morgan
> >

You are correct. REF CURSORS are the prefered method of accomplishing the goal. But they come with a price just as everything in any system involves trade-offs.

My comment was only intended to make you aware of where the price is paid so that you could perform further research if warranted.

Daniel Morgan Received on Mon Jun 24 2002 - 10:18:10 CDT