FileDocCategorySizeDatePackage
RefCursorExample.javaAPI DocExample1853Wed Sep 17 13:50:38 BST 1997None

RefCursorExample

public class RefCursorExample extends Object

Fields Summary
Constructors Summary
Methods Summary
static voidinit(java.sql.Connection conn)

    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;");
public static voidmain(java.lang.String[] args)

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