Easy to Learn Java: Programming Articles, Examples and Tips

Start with Java in a few days with Java Lessons or Lectures

Home

Code Examples

Java Tools

More Java Tools!

Java Forum

All Java Tips

Books

Submit News
Search the site here...
Search...
 

Using PL/SQL Ref Cursors with JDBC Java code example - Click here to copy ->>>

   Can't find what you're looking for? Try our search:

Really working examples categorized by API, package, class. You can compile and run our examples right away! Not from source code for Java projects - only working examples! Copy, compile and run!
------------------

Code:

 
// -----------------------------------------------------------------------------
// RefCursorExample.java
// -----------------------------------------------------------------------------

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Types;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.jdbc.driver.OracleResultSet;


/**
 * -----------------------------------------------------------------------------
 * This class provides an example on the use of REF Cursors to execute SQL from
 * a JDBC program, simulating dynamic SQL.
 *
 * ===================
 * DYNAMIC SQL IN JAVA
 * ===================
 *
 * JDBC provides APIs for executing Dynamic SQL using PreparedStatement.
 * For example:
 *
 *      PreparedStatement pstmt;
 *      pstmt=conn.prepareStatement("SELECT name FROM dept WHERE deptno > ?");
 *      pstmt.setInt(1,104);
 *      ResultSet c1;
 *      c1=pstmt.executeQuery();
 *      pstmt.setInt(1,10)
 *      while (c1.next ()) {System.out.println (c1.getInt(1));}
 *
 *
 * ==========
 * REF CURSOR
 * ==========
 *
 * Another option of executing dynamic SQL from JDBC is provided in this
 * example. Keep in mind that this example will only work with Oracle8i and
 * higher. In this case, the  procedure uses a PL/SQL procedure which returns
 * a REF CURSOR.
 *
 * A REF CURSOR is similar a pointer in the C programming language. It points
 * to rows retrieved from the database using a PL/SQL cursor. The example I
 * provide in this class uses a REF CURSOR to point to the result set
 * returned by a SELECT statement that retrieves rows from the DEPT table
 * using a PL/SQL cursor.
 *
 * In this example, I call a PL/SQL procedure named "get_dept_ref_cursor" which
 * returns a variable of type "t_ref_cursor".
 *
 * Stored procedures can return user-defined types, or cursor variables, of the
 * REF CURSOR category.  This output is equivalent to a database cursor or a
 * JDBC result set. A REF CURSOR essentially encapsulates the results of a
 * query.
 *
 * Advantages of using a REF CURSOR are:
 *
 *      1.) Code Reusability
 *     
 *          The same package procedure could be used for other Java and non-Java
 *          applications.
 *         
 *      2.) Load Balancing.
 *
 *
 * =============================
 * OracleCallableStatement CLASS
 * =============================
 *
 * You will notice that in this example, I use an OracleCallableStatement class
 * in place of our typical CallableStatement class. This class defines a method
 * named getCursor() that enables you to read Oracle cursors.
 *
 *
 * =================
 * OracleTypes CLASS
 * =================
 *
 * You will also notice the oracle.jdbc.driver.OracleTypes is also used
 * when registering the OutParameter. This class defines those special TYPEs
 * offered by the Oracle database. This class is similar to java.sql.Types.
 *
 *
 * ===========================================================
 * NOT USING OracleCallableStatement and OracleResultSet CLASS
 * ===========================================================
 *
 * Note that you are not required to use the OracleCallableStatement and
 * OracleResultSet classes; you could use the regular CallableStatement
 * and ResultSet classes found in java.sql. However, you will need to
 * use the getObject() method to read the Oracle cursor. An example of this is
 * provided in this example with the performRefCursor2() method.
 *
 *
 * -----------------------------------------------------------------------------
 *
 * NOTE: Opening a REF CURSOR for a statement present in a variable is only
 *       supported with Oracle8i and higher.
 *
 * NOTE: In order to successfully use this class, you will need to run the
 *       create_all_ddl.sql file included in the same section this example class
 *       is located.
 *
 * -----------------------------------------------------------------------------
 */

public class RefCursorExample {

    final static String driverClass    = "oracle.jdbc.driver.OracleDriver";
    final static String connectionURL  = "jdbc:oracle:thin:@localhost:1521:CUSTDB";
    final static String userID         = "scott";
    final static String userPassword   = "tiger";
    Connection   con                   = null;


    /**
     * Construct a RefCursorExample object. This constructor will create an Oracle
     * database connection.
     */
    public RefCursorExample() {

        try {

            System.out.print("  Loading JDBC Driver  -> " + driverClass + "\n");
            Class.forName(driverClass).newInstance();

            System.out.print("  Connecting to        -> " + connectionURL + "\n");
            this.con = DriverManager.getConnection(connectionURL, userID, userPassword);
            System.out.print("  Connected as         -> " + userID + "\n\n");

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }


    /**
     * This method is used to return a REF CURSOR that will be used to retrieve
     * data from a result set. This REF CUSROR is retrieved by the JDBC program
     * into a ResultSet.
     *
     * This method Uses the OracleCallableStatement and OracleResultSet classes.
     */
    public void performRefCursor() {

        OracleCallableStatement oraCallStmt   = null;
        OracleResultSet         deptResultSet = null;

        System.out.println("Using OracleCallableStatement / OracleResultSet");
        System.out.println("-----------------------------------------------");

        try {

            oraCallStmt = (OracleCallableStatement) con.prepareCall(
                "{? = call ref_cursor_package.get_dept_ref_cursor(?)}"
            );
            oraCallStmt.registerOutParameter(1, OracleTypes.CURSOR);
            oraCallStmt.setInt(2, 104);
            oraCallStmt.execute();

            deptResultSet = (OracleResultSet) oraCallStmt.getCursor(1);

            while (deptResultSet.next()) {
                System.out.println(
                    " - " +
                    deptResultSet.getString(2) + " (" + deptResultSet.getInt(1) + "), " +
                    deptResultSet.getString(3)
                );
            }
            System.out.println();
           
            oraCallStmt.close();
           
        } catch (SQLException e) {

            e.printStackTrace();

        }

    }


    /**
     * This method is used to return a REF CURSOR that will be used to retrieve
     * data from a result set. This REF CUSROR is retrieved by the JDBC program
     * into a ResultSet.
     *
     * This method Uses the the regular CallableStatement and ResultSet classes.
     */
    public void performRefCursor2() {

        CallableStatement cstmt = null;
        ResultSet         rset  = null;

        System.out.println("Using CallableStatement / ResultSet");
        System.out.println("-----------------------------------");

        try {

            cstmt = con.prepareCall(
                "{? = call ref_cursor_package.get_dept_ref_cursor(?)}"
            );
            cstmt.registerOutParameter(1, OracleTypes.CURSOR);
            cstmt.setInt(2, 104);
            cstmt.execute();

            rset = (ResultSet) cstmt.getObject(1);
           
            while (rset.next()) {
                System.out.println(
                    " - " +
                    rset.getString(2) + " (" + rset.getInt(1) + "), " +
                    rset.getString(3)
                );
            }
            System.out.println();
                   
            cstmt.close();
           
        } catch (SQLException e) {

            e.printStackTrace();

        }

    }


    /**
     * Close down Oracle connection.
     */
    public void closeConnection() {

        try {
            System.out.print("  Closing Connection...\n");
            con.close();
           
        } catch (SQLException e) {
       
            e.printStackTrace();
           
        }

    }


    /**
     * Sole entry point to the class and application.
     * @param args Array of String arguments.
     * @exception java.lang.InterruptedException
     *            Thrown from the Thread class.
     */
    public static void main(String[] args)
            throws java.lang.InterruptedException {

        RefCursorExample mainPrg = new RefCursorExample();
        mainPrg.performRefCursor();
        mainPrg.performRefCursor2();
        mainPrg.closeConnection();

    }

}

 



References.

The list of classes which were used on this page you can find below. The links to Java API contain official SUN documentation about all used classes.

  java.sql.ResultSet

  java.sql.PreparedStatement

  java.sql.CallableStatement

  java.sql.Connection

  java.sql.Driver

  java.lang.ClassNotFoundException

  java.lang.InstantiationException

  java.lang.IllegalAccessException

  java.sql.SQLException

  java.lang.reflect.Array

  java.lang.Thread




[ Go Back ]



Home Code Examples Java Forum All Java Tips Books Submit News, Code... Search... Offshore Software Tech Doodling

RSS feed Java FAQ RSS feed Java FAQ News     

    RSS feed Java Forums RSS feed Java Forums

All logos and trademarks in this site are property of their respective owner. The comments are property of their posters, all the rest 1999-2006 by Java FAQs Daily Tips.

Interactive software released under GNU GPL, Code Credits, Privacy Policy