Re: oracle stored procedure and java

From: Gary SMITH <Gary.Smith_at_cern.ch>
Date: 1997/12/19
Message-ID: <349A31D2.8D955D7B@cern.ch>#1/1

Billy,

I received this mail from Oracle. It may interest you.

John Sutton Wrote:

If you have the JDBC drivers with samples available from the Oracle web site, there should be a sample (samples\oci7\RefCursorExample.java) which demonstrates how you can get the ref cursor returned from a PL/SQL procedure and use it as a result set.

(In case you don't have it, here's the code!)

/*
 * 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, ClassNotFoundException   {
// Load the driver

    Class.forName ("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:oci7:@", "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, "SALES");
    call.execute ();

    ResultSet rset = (ResultSet)call.getObject (1);

// Dump the cursor

    while (rset.next ())
      System.out.println (rset.getString ("ENAME"));   }

  // 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;");

  }
}

---

 John Sutton                          New England Development Center
 Oracle System Management Products    internet:  jsutton_at_us.oracle.com
 One Oracle Drive, Office 2012        voice:     (603) 897-3223
 Nashua, NH 03062                     fax:       (603) 897-3317
---------------------------------------------------------------------------


*************************

Gary Smith
CERN, Geneva, Switzerland
Email: Gary.Smith_at_cern.ch
Tel: +41 22 7678944

---

Received on Fri Dec 19 1997 - 00:00:00 CST