FB

ORA-00911 invalid character Solution

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.

 

ORA-00911 Solution

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:

1 thought on “ORA-00911 invalid character Solution”

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Table of Contents