Discrepancy in result returned by the Jdbc method

Question

I can't seem to figure out whats wrong with the code below. The method getSalaryAverageDepartment calls the PL/SQL procedure get_salary_average_dept belonging to the EMP_PGK package in the HR schema.

    public float getSalaryAverageDepartment(int deptId)
    {
        Connection conn =  null;
        CallableStatement callStmt = null;
        Float avgDeptSal = -1f;

        try
        {
            // Register the Jdbc Driver  
            // Class.forName(JDBC_DRIVER_ORACLE);

            // Create a database connection
            conn = DriverManager.getConnection(DB_URL,DB_USER,DB_PWD);

            // Create a SQL String
            String callProc = "{ call HR.EMP_PKG.get_salary_average_dept( ? , ? )}";

            // Create a callable statement
            callStmt = conn.prepareCall(callProc);

            // Bind value to the IN parameter
            callStmt.setInt(1, java.sql.Types.NUMERIC);

            // Register the OUT parameter's type to the SQL type of the return value
            callStmt.registerOutParameter(2, java.sql.Types.FLOAT);

            // Execute the callable statement
            callStmt.execute();

            // Retrieve the OUT parameter
            avgDeptSal = callStmt.getFloat(2);
            System.out.println("Department with Id : "+ deptId + " has average employee salary of : " + avgDeptSal);
        }
        catch (SQLException se) 
        {
            System.out.println("Exception occured in the database");
            System.out.println("Exception message: "+ se.getMessage());
            System.out.println("Dataabse error code: "+ se.getErrorCode());
            se.printStackTrace();
        }
        finally
        {
            // Clean up
            if(callStmt != null)
            {
                try
                {
                    callStmt.close();
                } 
                catch (SQLException se2) 
                {
                    se2.printStackTrace();
                }
            }

            if(conn != null)
            {
                try
                {
                    conn.close();
                } 
                catch (SQLException se2) 
                {
                    se2.printStackTrace();
                }
            }
        }

        return avgDeptSal;
    }

Here id the main method that calls the above code:

public static void main(String[] args) 
{
    HrManager hrMngr = new HrManager();

    int deptId = 80;

    hrMngr.getSalaryAverageDepartment(deptId);
}

Following is the PL/SQL procedure:

  -- Purpose: Returns the average salary of a department with given id
  PROCEDURE get_salary_average_dept(dept_id IN departments.department_id%type, avg_salary OUT FLOAT) IS
  BEGIN
   SELECT AVG(CAST(salary as FLOAT))
   INTO avg_salary
   FROM employees
   WHERE department_id = dept_id;
   dbms_output.put_line('Average salary for department with id : '|| dept_id ||' is : '|| avg_salary);
  EXCEPTION
   WHEN NO_DATA_FOUND THEN
   dbms_output.put_line('No department found with id: '|| dept_id);
   WHEN others THEN
   dbms_output.put_line('Error!');
  END get_salary_average_dept;

Here is the output of the java program:

Department with Id : 80 has average employee salary of : 0.0

I executed the PL/SQL procedure independently in Jdeveloper here is the following output:

Connecting to the database Insight_Dev_Hr.
Average salary for department with id : 80 is : 9000
Process exited.

Why do both outputs differ. Am i missing something here ?

Answer 1

I think your problem is here:

      // Bind value to the IN parameter
      callStmt.setInt(1, java.sql.Types.NUMERIC);

You are binding the value of java.sql.Types.NUMERIC (2) as the value of the first parameter to the stored procedure. Instead, you should do:

      callStmt.setInt(1, deptId);