ORA-00911: invalid character

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

Reference: http://docs.oracle.com/cd/B28359_01/server.111/b28278/e900.htm#ORA-00910

ORA-00911 exception is very common and usually occurs for common syntax mistakes. ORA-00911 occurs usually when a programmer makes one of the following mistakes

1. when a special character is added in an SQL statement with column name
SQL> select ename# from scott.emp;
select ename# from scott.emp
       *
ERROR at line 1:
ORA-00904: "ENAME#": invalid identifier

2. when some non-printable/special character added because of paste of sql statement from other editer (usually Acute` instead of quote')
SQL> select * from scott.emp where ename like `A%`;
select * from scott.emp where ename like `A%`
                                         *
ERROR at line 1:
ORA-00911: invalid character

3. when string is not enclosed by single quotes in where clause condition
SQL> select * from emp where ename like A%;
select * from emp where ename like A%
                                    *
ERROR at line 1:
ORA-00911: invalid character

4. when a extra semicolon (;) is added to end the query
SQL> select empno from emp;;
select empno from emp;
                     *
ERROR at line 1:
ORA-00911: invalid character

5. when semicolon (;) is added to end the query in execute immediate of pl/sql
SQL> begin
  2     execute immediate 'update scott.emp set sal = sal * 1.1 where deptno=10;';
  3     commit;
  4  end;
  5  /
begin
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 2

6. when semicolon (;) is added to end the query executing from programming language like .net or java



Related Posts:
- ORA-00936 missing expression
- ORA-01722: invalid number
- ORA-06550: line n, column n
- ORA-00904: invalid identifier
- ORA-06502: PL/SQL: numeric or value errorstring

13 comments:

  1. in my case it was an syntax error in the mapping (.hbm.xml)

    ReplyDelete
  2. Good summary, Thank you for the post.
    Praveen

    ReplyDelete
  3. Very useful post thank you very much

    ReplyDelete
  4. Why is this error happening when there is a comment on the line?
    SELECT COUNT(*) FROM SATURN.SPRTELE WHERE SPRTELE_PIDM = 90384408; --1
    The --1 causes the 00911 error. Why?

    ReplyDelete
    Replies
    1. you need to put the comment inside statement i.e. before ; like
      SELECT COUNT(*) FROM SATURN.SPRTELE WHERE SPRTELE_PIDM = 90384408 --1;

      Delete
  5. Keep getting the ORA-00911 error in Oracle and have tried several different variants

    UPDATE L_EMPLOYEES set timer = 'Old Timer'
    where HIRE_DATE < 01-01-2000#;

    ReplyDelete
    Replies
    1. I hope you have L_EMPLOYEES table withe TIMER VARCHAR2 and HIRE_DATE DATE column, use following query
      UPDATE L_EMPLOYEES set timer = 'Old Timer'
      where HIRE_DATE < TO_DATE('01-01-2000','DD-MM-YYYY');

      Delete
    2. SQL> UPDATE L_EMPLOYEES set Old_Timer = HIRE_DATE
      2 where HIRE_DATE < TO_DATE('01-01-2000','DD-MM-YYYY');

      3 rows updated.

      I changed it to that and it worked

      Delete
    3. Now when I go to make my Union I get this ?

      SQL> SELECT * FROM(
      2 SELECT l1.LAST_NAME, l1.FIRST_NAME, l1.HIRE_DATE AS OLD_TIMERS, '' AS NEWER
      _HIRES
      3 FROM L_EMPLOYEES l1
      4 where l1.HIRE_DATE < (#01-01-2000#) and l1.HIRE_DATE is not null
      5 union all
      6 SELECT l2.LAST_NAME, l2.FIRST_NAME, '' AS OLD_TIMERS, l2.HIRE_DATE AS NEWE
      R_HIRES
      7 FROM L_EMPLOYEES l2
      8 where l2.HIRE_DATE >= (#01-01-2000#) and l2.HIRE_DATE is not null
      9 );
      where l1.HIRE_DATE < (#01-01-2000#) and l1.HIRE_DATE is not null
      *
      ERROR at line 4:
      ORA-00911: invalid character

      Delete
    4. use to_date() as in example for handling dates

      Delete
  6. Thank You Very Much....Help of this site i solve my problem.

    ReplyDelete