ORA-00911: invalid character Common Issues and Resolution

As per OERR,ORA-00911: invalid character

Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q’#…#’) cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.
Action: None

ORA-00911

ORA-00911 exception is very common and usually occurs for common syntax mistakes. Some of the common causes and resolution are given below
1. Sometimes when you copy the sql from another editor,it may non-printable/special character added (usually Acute` instead of quote’)

SQL> select * from APPS.FND_PROFILE_OPTION_NAME where profile_name like `USER%`;
select * from APPS.FND_PROFILE_OPTION_NAME where profile_name like `USER%`;
*
ERROR at line 1:
ORA-00911: invalid character

2. This error occurs when a special character is used in a SQL WHERE clause and the value is not enclosed in single quotations.

SQL> select * from APPS.FND_PROFILE_OPTION_NAME where profile_name like USER%;
select * from APPS.FND_PROFILE_OPTION_NAME where profile_name like USER%;
*
ERROR at line 1:
ORA-00911: invalid character

3. when a extra semicolon (;) is added to end the query

SQL> select * from APPS.FND_PROFILE_OPTION_NAME where profile_name like ‘USER%’;;
select * from APPS.FND_PROFILE_OPTION_NAME where profile_name like ‘USER%’;
*
ERROR at line 1:
ORA-00911: invalid character

Oracle has improved this 11g and above

select CHECKPOINT_CHANGE# from v$database;;
select CHECKPOINT_CHANGE# from v$database;
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

4. when semicolon (;) is added to end the query in execute immediate of pl/sql

SQL> begin
execute immediate ‘select * from v$database;’;
end;
/
begin
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 2

Oracle has improved this 11g and above

begin
execute immediate ‘select * from v$database;’;
end;
/ 2 3 4
begin
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at line 2

5. it also occurs when you try to use a special character in a SQL statement. If a special character other than $, _, and # is used in the name of a column or table, the name must be enclosed in double quotations.

 

create table example (j% number);

create table example (j% number)

*

ERROR at line 1:

ORA-00911: invalid character

Correct way

SQL> create table example (“j%” number);

 

Table created.

6. when semicolon (;) is added to end the query executing from programming language like .net or java