ORA-00911 error resolution instance

ORA-00911 error resolution instance

ORA-00911, "911" looked very domineering error number, although I am still a beginner of Oracle, but every time I encounter a not seen ORA error number, there is a kind of catch-up, based on the error code, OERR, and related error information, determine the cause of the error and find a solution or alternative solution. Although most of them may still refer to their predecessors or official counterparts, once they come across, they are at least familiar with each other, even if you don't remember it again, you may have a direction.

Again, this ORA-00911 error was encountered in a test case written in JAVA,

...

Private static final String SQL _INSERT_TBL = "insert into tbl (id ,...)"
+ "Values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE, SYSDATE );";

This error occurs during this SQL statement.

See the error description:

Invalid character ORA-00911
Cause: Special characters are valid only in certain places. if special characters other than $, _, and # are used in a name and the name is not enclosed in double quotation marks ("), this message will be issued. one exception to this rule is for database names; in this case, double quotes are stripped out and ignored.
Action: Remove the invalid character from the statement or enclose the object name in double quotation marks.

Obviously, this error is mainly because the SQL statement contains invalid characters and an error is reported during parsing.

But this SQL statement is very simple. It is an INSERT statement. It seems like it is okay to copy it to plsql developer. It's strange.

After debugging many times, I suddenly found that there was a ";" at the end of the definition. Is that the problem?

So first in plsql developer to execute the INSERT statement, the end with two ";", and indeed reported this ORA-00911 error.

Modify the statements in the Code:

Private static final String SQL _INSERT_TBL = "insert into tbl (id ,...)"
+ "Values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE, SYSDATE )";
 

Sure enough, the echo is normal.

A seemingly simple but troublesome mistake is worth your summary.

1. The OERR error description is a benchmark. The basic direction for troubleshooting is clear.

2. SQL errors in the code can be re-executed in tools such as plsql developer to simulate errors, but they must be the same as SQL statements in the code, here, I may not end it when I copy it, so that I did not find this error for the first time. In a word, except for judging the general direction, the most important thing is to be careful, do not let go of any small details.

Can refer to elder Yang once encountered the same ORA-00911 error: