Java JDBC Oracle 9i upgrade to 10g issues...

Question

We have just upgrade from oracle 9i to 10g and a database query I have works with the 9i client but not the 10g. Nothing in the query has changed. I get the following error:

java.sql.SQLException: ORA-01036: illegal variable name/number

Not really sure what is going on. Why wouldn't it run anymore. It's just a select statement which joins about 3 or 4 tables. I am making sure that I am passing in the variable using setInt (it's a number that I'm using). While diff'ing the tables being joined the only thing I find different is that on one table a column I'm joining is set to be a Number on one table and Number(12) on the other. Does this make a difference? The query still runs in TOAD and SQL Navigator...

Answer 1

2 thoughts spring to mind:

1. make sure you're using the correct version of the jdbc driver. Since you've said the query works in TOAD etc, this is very likely to be your problem.
2. make sure you're not using any ORACLE key-words in your query as column-aliases etc

Also, from experience if your database is big/busy you've got a fair way to go before your 10g environment is stable. My recommendations:

1. learn as much as you can about stats
2. Read the survival guides (there's lots on the net)
3. watch out for built-in jobs that re-compute status. We got hammered 12 days after go-live because stats changes broke key query performance. Our testing hadn't allowed a 12 day stability test.
4. be aware of bind-variable-peeking if you aren't already

Answer 2

The problem was with the following method call on the prepared statement:

ps.setEscapeProcessing(false);

Removed that and now it works fine. Don't think that was compatable with oracle 10g driver. Thanks everyone for the help.