I tried to execute an SQL INSERT with Toad for oracle:
INSERT INTO GRAT_ACTIVITY
(UUID, IP_ADRESS, SEND_MAIL, DATE_CREA, DATE_UPD, CREATOR, CENTER, ETAT, REQUEST)
VALUES('555-vgd9-pllkd-5513', '172.12.23.130', 'N', SYSDATE, SYSDATE, '1554', 'M18', 'I', 8842);
--COMMIT;
the GRAT_ACTIVITY table structure is as below:
CREATE TABLE CASH.GRAT_ACTIVITY
(
UUID VARCHAR2(64 BYTE) NOT NULL,
IP_ADRESS VARCHAR2(15 BYTE),
SEND_MAIL VARCHAR2(1 BYTE),
DATE_CREA DATE,
DATE_UPD DATE,
CREATOR VARCHAR2(4 BYTE),
CENTER VARCHAR2(4 BYTE),
ETAT VARCHAR2(1 BYTE),
REQUEST NUMBER
)
the error message:
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
How can I solve it?
ORA-00911
ORA-00911: неправильный символ
Причина:
Специальные символы правильны только в определенных местах. Если специальные символы такие как $, ~, # используются в имени и имя не заключено в двойные кавычки, то последует сообщение.
Действие:
Уберите неправильный символ из оператора (выражения), или заключите имя объекта в двойные кавычки.
Are you getting an “ORA-00911 invalid character” error when running an SQL statement? Find out what causes it and how to resolve it in this article.
ORA-00911 Cause
So, you’ve tried to run an SQL statement, such as INSERT or SELECT, and gotten this error:
ORA-00911: invalid character
Why did this happen?
According to the Oracle error message:
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.
This error occurred because there was a special character in your SQL statement. It could be a special character in the WHERE clause that is not enclosed in single quotes.
Oracle mentions that identifiers (such as table names) cannot start with any character other than letters or numbers. A few symbols (such as $#_) are allowed after the first character.
To resolve this error, you need to remove the special character from your statement or enclose it in single quotes.
Let’s take a look at some examples of where you might get this error, and how to resolve it.
While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:
ORA-00911 invalid character While Inserting
If you’re getting this error when running an INSERT statement, it could be that you have:
- Added a special character to one of the column names
- Added a special character to the VALUES without enclosing it in single quotes.
An example of a query that would cause this error is:
INSERT INTO student (student_id, first_name, last_name)
VALUES (21, ##, 'Hanson');To resolve it, change your query to remove the special character:
INSERT INTO student (student_id, first_name, last_name)
VALUES (21, 'Maria', 'Hanson');Or, enclose it in single quotes so it is treated like a string, if you need the value:
INSERT INTO student (student_id, first_name, last_name)
VALUES (21, '##', 'Hanson');You can read my guide on the INSERT statement for more information.
ORA-00911 invalid character in Oracle SELECT
If you’re getting this error in a SELECT statement, then it’s also probably because there is a special character where there shouldn’t be.
An example of a query that causes this error is:
SELECT student_id, first_name, last_name
FROM student
WHERE student_id = #9;To resolve it, you can change your query to remove the special character:
SELECT student_id, first_name, last_name
FROM student
WHERE student_id = 9;ORA-00911 invalid character In Toad
If you’re running Toad, you might be seeing some strange behaviour.
Your query might look like this:
INSERT INTO student (student_id, first_name, last_name)
VALUES (21, 'Maria', 'Hanson');--COMMIT;If you run this command, you might be getting an ORA-00911: invalid character in Toad.
But, if you look closely, there’s no special characters in the query!
Why is this happening?
It’s because Toad has some strange behaviour when it comes to semicolons and comments (which you can read more about here)
The error is happening because the semicolon from the commented-out section is being included – even though it is commented out.
To resolve the issue and make your query run, remove the commented-out section.
INSERT INTO student (student_id, first_name, last_name)
VALUES (21, 'Maria', 'Hanson');Now the query should run successfully.
So, that’s how you resolve the “ORA-00911: invalid character” error in Oracle.
While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:
Learn the cause and how to resolve the ORA-00911 error message in Oracle.
Description
When you encounter an ORA-00911 error, the following error message will appear:
- ORA-00911: invalid character
Cause
You tried to execute a SQL statement that included a special character.
Resolution
The option(s) to resolve this Oracle error are:
Option #1
This error 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.
Option #2
This error may occur if you’ve pasted your SQL into your editor from another program. Sometimes there are non-printable characters that may be present. In this case, you should try retyping your SQL statement and then re-execute it.
Option #3
This error occurs when a special character is used in a SQL WHERE clause and the value is not enclosed in single quotations.
For example, if you had the following SQL statement:
SELECT * FROM suppliers WHERE supplier_name = ?;
You would receive the following error message:
You could correct this error by enclosing the ? in single quotations as follows:
SELECT * FROM suppliers WHERE supplier_name = '?';
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.
ORA-03113: end-of-file on communication channel
Oracle Documentation