SQLSyntaxErrorException: ORA-00911: invalid character

Question

I'm trying to INSERT records to an Oracle DB using PreparedStatement but I only get this error. At this point, my effort to overcome it far outweighs my progress so another set of eyes might help. Where is the invalid character?

A lot of what I've found suggests that a trailing ";" inside of your sql String can be the culprit, but I haven't had one in my statement from the outset.

My connection itself, which works perfectly at several other places in the program:

Properties props = new Properties();
props.setProperty( "user", username );
props.setProperty( "password", password );
props.setProperty( "defaultRowPrefetch", "10" );
props.setProperty( "defaultBatchValue", "10" );
props.setProperty( "processEscapes", "false" );

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Connection conn = DriverManager.getConnection(DB_URL_SVC, props);

The way I'd like to get it done (except that I would wrap it in a method that accepts three Strings) but it throws a SQLSyntaxErrorException

String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( ? , ? , ? )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.setString(1, "JHT");
preStatement.setString(2, "USA");
preStatement.setInt(3, 2500);
preStatement.executeUpdate();    // ORA-00911: invalid character error

This works, but defeats the purpose of using PreparedStatement since the parameters are hard-coded:

String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( 'JHT' , 'USA' , '2500' )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.executeUpdate();

Works. However, I understand that concatenating variables with single and double quotes also isn't really the best way because PreparedStatement should alleviate us from having to deal with that part of the syntax:

String value1 = "JHT";
String value2 = "USA";
int value3 = 2500;
String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( '" + value1 + "', '" + value2 + "', '" + value3 + "' )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.executeUpdate();

Fails with a SQLSyntaxErrorException. So even if code quotation syntax myself, I'm still unable to place those variables in the preStatement.setString(), which would at least allow a little flexibility.

String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( ? , ? , ? )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.setString(1, "' + value1 + '");
preStatement.setString(2, "' + value2 + '");
preStatement.setInt(3, "' + value3 + '");
preStatement.executeUpdate();    // ORA-00911: invalid character error

Fails. Enclosing the placeholders in my String with single quotes results in SQLException.

String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( '?' , '?' , '?' )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.setString(1, "JHT");
preStatement.setString(2, "USA");
preStatement.setInt(3, 2500);
preStatement.executeUpdate();    // invalid column index

Fails. Enclosing the two String (but not the int) placeholders in my String with single quotes results in SQLException.

String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( '?' , '?' , ? )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.setString(1, "JHT");
preStatement.setString(2, "USA");
preStatement.setInt(3, 2500);
preStatement.executeUpdate();    // invalid column index

This doesn't fail, but doesn't write to database either (even though I haven't disabled auto-commit).

String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( ? , ? , ? )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.setString(1, "JHT");
preStatement.setString(2, "USA");
preStatement.setInt(3, 2500);
preStatement.executeBatch();

I've also tried all matter of escapes with backslashes, double backslashes, backticks, quitsies, no-startsies, erasies, double-stamps, and tofus-make-it-true! Maybe somebody out there knows the voodoo that will help me?!

In order to use executeBatch(), you need to call addBatch() (for each row) before. I guess then it'll fail, too. — skirsch
What strikes me is that your third parameter is an int, but in your working examples, you use single-quotes, too. What happens if you leave them out? If that doesn't work either, I assume your COST column is VARCHAR, too. — skirsch
Ah yes, I forgot to include that case in my original post. Using executeBatch() was more of a shot in the dark that I tried because I had set a batchValue in my properties object. In this case the user can only update a single record at a time. But including addBatch() just above executeBatch() still results in BatchUpdateException: ORA-00911: invalid character. — es0329
The COST column is a NUMBER but removing the single-quotes throws ORA-00911. — es0329
You mean, executing "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( 'JHT' , 'USA' , 2500 )" results in an ORA-00911, too? Could you please alter your first (original) snippet to this: preStatement.setString(3, "2500"); and try again? — skirsch

Luke Woodward Luke Woodward · Accepted Answer · 2013-03-29T23:03:38

Is there a reason why you have props.setProperty( "processEscapes", "false" );?

I believe that this turns off the ability to use ? as bind parameter placeholder. I believe that if escape processing is enabled, JDBC does some 'magic' with the ? placeholders before passing the SQL string to Oracle. Otherwise, the ? character is sent to the database as-is.

There are occasional uses for disabling escape processing. I used it in a previous answer to a question involving ? characters in passwords. I believe it can be disabled at the connection or the statement level; to re-enable escape processing on a PreparedStatement, try calling preStatement.setEscapeProcessing(true);. I would expect the first of your failing examples to succeed with this option set.

As for your failing examples, those with unescaped ?s will cause problems as ? is not a valid character in SQL. Surrounding ? in single quotes turns it into a single-character string, so it wouldn't be a bind parameter even if escape processing is enabled. I can't say why the last one doesn't write to the database.

SQLSyntaxErrorException: ORA-00911: invalid character

1 Answers