Oracle is a decent product but there are some things I loathe about it. The biggest gripe I have with Oracle, and it is a BIG issue I have with it is the following:
Caused by: java.sql.SQLException: Invalid column name
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:229)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:403)
at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3366)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:2009)
at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:494)
at com.mchange.v2.c3p0.impl.NewProxyResultSet.getString(NewProxyResultSet.java:3342)
at org.hibernate.type.StringType.get(StringType.java:18)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:189)
at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.extract(CustomLoader.java:474)
at org.hibernate.loader.custom.CustomLoader$ResultRowProcessor.buildResultRow(CustomLoader.java:420)
at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:317)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:606)
at org.hibernate.loader.Loader.doQuery(Loader.java:701)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 46 more
Can someone please put the guy that crafted this error message to the wall and shoot him? If you discover an invalid column name, then TELL me which one that was. Queries can span 100+ column names, and it is neigh impossible to figure out which one is invalid.
And this is not the only place where Oracle really is subpar in its error messages. Any of the invalid identifier messages doesn’t provide you with context. A multi-thousand dollar license product that can’t give me decent error messages is not worth a penny in my opinion. Oracle should be ashamed of itself and make a public apologie for billions lost revenue of their customers. Now go and stand in the corner!
+1 with you.
I guess that why they make dollars cause it really needs a dedicated person to handle that scary thing .
I usually end up setting a breakpoint for the thrown exception, then going back up the stack to try and find the variable which indicates the invalid column.
Of course, that doesn’t help at all when all you have is the log file from a production server…
IBM’s DB at least provides some context, but no actual error message, instead only an error code that you have to lookup somewhere else. I’m not sure whats worse.
Right complaint, wrong victim. As far as I can tell, the Oracle RDBMS product does tell you which column is invalid. If you paste your query into sqlplus (or whatever your dba tool of choice is) then you can see it.
SQL> select cow_face from user_table;
select cow_face from user_table
*
ERROR at line 1:
ORA-00904: “COW_FACE”: invalid identifier
The one that needs to be shot appears to be the JDBC driver because it’s throwing away very useful information.
Nice complaint!
But you can also complaint about :
- the “data truncation” on jtds driver (for sql server) that does not give you the name of the column where the data is truncated.
- Worse, much much worse, I had – and I still have -t lots of fights with jsf that throws an exception but does not say ANYTHING! Not a word!
For DB2 you can use retrieveMessagesFromServerOnGetMessage property.
So right, Oracle’s JDBC sucks. We lost values due to BigDecimal+Java5 bug in Oracle drivers. And if you try to put too large values into columns, you just get a “Value too large” without the column name or the datatype or anything.
Yeah, oracle is certainly like MS aren’t they? They can not improve but still be on top…
James
Ahmen brother.
http://www.oracle.com/technology/tech/java/sqlj_jdbc/pdf/11.1%20logging%20white%20paper.pdf
@Matt: great, now we can log stuff. How does that improve the *#$@#$ exception?
Command line invoked :-
java -classpath .;ojdbc14_g.jar -Djava.util.logging.config.file=log.properties -Doracle.jdbc.Trace=true Test
log.properties :-
oracle.jdbc.driver.level=INFO
oracle.jdbc.driver.handlers=java.util.logging.ConsoleHandler
java.util.logging.ConsoleHandler.formatter = java.util.logging.SimpleFormatter
Test.java:-
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;
public class Test
{
public static void main(String[] args)
{
String url = “jdbc:oracle:thin:@//test:1521/orcl”;
String user = “scott”;
String password = “tiger”;
try
{
OracleDataSource ods = new OracleDataSource();
ods.setURL(url);
ods.setUser(user);
ods.setPassword(password);
OracleConnection conn = (OracleConnection) ods.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(“select * from session_roles”);
while (rs.next())
{
System.out.println(rs.getString(“role”));
System.out.println(rs.getString(“invalidcolumn”));
}
rs.close();
stmt.close();
}
catch (SQLException se) { throw new RuntimeException(se); }
}
}
Output :-
RESOURCE
15/05/2009 10:03:22 oracle.jdbc.driver.PhysicalConnection setAutoCommit
INFO: PhysicalConnection.setAutoCommit(autoCommit=true)
15/05/2009 10:03:22 oracle.jdbc.driver.PhysicalConnection setAutoCommit
INFO: PhysicalConnection.setAutoCommit(autoCommit): return
15/05/2009 10:03:22 oracle.jdbc.driver.PhysicalConnection getDefaultFixedString
INFO: PhysicalConnection.getDefaultFixedString() returning false
15/05/2009 10:03:22 oracle.jdbc.driver.OracleStatement executeQuery
INFO: OracleStatement.executeQuery(sql) needToPrepareDefineBuffer = true
15/05/2009 10:03:23 oracle.jdbc.driver.T4CStatement allocateTmpByteArray
SEVERE: oracle.jdbc.driver.T4CStatement.allocateTmpByteArray : Re-allocate byte array of size : 120
15/05/2009 10:03:23 oracle.jdbc.driver.OracleResultSetImpl findColumn
INFO: OracleResultSetImpl.findColumn(columnName=role)
15/05/2009 10:03:23 oracle.jdbc.driver.OracleResultSetImpl getString
INFO: OracleResultSetImpl.getString(columnIndex=1)
15/05/2009 10:03:23 oracle.jdbc.driver.OracleResultSetImpl findColumn
INFO: OracleResultSetImpl.findColumn(columnName=invalidcolumn)
15/05/2009 10:03:23 oracle.jdbc.driver.DatabaseError findMessage
WARNING: DatabaseError.findMessage(errNum, obj): returned Invalid column name
How about “ORA-01461: can bind a LONG value only for insert into a LONG column”? Got this when inserting more characters than the column-width allowed. Then PLEASE tell me the column-width is too small, or my variable too large, and don’t give me such a crap errormessage.
It look like some sort of a PostgreSQL promlem I had few months ago. I lokalized issue in my c3p0 and Hibernate configuration. May you should look there before you change all the code.