CODEWITHSUNDEEP
javasqloracle-databasejdbccasting
Nishant
Nishant

Reputation: 1142

Why do I get ORA-01438 error

I have an EMPLOYEE table with column(primary key) EMPLOYEE_ID NUMBER(6)

And the following method to check whether an employee record exists in the table

public boolean exists(int employeeId) {

        Connection con = ConnectionManager.getConnection();
        boolean exists = false;
        String stmt = "select EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER ,"
                + "HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID "
                + " FROM EMPLOYEES where cast (EMPLOYEE_ID as Number(2)) = ?";
        PreparedStatement pstmt = null;
        try {
            pstmt = con.prepareStatement(stmt);
            pstmt.setInt(1, employeeId);
            pstmt.execute(); //Returns true if the first object that the query returns is a ResultSet object
            exists = pstmt.getResultSet().next();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return exists;
    }

Now, in the SQL statement when I use cast (EMPLOYEE_ID as Number(2)) = ? and call the exists method with input 2 employeeOperations.exists(2); I get the following error

ORA-01438: value larger than specified precision allowed for this column

Why does this error occur, even when the size of the column is large enough?

It works well with input of 4 digits or more(maximum length of employee_id present in table is 4) i.e. cast (EMPLOYEE_ID as Number(4)) = ? works.

Upvotes: 0

Views: 369

Answers (1)

user180100
user180100

Reputation:

When casting from NUMBER(6) to NUMBER(2), the records having EMPLOYEE_ID greater than 99 are causing the error (for example Oracle cannot put 100 in \d{2}).

Quick fix, use this request:

SELECT COUNT(1) FROM EMPLOYEES WHERE EMPLOYEE_ID = ?

Upvotes: 2

Related Questions