0

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...

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
user16208
  • 305
  • 1
  • 5
  • 13
  • 1
    NUMBER vs NUMBER(12) isn't the issue; NUMBER(12) just defines the precision after the decimal point. Can't remember what the precision is for NUMBER by default. – OMG Ponies Jun 24 '10 at 19:46
  • Yea I kinda guessed at that, I just threw this out there because this really isn't my area and noone around here has a clue or even an idea of where to begin. thanks. – user16208 Jun 24 '10 at 19:49
  • 1
    Seeing the function might help. – OMG Ponies Jun 24 '10 at 20:11

2 Answers2

0

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
Paul Jowett
  • 6,513
  • 2
  • 24
  • 19
0

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.

user16208
  • 305
  • 1
  • 5
  • 13