Re: pl/sql refcursor

From: Jeff Smith <jsmit234_at_ford.com>
Date: Fri, 21 Jun 2002 11:30:44 -0400
Message-ID: <aevfud$d6318@eccws12.dearborn.ford.com>

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

Received on Fri Jun 21 2002 - 10:30:44 CDT