How can I solve ORA-00911: invalid character error?

Question

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?

Florella

The statement is fine. I seem to recall some versions of Toad were confused by comments; does it work if you remove the commented-out commit completely? And are you running as a statement or as a script? – Leffert

I had no problem executing your DDL and your DML using SQL Plus (not Toad). – Desiree

This might be relevant, though that has the comment on the same line. – Leffert

Is there any chance that you've copied the SQL from somewhere that uses smart quotes (i.e. MS Word)? You'll get this error any time they appear instead of regular quotes. – Madea

Or possibly a duplicate of this question, depending on how you're running the snippet you showed. – Leffert

@AlexPoole i solve it by removing the --COMMIT; post your answer, please – Florella

Just rewrite the query again manually.. It will give error because, when you are copying query from one place like skype or internet, with it some extra spaces (or spl. character) will be copied. And it will give error. – Pantisocracy

F9 saved my day! – Xi

Answer 1

The statement you're executing is valid. The error seems to mean that Toad is including the trailing semicolon as part of the command, which does cause an ORA-00911 when it's included as part of a statement - since it is a statement separator in the client, not part of the statement itself.

It may be the following commented-out line that is confusing Toad (as described here); or it might be because you're trying to run everything as a single statement, in which case you can try to use the run script command (F9) instead of run statement (F5).

Just removing the commented-out line makes the problem go away, but if you also saw this with an actual commit then it's likely to be that you're using the wrong method to run the statements.

There is a bit more information about how Toad parses the semicolons in a comment on this related question, but I'm not familiar enough with Toad to go into more detail.

Leffert

@MichaelS. - your answer gives more detail about Toad's run command than I can, so it might be useful, and you could consider undeleting it - I don't think it's poaching *8-) – Leffert

I have a similar problem but on SQL developer and i am unable to fix it, could you pls answer this question if you can here – Iconology

Answer 2

Remove the semicolon (;), backtick (``) etc. from inside a query

Stockade

removing ; from the query fixed the issue for me – Sterner

after removing ; from the sql query, it fixed for me. – Intrauterine

Answer 3

Remove the semicolon ( ; ).

In oracle, you can use semicolon or not when u ran query directly on DB. But when u using java to ran a oracle query, u have to remove semicolon at the end.

Buccaneer

Answer 4

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. Link

Celluloid

From the DDL and DML in the question, that isn't the case here. – Leffert

@Celluloid I don't use any special caracter – Florella

think Option-2 that is in same link which i wrote in my answer. 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. – Celluloid

still a valid answer to provide useful focused context, just incase – Adynamia

Answer 5

I encountered the same thing lately. it was just due to spaces when copying a script from a document to sql developer. I had to remove the spaces and the script ran.

Anatomy

Answer 6

I'm using a 3rd party program that executes Oracle SQL and I encountered this error. Prior to a SELECT statement, I had some commented notes that included special characters. Removing the comments resolved the issue.

Whatsoever

Answer 7

I had the same problem and it was due to the end of line. I had copied from another document. I put everythng on the same line, then split them again and it worked.

Griz

Answer 8

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 = ?;

Jalapa

Option 4- there's a bug in the software that submits the SQL. – Smear

Answer 9

You probably copied and pasted the SQL from another source, and it screwed up something somehow. . . I don't know how or why lol.

Re-type your sql in whatever IDE you are using, and it should work.

Sunshade