CODEWITHSUNDEEP
sqloracle-databasecase
HEEN
HEEN

Reputation: 4727

ORA-00911: invalid character 00911. 00000 - "invalid character" unusual Error in Oracle

While executing below line of query in Oracle using CASE statement, I'm getting some unusual error.

QUERY

SELECT MODEL_NAME, 
       CASE WHEN EQP_TYPE_NAME IN('BAT') THEN MODEL_NAME  END AS EQUIPMENT_MODEL_NAME 
FROM  solar_equipment;

and the error is below

ORA-00911: invalid character
00911. 00000 -  "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:
Error at Line: 4 Column: 1

Please suggest what is wrong

Upvotes: 1

Views: 8530

Answers (2)

alejandrob
alejandrob

Reputation: 681

For some odd reason, this error ALWAYS happens when you paste an Oracle DDL into SQL Developer that was sent via Microsoft Teams chat.

It seems that some hidden invalid characters are added into the code, and the only way to fix this is either manually re-type everything (NOT recommended) or get it via email or some other channel.

Think of the similar case of Microsoft Word automatically changing your straight quote characters for typographic quotes, the latter being illegal in SQL.

SELECT *
FROM "TABLE_X"

Gets converted into:

SELECT *
FROM “TABLE_X”

Upvotes: 0

user5683823
user5683823

Reputation:

Most likely cause (based on the exchange in comments, about "copying and pasting"):

You copied the whole query from somewhere - most likely a website - where single-quotes aren't the standard apostrophe from the ASCII character set. Instead, they are calligraphic quotes. This applies specifically to the IN clause where you have the string BAT enclosed in single-quotes. That is why if you re-type the query in your editor you don't get that problem.

This problem is masked by the fact that, if you copy the query from wherever and paste it in your editor, the quotes are silently translated into standard apostrophe in your GUI (for displaying only) - but NOT in the actual query text. And, the problem can originally be caused by someone copying a valid query (with standard ASCII apostrophe) to a website, where the website silently converts those single quotes into calligraphic quotes.

One of the many "benefits" of letting Big Brother do stuff for us without asking us and without even telling us what he's doing...

And, why do you get that specific error? Because the parser interprets every "token" in the query as either a string or a SQL keyword or an identifier (table or column name), etc. When it gets to BAT enclosed in calligraphic quotes, that can't be a hard-coded string, it is not a SQL keyword, etc. - so the parser guesses it's an identifier; but identifiers not enclosed in double-quotes can't begin with a calligraphic left single-quote. So the parser tells you that the "identifier" is invalid.

Upvotes: 2

Related Questions