Resolving ORA-00911 Invalid Character Error: Common Pitfalls and Optimization Strategies in JDBC PreparedStatement

Problem Background and Error Phenomenon

In Oracle database development, ORA-00911: invalid character is a common error code typically indicating unrecognized characters in SQL statements. This paper analyzes this error through a practical case: a developer encountered this error when attempting to insert multiple CLOB data into a database using JDBC's PreparedStatement, even though the same SQL statement executed successfully in SQL Developer.

Root Cause Analysis

After thorough investigation, the core issue lies in the handling of semicolon characters. In JDBC calls, SQL strings passed to the prepareStatement() method should not include trailing semicolons. This is because the JDBC driver automatically handles statement termination, and additional semicolons are treated as invalid characters.

The original query string was:

insert all
into domo_queries values (?)
select * from dual;

The trailing semicolon here caused the ORA-00911 error. While removing the semicolon avoided the invalid character error, it triggered ORA-00933: SQL command not properly ended, indicating the need to reconsider the entire SQL statement structure.

Solutions and Code Optimization

1. Simplify SQL Statement Structure

The original code used INSERT ALL statements for multi-row insertion, but for single-row insertion, standard INSERT statements are more concise and efficient. Even for multiple rows, batch operations are superior to complex INSERT ALL statements.

2. Eliminate Unnecessary CLOB Processing

The original code's creation of temporary CLOB objects was both complex and error-prone. JDBC provides simpler methods for handling large text data:

String sql = "insert into domo_queries (clob_column) values (?)";
PreparedStatement pstmt = con.prepareStatement(sql);
StringReader reader = new StringReader(query1);
pstmt.setCharacterStream(1, reader, query1.length());
pstmt.addBatch();

3. Implement Batch Operations

For inserting multiple CLOB data, batch processing can significantly improve performance:

// Prepare first CLOB data
StringReader reader = new StringReader(query1);
pstmt.setCharacterStream(1, reader, query1.length());
pstmt.addBatch();

// Prepare second CLOB data
reader = new StringReader(query2);
pstmt.setCharacterStream(1, reader, query2.length());
pstmt.addBatch();

// Execute batch
pstmt.executeBatch();
con.commit();

Best Practice Recommendations

1. Avoid including semicolons in JDBC SQL strings: This is the primary principle for preventing ORA-00911 errors.

2. Prefer standard INSERT statements: Avoid complex INSERT ALL syntax unless specifically required.

3. Utilize JDBC advanced features: Such as setCharacterStream() for CLOB data handling and batch operations for performance improvement.

4. Maintain code simplicity: Remove unnecessary temporary object creation and complex data conversion logic.

Conclusion

While the ORA-00911 error superficially appears as a character encoding issue, it often reflects deeper SQL statement structure or JDBC usage problems. Through the analysis and solutions presented in this paper, developers can not only resolve current errors but also master more optimized database operation methods. The key is understanding JDBC's working principles, avoiding direct copying of SQL statements executable in tools into code, and making appropriate adjustments based on programming interface characteristics.