import  oracle.jdbc.OracleTypes;

 import  java.sql.*;
/**
 * Oracle Callable statement test  
 * <p>
 * Oracle stored procedure:
 * <code>
 * create or replace function get_emp (p_empno number) return sys_refcursor as
 * v_result sys_refcursor;
 * begin
 *   open v_result for
 *     select e.*
 *       from scott.emp e
 *       where p_empno is null or e.empno = p_empno;
 *   return v_result;
 * end;
 * /
 * <code>
 */

 public   class  JDBCCallTest {
   private   static   final  String USERNAME = <username>;
   private   static   final  String PASSWORD = <password>;
   private   static   final  String URL = "jdbc:oracle:thin:@<host>:<port>:<SID>";
   private   final   static  String SQL_GET_EMP = "{? = call get_emp(?)}";


   public   static   void  main(String args[])
     throws  SQLException {
    Connection conn =  null ;
    CallableStatement cst =  null ;
    ResultSet rs =  null ;
     try  {
//      Class.forName("oracle.jdbc.OracleDriver").newInstance();
      DriverManager.registerDriver( new  oracle.jdbc.OracleDriver());
      conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);

      cst = conn.prepareCall(SQL_GET_EMP);
      cst.registerOutParameter( 1 , OracleTypes.CURSOR);
      // All rows
      cst.setString( 2 ,  null );
      // Single row
//      cst.setInt(2, 7788);
      cst.execute();
      rs = (ResultSet) cst.getObject( 1 );
       while  (rs.next()) {
        System.out.println("empno=" + rs.getString("empno") + ", ename=" + rs.getString("ename"));
      }
    }  finally  {
       if  (rs !=  null ) { rs.close(); }
       if  (cst !=  null ) { cst.close(); }
       if  (conn !=  null ) { conn.close(); }
    }
  }
}