After a horrible afternoon dealing with the Oracle Universal Installer and the Oracle Database Creation Assistant (both of them are serious contenders for the “shitware of the year” 2004 awards), I thought I’d throw in a little tip for dealing with SQLExceptions as thrown by Oracle.
There are 2 types of SQLException that the average Java-Oracle developer can encounter:
- the ORA-xxxxx variant
- the (DESCRIPTION=(TMP=)(VSNNUM=…)(ERR=…) variant
and there are (usually) 3 software components involved:
- a servlet container, or an application server
- the Oracle JDBC driver
- the Oracle database
In the ORA-xxxxx variant, the query issued by the first component (say, a web application running on Tomcat) was processed by the JDBC driver and passed to the database. The error message is produced by the database. Trying to solve the problem by modifying a Java class or a JSP page is pointless, so what does a smart developer do? He looks at the server log files and extracts the offending query, then connects to the Oracle database with SQL*Plus: surprise, surprise, pasting the query in SQL*Plus produces the exact same error message. After correcting the problem where the problem is, the same smart developer now ports the fixed query to the application server, and all is well.
The (DESCRIPTION=(TMP=)(VSNNUM=…)(ERR=…) variant instead usually means that the query has never reached the database. In this case the smart developer should concentrate on the application server and JDBC settings: maybe the wrong version of the JDBC driver is on the CLASSPATH, maybe the OCI (type-2) driver cannot find the native client libraries, and so on. The important point is that in this case is useless to go to the database!
For the ORA-xxxxx variant, there is a lookup facility on the Oracle Tahiti web site.