Java JDBC Oracle 9i upgrade to 10g issues
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 开发者_如何学Cthe 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...
2 thoughts spring to mind:
- 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.
- 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:
- learn as much as you can about stats
- Read the survival guides (there's lots on the net)
- 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.
- be aware of bind-variable-peeking if you aren't already
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.
精彩评论