others-How to solve ORA-00911 invalid character problem

1. The purpose of this post

Sometimes, when we copy and paste sql scripts from sqlplus or plsql to java programs, we get this exception or error like this:

ORA-00911: invalid character found in sql...

2. Solution

2.1 What is ORA-00911

The ORA-00911 error is an Oracle Database error code that indicates an issue with the SQL statement being executed. Specifically, it signifies that an “invalid character” has been encountered in the SQL statement. This error typically occurs when the database parser finds a character or sequence of characters that it does not recognize as part of the SQL language syntax.

The error message associated with ORA-00911 provides additional information about the nature of the problem. It includes the exact location of the invalid character within the SQL statement, which can help in identifying and correcting the error. Common causes of this error include:

  1. Syntax Errors: Using characters or keywords that are not part of the SQL syntax.
  2. Special Characters: Including special characters or symbols that are not supported in the context where they are used.
  3. Non-Printable Characters: The presence of non-printable characters, possibly introduced from foreign programs or due to copy-paste errors.
  4. Incorrect Delimiters: Using incorrect or additional punctuation, such as a semicolon (;) at the end of a SQL statement when it is not expected or required.

To resolve the ORA-00911 error, one must carefully review the SQL statement and make sure that it adheres to the correct syntax and does not contain any unsupported characters. If the error persists, it may be necessary to check for hidden or non-printable characters that could be causing the issue.

Here are some steps to troubleshoot and fix the ORA-00911 error:

  • Review the SQL Statement: Carefully examine the SQL statement for any typos or incorrect characters.
  • Check for Hidden Characters: Sometimes, hidden or non-printable characters can be the cause. Use tools to reveal these characters and remove them.
  • Remove Unnecessary Delimiters: If a semicolon or another delimiter is causing the issue, remove it from the end of the SQL statement.
  • Consult Documentation: Refer to Oracle’s documentation for the correct syntax and usage of SQL statements.
  • Test in Isolation: Try running the SQL statement in a different environment or tool to see if the error persists.

For more detailed information and solutions, you can refer to various resources, including official Oracle documentation and community forums where developers share their solutions to similar problems.

2.2 The solution

Check if your sql has semicolon at the end of your sql.

My old sql:

insert   into  tablea(aaa)   values('111');

My new sql:

insert   into  tablea(aaa)   values('111')

Run the sql , everything works fine now.