Re: Stored Procedures and Result Sets

From: Gary SMITH <Gary.Smith_at_cern.ch>
Date: Fri, 15 May 1998 10:23:36 +0200
Message-ID: <355BFB88.38598EAA@cern.ch>

Here's a posting from a while back re: JDBC and result sets

---

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
---------------------------------------------------------------------------

tlp_at_redrockgrp.com wrote:

> I'm not sure how helpful this is but... I know that in Visual
>         Basic you can use the logic you describe utilizing VB RDO objects
>         (specifically rdo queries and resultsets).
>
>         Maybe there's something similar in JDBC?
>
>         Tracy           tlp_at_redrockgrp.com
>
> Thomas Boyd wrote:
>
> >
> > This question is more appropriate for a java/jdbc forum, but I thought
> > that the
> > more general question of returning result sets from PL/SQL procedures
> > might get answered here:I
> >
> > I am attempting to write JDBC calls to Oracle procedures that include
> > Oracle PL/SQL arrays (Oracle actually calls them PL/SQL tables).
> >
> > The package specification might look like:
> >
> >     create or replace package foo_pkg  is
> >         type num_arr  is table of number        index by binary_integer;
> >
> >         procedure foo_proc (p_number_array    in out    num_arr);
> >     end foo;
> >
> > Can I call this procedure from JDBC?  What datatype/object will be
> > passed for
> > p_number_array?
> >
> > This really relates to the problem that we have logic that is too
> > complicated
> > to embed in a query or a view, and we cannot return a result set from an
> >
> > Oracle PL/SQL procedure.  If we cannot use arrays, does anyone have an
> > alternative solution for returning multiple rows from an Oracle
> > procedure?
> >
> > Thanks,
> >    Tom Boyd

--

---

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

---

Received on Fri May 15 1998 - 03:23:36 CDT