Escaping & in SQL

SQL *PLUS treats the '&' in queries as a substitution variable by default.
If you are using SQL Developer, you might get an unexpected resposne if you have '&' in your query.
It prompts you for a value for the string that follows. For example if you have a query like

Select * from emp where name = 'Carrol & Scott',

you will get a pop up a message like 'enter value for scott' or something like that.

In such cases, turn off substitution by using 'SET DEFINE OFF' or you can tell SQL *PLUS to use a different character for substitution like 'SET DEFINE $'

I didn't have this problem with SQL Navigator or JDBC. So, it's not a problem with Oracle.

java.sql.SQLException: Unsupported feature

This week I hit a problem when my application tried to write a blob to a Database.
I use container managed persistence with Websphere. It turned out the ojdbc jar at the location pointed to by the websphere variable - "ORACLE_JDBC_DRIVER_PATH" - needed to be updated to ojdbc14 which you can pick up from the Oracle lib folder.

This is part of the exception stack trace:

java.sql.SQLException: Unsupported feature
at oracle.jdbc.dbaccess.DBError.throwSqlException
(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException
(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException
(DBError.java:269)
at oracle.jdbc.dbaccess.DBError.
throwUnsupportedFeatureSqlException(DBError.java:689)
at oracle.sql.BLOB.setBinaryStream(BLOB.java:1007)

Note: Make sure you pick up this jar from the Oracle library. Other versions of ojdbc14 might not solve this problem.

ORA-00942: table or view does not exist

Apart from the obvious cause of this error (mistake in the sql query), this could also happen if the user account used to connect to the DB, doesn't have the necessary privileges to access the table or view in the query.

java.io.IOException: Start of root element expected.

I hit a problem when passing an InputStream object to a parser today.

java.io.IOException: Start of root element expected.

Cause: The InputSteam object had been read prior to passing it to the DomParse. Seems obvious now, but its easy to miss this if you happen to be passing data to be parsed from some other method. (especially if you want to verify the contents of the InputStream)