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

CHHIBI AMOR picture CHHIBI AMOR · Jan 16, 2015 · Viewed 181.6k times · Source

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?

Answer

Alex Poole picture Alex Poole · Jan 16, 2015

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.