ORA-00911: Invalid character resolved

ORA-00911: Invalid character resolved

Today, I helped my colleagues call a stored procedure. The storage process is long and the problem occurs in a piece of code similar to the following.

BEGIN

Execute immediate 'update' | v_table_name | 'set' | v_column_name | '=: column_value where id =: id'

USING V_COLUMN_VALUE, V_ID;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

END;

The exception handling module is added to the program, but no further processing is performed after an error is caught. Therefore, when the stored procedure is executed, the program does not perform the UPDATE operation and no error is reported.

First, check the UPDATE statement. It seems that there is no problem. Therefore, SQLCODE and SQLERRM are printed in the EXCEPTION module.

The error after running is: ORA-00911: Invalid character.

Therefore, it is suspected that there is a problem with the passed parameters. Check the values of v_table_name, v_column_name, V_COLUMN_VALUE, and V_ID.

You have to print out the generated UPDATE statement. I checked and did not find any problems.

It was very strange, so I checked Oracle's Error Reference:

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.

It seems that the column name or table name contains incorrect fields, and the table name and column name generated by the UPDATE statement are carefully checked, which are common letters.

V_COLUMN_VALUE is a string that contains Chinese characters. Is it because the current environment supports Chinese characters. There is no problem with changing the environment test.

Carefully checked the field names and table names in the database, and confirmed that they are not stored in lower case format.

It also checks the four general input parameters and their length and whether there are invisible characters. There is still no gains.

Without binding variables, all four parameters are passed into the string and assembled into an UPDATE statement. And then put it in SQLPLUS for execution. Still reports an error.

The UPDATE statement is in the following format and an error is still reported.

SQL> UPDATE T SET NAME = 'Chinese and1234 'WHERE ID = 1;

Update t set name = 'Chinese and1234' where id = 1

*

ERROR is located in row 1st:

ORA-00911: invalid characters

Finally, I finally found that the first equal sign "=" was knocked into a Chinese equal sign by my colleagues.

Depressed, we both made calls for nearly an hour. It was a mistake.