How to use ref cursor to get multiple rows and retrieving them in Java?

Question

The problem statement is like this :

I have to create a procedure to fetch all the data (only 9 records actually) in Employees1 table and then display that data by calling this procedure in Java. I am working on a legacy application that is built on struts 1.3 but that should not be of any concern here i guess.

Below is the procedure I created:

CREATE OR REPLACE PROCEDURE getALLEmployees(ref_cur out sys_refcursor)
IS
BEGIN
OPEN ref_cur FOR 'SELECT * FROM employees1';
END;

And here is my Java Code:

callableStatement = conn.prepareCall("{call getAllEmployees()}");

// Below line is (EmployeeDataJdbc.java:33) as mentioned in error
callableStatement.registerOutParameter(1,oracle.jdbc.driver.OracleTypes.CURSOR); 

boolean isResultSet = callableStatement.execute(); 

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

        /*Employee paramater Order in DB
        employee_id, employee_first_name, employee_last_name, employee_address, 
        employee_blood_group, employee_email, employee_department, employee_role, 
        employee_band, employee_mobile_number*/

        while(rs.next()){
            emp.setEmployeeId(rs.getInt(1));
            emp.setEmployeeFirstName(rs.getString(2));
            emp.setEmployeeLastName(rs.getString(3));
            emp.setEmployeeAddress(rs.getString(4));
            emp.setEmployeeBloodGroup(rs.getString(5));
            emp.setEmployeeEmail(rs.getString(6));
            emp.setEmployeeDepartment(rs.getString(7));
            emp.setEmployeeRole(rs.getString(8));
            emp.setEmployeeBand(rs.getString(9));
            emp.setEmployeeMobileNumber(rs.getLong(10));

            employeeList.add(emp);
        }

This is the error I am getting at console:

Connected to Database

java.sql.SQLException: Invalid column index
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
    at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:121)
    at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:268)
    at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:348)
    at jdbcHandles.EmployeeDataJdbc.getEmployeeList(EmployeeDataJdbc.java:33)

I have not used PL-SQL since a long long time. Please help me find out where I am going wrong. Thanks in advance.

Note: I am using Oracle 10G express edition as database.

Answer 1

  callableStatement = conn.prepareCall("{call getAllEmployees(?)}");

You have to mention where the out parameter has to be binded using the ?

And since the column count can change. Better have a check using the Metdatadata call rSet.getColumnCount()