my dog learned polymorphism*
The moose likes JDBC and the fly likes cannot bind variables to upsert query using preparedstatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "cannot bind variables to upsert query using preparedstatement" Watch "cannot bind variables to upsert query using preparedstatement" New topic

cannot bind variables to upsert query using preparedstatement

ishara gunathilake

Joined: Jul 04, 2012
Posts: 3
here is my code. I cant bind variables to this upsert statment. it gives me an error "ORA-01008: not all variables bound" even it has bound all variables. i use ojdbc14 driver.

i hard coded the values. then it works fine. i think the problem having with the inside USING statement. please help me to solve this
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3606

Welcome to the Ranch!

1) I don't know why you're getting the error. I've casually counted your question marks and it seems there are 16 of them, so it is indeed quite strange. However, I'm successfully using the following SQL query with bind variables:

As you can see, I'm binding the values in the USING clause, but nowhere else (I'd say this is the "natural" way of doing it, but I don't see a reason why binding the values elsewhere should not work).

If this approach does not hep or you want to untangle your original problem anyway, please post the stack trace so we can see on which line the error occurs. Make sure the line numbers match the code you've posted.

2) You definitely should use a newer Oracle JDBC driver, unless you're still on JDK 1.4. See Which version of JDBC driver shall I use?.

3) Make sure you've turned autocommit off if you're using batch updates. Also, without the 11g version of the driver the standard JDBC batching is not very effective.

4) This is not related to your issue, but you don't need to stuff the schema name into your SQL queries. You can set the default schema using ALTER SESSION SET CURRENT_SCHEMA = schema .
I agree. Here's the link:
subject: cannot bind variables to upsert query using preparedstatement
Similar Threads
'&' missing while displaying Textbox values in JSP/jquery/javascript
String.split( ) very confusing !!!!!
Servlet hang when performing to much insert function
Collection to read data from a file and store it in memory???