Re: pl/sql refcursor

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
>
Received on Mon Jun 24 2002 - 07:10:19 CDT