Processing SQL Exceptions

To handle error conditions, the Oracle JDBC drivers throws SQL exceptions, producing instances of class java.sql.SQLException or a subclass. Errors can originate either in the JDBC driver or in the database (RDBMS) itself. Resulting messages describe the error and identify the method that threw the error. Additional run-time information can also be appended.

Basic exception-handling can include retrieving the error message, retrieving the error code, retrieving the SQL state, and printing the stack trace. The SQLException class includes functionality to retrieve all of this information, where available.

Errors originating in the JDBC driver are listed with their ORA numbers in Appendix A, "JDBC Error Messages".

Errors originating in the RDBMS are documented in the Oracle8i Error Messages reference.

Retrieving Error Information

You can retrieve basic error information with these SQLException methods:

• getMessage()

  For errors originating in the JDBC driver, this method returns the error message with no prefix. For errors originating in the RDBMS, it returns the error message prefixed with the corresponding ORA number.

• getErrorCode()

  For errors originating in either the JDBC driver or the RDBMS, this method returns the five-digit ORA number.

• getSQLState()

  For errors originating in the JDBC driver, this returns no useful information. For errors originating in the RDBMS, this method returns a five-digit code indicating the SQL state. Your code should be prepared to handle null data.

The following example prints output from a getMessage() call.

catch(SQLException e);
{
   System.out.println("exception: " + e.getMessage());
}

This would print output such as the following for an error originating in the JDBC driver:

exception: Invalid column type

(There is no ORA number message prefix for errors originating in the JDBC driver, although you can get the ORA number with a getErrorCode() call.)

Note: Error message text is available in alternative languages and character sets supported by Oracle.

Printing the Stack Trace

The SQLException class provides the following method for printing a stack trace.

• printStackTrace()

  This method prints the stack trace of the throwable object to the standard error stream. You can also specify a java.io.PrintStream object or java.io.PrintWriter object for output.

The following code fragment illustrates how you can catch SQL exceptions and print the stack trace.

try { <some code> } 
catch(SQLException e) { e.printStackTrace (); } 
 

To illustrate how the JDBC drivers handle errors, assume the following code uses an incorrect column index:

// Iterate through the result and print the employee names 
// of the code 
 
try { 
  while (rset.next ()) 
      System.out.println (rset.getString (5));  // incorrect column index
}
catch(SQLException e) { e.printStackTrace (); } 
 

Assuming the column index is incorrect, executing the program would produce the following error text:

java.sql.SQLException: Invalid column index 
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:235) 
at oracle.jdbc.driver.OracleStatement.prepare_for_new_get(OracleStatemen 
t.java:1560) 
at oracle.jdbc.driver.OracleStatement.getStringValue(OracleStatement.jav 
a:1653) 
at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:175 
) 
at Employee.main(Employee.java:41)

---