Re: Returning a result set from a stored procedure

From: Matz <matz_at_ossis.de>
Date: Fri, 9 Feb 2001 10:32:18 +0100
Message-ID: <960djv$shc$02$1@news.t-online.com>

Hi Tom,

to answer you question, unzip a demo which comes with Oracle (8i):

ora81\jdbc\demo\demo.zip
and then checkout
oci8\basic-samples\RefCursorExample.java

It shows everything: How the procedure(function) must be built and what you have to do with JDBC to get the resultset.

Matz

P.S. Hmm dunno if it's bad style but here's the File so you don't need to search for it:
(anyway it's worth to look at the demos)

RefCursorExample.java

/*
 * 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.*;

// Importing the Oracle Jdbc driver package makes the code more readable import oracle.jdbc.driver.*;

class RefCursorExample
{
  public static void main (String args [])

       throws SQLException
  {
// Load the driver

    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    String url = "jdbc:oracle:oci8:@";
    try {

      String url1 = System.getProperty("JDBC_URL");
      if (url1 != null)
        url = url1;
    } catch (Exception e) {
      // If there is any security exception, ignore it
      // and use the default

    }

// Connect to the database

    Connection conn =
      DriverManager.getConnection (url, "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();
  }
} Received on Fri Feb 09 2001 - 03:32:18 CST