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?

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? — Alex Poole, Jan 16 '15 at 16:07
I had no problem executing your DDL and your DML using SQL Plus (not Toad). — Patrick Bacon, Jan 16 '15 at 16:10
[This might be relevant](https://support.software.dell.com/toad-for-oracle/kb/47096), though that has the comment on the same line. — Alex Poole, Jan 16 '15 at 16:12
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. — Allan, Jan 16 '15 at 16:13
Or [possibly a duplicate of this question](http://stackoverflow.com/q/23029246/266304), depending on how you're running the snippet you showed. — Alex Poole, Jan 16 '15 at 16:19
@AlexPoole i solve it by removing the `--COMMIT;` post your answer, please — CHHIBI AMOR, Jan 16 '15 at 16:22
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. — kundan, Dec 26 '17 at 12:08

score 79 · Accepted Answer · edited May 23 '17 at 12:03

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.

@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-) — Alex Poole, Jan 16 '15 at 18:07
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](https://stackoverflow.com/questions/64256770/invalid-character-error-while-executing-oracle-procedure-using-getclobval/64257178) — Scope, Oct 09 '20 at 17:32

score 25 · Answer 2 · answered Sep 20 '18 at 09:13

25

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

answered Sep 20 '18 at 09:13

Bablu Ahmed

4,412
5
49
64

11

removing `;` from the query fixed the issue for me – csandreas1 Jan 29 '19 at 13:32
2

after removing ; from the sql query, it fixed for me. – Chameera M Perera Nov 15 '19 at 06:52

score 9 · Answer 3 · answered Jul 07 '20 at 10:55

9

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.

answered Jul 07 '20 at 10:55

Sagansha San

156
1
4

score 4 · Answer 4 · answered Jan 16 '15 at 16:08

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

answered Jan 16 '15 at 16:08

erakm

106
2

From the DDL and DML in the question, that isn't the case here. – Alex Poole Jan 16 '15 at 16:09
@erakm I don't use any special caracter – CHHIBI AMOR Jan 16 '15 at 16:11
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. – erakm Jan 16 '15 at 16:17
still a valid answer to provide useful focused context, just incase – petrosmm Feb 02 '22 at 19:34

score 4 · Answer 5 · answered Sep 19 '17 at 08:08

4

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.

answered Sep 19 '17 at 08:08

Norbert Wupona

122
7

score 3 · Answer 6 · answered Jan 02 '19 at 14:56

3

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.

answered Jan 02 '19 at 14:56

root

325
2
11

score 1 · Answer 7 · answered May 21 '18 at 09:49

1

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.

answered May 21 '18 at 09:49

azzurroverde

567
5
18

score 1 · Answer 8 · answered Apr 07 '20 at 15:38

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

Option 4- there's a bug in the software that submits the SQL. — dataman, Aug 28 '22 at 14:46

score 1 · Answer 9 · answered Jan 23 '23 at 21:10

1

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.

answered Jan 23 '23 at 21:10

Jaguar Nation

122
11

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

9 Answers9

Linked

Related