ORA-00911: invalid character

ORA-00911: invalid character error occurs when a special character or a non-printable character is added to the SQL Statement. If a special character other than $, _, or # is used in the column or table name, it must be surrounded by double quotation marks. Oracle SQL statements do not allow special characters, non-printable characters, or non-ascii characters. Otherwise, an error ORA-00911: invalid character will be thrown

When SQL statements are copied, non-printable characters are occasionally introduced to the SQL statement. Non-ascii characters are added to sql statements if you use an editor that supports Unicode. Oracle will throw the error ORA-00911: invalid character if it detects any special characters other than $, _, or #, as well as non-printable or non-ascii characters.



When the ORA-00911 error occur

Oracle will throw this error if any special characters other than $, _, or #, as well as non-printable or non-ascii characters, are discovered in the oracle sql query. The SQL Statement must be written in ascii characters. Non-ascii strings should be surrounded by single or double quotation marks.

Problem

create table dept(
id% number primary key,
name varchar2(100)
);

Error

Error report -
ORA-00911: invalid character
00911. 00000 -  "invalid character"
*Cause:    The identifier name started with an ASCII character other than a
           letter or a number. After the first character of the identifier
           name, ASCII characters are allowed including "$", "#" and "_".
           Identifiers enclosed in double quotation marks may contain any
           character other than a double quotation. Alternate quotation
           marks (q'#...#') cannot use spaces, tabs, or carriage returns as
           delimiters. For all other contexts, consult the SQL Language
           Reference Manual.
*Action:   Check the Oracle identifier naming convention. If you are
           attempting to provide a password in the IDENTIFIED BY clause of
           a CREATE USER or ALTER USER statement, then it is recommended to
           always enclose the password in double quotation marks because
           characters other than the double quotation are then allowed.


Root Cause

The identifier name started with an ASCII character other than a letter or a number. After the first character of the identifier name, ASCII characters are allowed including “$”, “#” and “_”. Identifiers enclosed in double quotation marks may contain any character other than a double quotation. Alternate quotation marks (q’#…#’) cannot use spaces, tabs, or carriage returns as delimiters.



Solution 1

If a special character is added to a column or table name, the special character should be deleted for consistency. Otherwise, a double quotation mark should be used around the column or table name. In this instance, the column or table name must always be surrounded in double quotation marks.

Problem

create table dept(
id% number primary key,
name varchar2(100)
);

Error report -
ORA-00911: invalid character
00911. 00000 -  "invalid character"

Solution

create table dept(
id number primary key,
name varchar2(100)
);
create table dept(
"id%" number primary key,
name varchar2(100)
);


Solution 2

The string in the SQL Statement should be surrounded by single quotation marks. Oracle will give an error if the string is not enclosed in single quotes. This occurs when you copy SQL statements from one editor to another. The single quotation can be replaced with other characters. The error will be fixed if you enclose the strings in the SQL statement with single quotation marks.

Problem

select * from dept where name = `a`;

Error report -
ORA-00911: invalid character
00911. 00000 -  "invalid character"

Solution

select * from dept where name = 'a';


Solution 3

The error will be thrown if you use any special characters in the SQL Statement. If any special characters are used in the sql query, they must be deleted. If special characters are used in a string, they should be surrounded by single or double quotation marks.

Problem

select * from dept where name like a%;

Error report -
ORA-00911: invalid character
00911. 00000 -  "invalid character"

Solution

select * from dept where name like 'a%';


Solution 4

When you copy a SQL Statement from one editor to another, the non-printable character is occasionally included. These characters are not visible. The error will be thrown when you execute the SQL Statement. To fix this issue, the SQL Statement needs be manually rewritten. This issue may be resolved by copying the ascii-based editor and pasting it into the sql editors.



Leave a Reply