Oracle stored procedures returning Java resultsets

First .. create some test data in the database

/*
** Create a table for testing
*/
--DROP TABLE EMPLOYEE CASCADE CONSTRAINTS ; 

CREATE TABLE EMPLOYEE ( 
  EMPLOYEE_ID     NUMBER (4)    NOT NULL, 
  LAST_NAME       VARCHAR2 (15), 
  FIRST_NAME      VARCHAR2 (15), 
  MIDDLE_INITIAL  VARCHAR2 (1), 
  JOB_ID          NUMBER (3), 
  MANAGER_ID      NUMBER (4), 
  HIRE_DATE       DATE, 
  SALARY          NUMBER (7,2), 
  COMMISSION      NUMBER (7,2), 
  DEPARTMENT_ID   NUMBER (2), 
  PRIMARY KEY ( EMPLOYEE_ID ) ) ; 

CREATE INDEX EMPLOYEE_IX01 ON EMPLOYEE(HIRE_DATE);

INSERT INTO EMPLOYEE ( EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, COMMISSION, DEPARTMENT_ID ) VALUES ( 
7369, 'SMITH', 'JOHN', 'Q', 667, 7902,  TO_Date( '12/17/1984', 'MM/DD/YYYY') , 800, NULL, 20); 
INSERT INTO EMPLOYEE ( EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, COMMISSION, DEPARTMENT_ID ) VALUES ( 
7499, 'ALLEN', 'KEVIN', 'J', 670, 7698,  TO_Date( '02/20/1985', 'MM/DD/YYYY') , 1600, 300, 30); 
INSERT INTO EMPLOYEE ( EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, COMMISSION, DEPARTMENT_ID ) VALUES ( 
7505, 'DOYLE', 'JEAN', 'K', 671, 7839,  TO_Date( '04/04/1985', 'MM/DD/YYYY') , 2850, NULL, 13); 
INSERT INTO EMPLOYEE ( EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, COMMISSION, DEPARTMENT_ID ) VALUES ( 
7506, 'DENNIS', 'LYNN', 'S', 671, 7839,  TO_Date( '05/15/1985', 'MM/DD/YYYY') , 2750, NULL, 23); 
INSERT INTO EMPLOYEE ( EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, COMMISSION, DEPARTMENT_ID ) VALUES ( 
7507, 'BAKER', 'LESLIE', 'D', 671, 7839,  TO_Date( '06/10/1985', 'MM/DD/YYYY') , 2200, NULL, 14); 
INSERT INTO EMPLOYEE ( EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, COMMISSION, DEPARTMENT_ID ) VALUES ( 
7521, 'WARD', 'CYNTHIA', 'D', 670, 7698,  TO_Date( '02/22/1985', 'MM/DD/YYYY') , 1250, 500, 30); 
INSERT INTO EMPLOYEE ( EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, COMMISSION, DEPARTMENT_ID ) VALUES ( 
7555, 'PETERS', 'DANIEL', 'T', 670, 7505,  TO_Date( '03/31/1985', 'MM/DD/YYYY') , 1250, 300, 13); 
COMMIT;

Next ... create the PL/SQL package

/*
** Create packaged procedure
*/
CREATE OR REPLACE PACKAGE Adodemo_Pkg IS
    TYPE resultset_typ IS REF CURSOR;

    -- Procedure to show the return of a ref cursor appearing as an ADO recordset in vb
    PROCEDURE GetEmployees(i_id          IN  EMPLOYEE.employee_id%TYPE   -- number input
                          ,i_hiredate_s  IN  EMPLOYEE.hire_date%TYPE     -- date input
                          ,i_hiredate_f  IN  EMPLOYEE.hire_date%TYPE     -- date input
                          ,o_resultset   OUT resultset_typ               -- 'recordset' output
                          ,o_error       OUT VARCHAR2                    -- error output
                          );

END Adodemo_Pkg;
/
CREATE OR REPLACE PACKAGE BODY Adodemo_Pkg IS
    -- Procedure to show the return of a ref cursor appearing as an ADO recordset in vb
    PROCEDURE GetEmployees(i_id          IN  EMPLOYEE.employee_id%TYPE   -- number input
                          ,i_hiredate_s  IN  EMPLOYEE.hire_date%TYPE     -- date input
                          ,i_hiredate_f  IN  EMPLOYEE.hire_date%TYPE     -- date input
                          ,o_resultset   OUT resultset_typ               -- 'recordset' output
                          ,o_error       OUT VARCHAR2                    -- error output
                         ) IS
    BEGIN
        -- If inputs are NULL then all rows are returned
        OPEN o_resultset FOR
          SELECT    employee_id, last_name, hire_date, salary, commission
          FROM      EMPLOYEE
          WHERE     employee_id = NVL(i_id,employee_id)
          AND       hire_date <= NVL(i_hiredate_s,hire_date)
          AND       hire_date >= NVL(i_hiredate_f,hire_date);
          
        o_error := NULL;
    EXCEPTION
        WHEN OTHERS THEN o_error := SQLERRM;  
    END GetEmployees;
END Adodemo_Pkg;
/

If necessary use this script to test the package from SQL*Plus

/*
** Test script
**
** In SQL*Plus: SET SERVEROUTPUT ON before running
*/
DECLARE 
  i_id NUMBER;
  i_hiredates DATE;
  i_hiredatef DATE;
  o_resultset  Adodemo_Pkg.resultset_typ;
  o_error VARCHAR2(256);
  
  TYPE employee_rec_typ IS RECORD (
    employee_id EMPLOYEE.employee_id%TYPE,
    name        EMPLOYEE.LAST_NAME%TYPE,
    hire_date   EMPLOYEE.hire_date%TYPE,
    salary      EMPLOYEE.salary%TYPE,
    commission  EMPLOYEE.commission%TYPE
  );
  employee_rec employee_rec_typ;
  
BEGIN 
  --i_id := 7555;
  i_hiredates := TO_DATE('01011985','DDMMYYYY');
  i_hiredatef := TO_DATE('31031985','DDMMYYYY');

  Adodemo_Pkg.getemployees ( i_id, i_hiredates, i_hiredatef, o_resultset, o_error );
  dbms_output.put_line('Error:' || NVL(o_error,'NULL'));

  LOOP
     FETCH o_resultset INTO employee_rec ;
     EXIT WHEN o_resultset%NOTFOUND;
     dbms_output.put_line(employee_rec.employee_id || ':' || 
                          employee_rec.name || ':' ||
                          employee_rec.hire_date);
  END LOOP;
  CLOSE o_resultset;
  
END;

Compile and run the following java program

import java.sql.*;
import oracle.jdbc.driver.*;

class JDBCDemo {
    public static void main (String args []) throws SQLException {

        CallableStatement stmt;
        ResultSet rset;
        ResultSetMetaData rsmd;
        int noColumns;
        int i;
        Date startHiredate = new Date(0);        
        Date endHiredate = new Date(0);

        // Load Oracle driver
        DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
        
        // Connect to the local database
        Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:HOME8I","demo", "demo");

        // Set up the stored procedure call
        stmt = conn.prepareCall("Begin ADODEMO_PKG.GetEmployees(?,?,?,?,?); End;");

        // Set the values for the input parameters
        // stmt.setInt(1, 7555); // Uncomment this to pass the value 7555
        stmt.setNull(1, Types.NUMERIC); // Pass a NULL for the employee id
        // stmt.setNull(2, Types.DATE); // Uncomment this to pass a NULL;
        stmt.setDate(2, startHiredate.valueOf("1985-01-01"));
        // stmt.setNull(3, Types.DATE); // Uncomment this to pass a NULL;
        stmt.setDate(3, endHiredate.valueOf("1985-03-31"));

        // Register the output parameters - we use the specific Oracle types
        stmt.registerOutParameter(4, OracleTypes.CURSOR);
        stmt.registerOutParameter(5, OracleTypes.CHAR);

        // Make the call and retrieve the resultset. To get a resultset from the
        // REF CURSOR we cast the CallableStatement to an OracleCallableStatement
        stmt.execute();
        rset = ((OracleCallableStatement)stmt).getCursor(4);

        // Get column names from the resultset
        rsmd = rset.getMetaData();
        noColumns = rsmd.getColumnCount();
        System.out.println("Resultset contains " + noColumns + " columns");
        for (i = 1; i < noColumns; i++) {
            System.out.print(rsmd.getColumnName(i) + ", ");
        }
        System.out.println(rsmd.getColumnName(i));

        // Print the values returned - note the default date format contains the time
        while (rset.next ()) {
            for (i = 1; i < noColumns; i++) {
                System.out.print(rset.getString(i) + ", ");
            }
            System.out.println (rset.getString (i));
        }

        // Explicitly close the resultset, statement and connection
        rset.close();
        stmt.close();
        conn.close();
    }
}