ORA-00911: invalid character Common Issues and Resolution

Last updated on July 13th, 2018 at 02:48 pm

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 double quotes may contain any character other than a double quote. 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

Check list to run forĀ ORA-00911 error

1. Sometimes when you copy the sql from another editor,it may non-printable/special character added (usually Acuteinstead of quote')
<table>
<tbody>
<tr>
<td width="638">SQL&gt; 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


Leave a Reply