Call Oracle Stored Function from Java

Returning CURSOR to Result Set

 

See http://www.enterprisedt.com/publications/oracle/result_set.html for full explanation

 

Oracle Create GLOBAL Weak Cursor Type

CREATE OR REPLACE PACKAGE Types
AS
    TYPE ref_cursor IS REF CURSOR;
END;

Oracle Stored Function

CREATE OR REPLACE FUNCTION sf_DemoCursor(v_credits IN NUMBER)
    RETURN Types.ref_cursor
AS
    student_cursor types.ref_cursor;
BEGIN
    OPEN student_cursor FOR
      SELECT * FROM student
      WHERE tot_cred < v_credits;
   
    RETURN student_cursor;
END;

 

Java Demo to Call Oracle Stored Function

package db;

 

import java.sql.*;

import java.io.*;

import oracle.jdbc.driver.*;

 

public class StoredFunCursor {

 

  /**

   *  Compile-time flag for deciding which query to use

   */

  private boolean useOracleQuery = true;

 

  /**

   *  Class name of Oracle JDBC driver

   */

  private String driver = "oracle.jdbc.driver.OracleDriver";

 

  /**

   *  Initial url fragment

   */

  private String url = "jdbc:oracle:thin:@";

 

 

  /**

   *  Standard Oracle listener port

   */

  private String port = "1521";

 

 

  /**

   *  Oracle style of calling a stored procedure

   */

  private String oracleQuery = "begin ? := sf_DemoCursor(?); end;";

 

 

  /**

   *  JDBC style of calling a stored procedure

   */

  private String genericQuery = "{ call ? := sp_get_stocks(?) }";

 

 

  /**

   *  Connection to database

   */

  private Connection conn = null;

 

 

  /**

   *  Constructor. Loads the JDBC driver and establishes a connection

   *

   *  @param  host        the host the db is on

   *  @param  db          the database name

   *  @param  user        user's name

   *  @param  password    user's password

   */

  public StoredFunCursor(String host, String db, String user, String password)

      throws ClassNotFoundException, SQLException {

 

      // construct the url

      url = url + host + ":" + port + ":" + db;

 

      // load the Oracle driver and establish a connection

      try {

          Class.forName(driver);

          conn = DriverManager.getConnection(url, user, password);

      }

      catch (ClassNotFoundException ex) {

          System.out.println("Failed to find driver class: " + driver);

          throw ex;

      }

      catch (SQLException ex) {

          System.out.println("Failed to establish a connection to: " + url);

          throw ex;

      }

  }

 

 

  /**

   *  Execute the stored procedure

   *

   *  @param   credits  price parameter for stored procedure

   */

  private void execute(int credits)

      throws SQLException {

 

      String query = useOracleQuery ? oracleQuery : genericQuery;

      System.out.println("Query: " + query + "\n");

      CallableStatement stmt = conn.prepareCall(query);

 

      // register the type of the out param - an Oracle specific type

      stmt.registerOutParameter(1, OracleTypes.CURSOR);

 

      // set the in param

      stmt.setInt(2, credits);

 

      // execute and retrieve the result set

      stmt.execute();

      ResultSet rs = (ResultSet)stmt.getObject(1);

 

      // print the results

      while (rs.next()) {

          System.out.println(  rs.getInt(1) + "\t" +

                                        rs.getString(2) + "\t" +

                               rs.getString(3) + "\t" +

                               rs.getInt(4));

      }

 

      rs.close();

      stmt.close();

  }

 

 

  /**

   *  Cleanup the connection

   */

  private void cleanup() throws SQLException {

 

      if (conn != null)

          conn.close();

  }

 

 

  /**

   *  Prints usage statement on stdout

   */

  static private void usage() {

 

      System.out.println("java com.enterprisedt.demo.oracle.JDBCDemo " +

          " host db user password price");

  }

 

 

  /**

   *  Runs the class

   */

  public static void main(String[] args) throws Exception {

 

      if (args.length != 5) {

         StoredFunCursor.usage();

          System.exit(1);

      }

      else {

          try {

              // assign the args to sensible variables for clarity

              String host = args[0];

              String db = args[1];

              String user = args[2];

              String password = args[3];

              int credits = Integer.valueOf(args[4]).intValue();

 

              // and execute the stored proc

              StoredFunCursor jdbc = new StoredFunCursor(host, db, user, password);

              jdbc.execute(credits);

              jdbc.cleanup();

          }

          catch (ClassNotFoundException ex) {

              System.out.println("Demo failed");

          }

          catch (SQLException ex) {

              System.out.println("Demo failed: " + ex.getMessage());

          }

      }

  }

}