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 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 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
The correct way is to remove those character and try again
SQL> select * from APPS.FND_PROFILE_OPTION_NAME where profile_name like 'USER%';
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
The correct query is
SQL> select * from APPS.FND_PROFILE_OPTION_NAME where profile_name like 'USER%';
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
The correct way is to use single semi colon
SQL> select * from APPS.FND_PROFILE_OPTION_NAME where profile_name like 'USER%'; SQL> select CHECKPOINT_CHANGE# from v$database;
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;
/
begin
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at line 2
The correct way is
begin execute immediate 'select * from v$database'; end; /
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 oracle 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
We should enclose them in double quotes “”
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
Hope you like this content to resolve the ORA-00911: invalid character in oracle database.Please do provide the feedback to improve and include more stuff in this post
Related Articles
ORA-00936 missing expression
ORA-01017: invalid username/password
ora-29913: error in executing odciexttableopen callout
ORA-00001 unique constraint violated
ORA-00257: archiver error. Connect internal only, until freed.