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

19 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
  7. Admiring the time and effort you put into your blog and iin depth
    information you provide. It's good to come across a blog every onhe in a while that isn't tthe same out off date rehashed material.

    Excellent read! I've bookmarked your site and I'm including your RSS feeds to my Google
    account.

    ReplyDelete
  8. Thanks for finally writing about >"ORA-00911: invalid character"
    <Loved it!

    ReplyDelete
  9. I AM GETTING THE ERROR WHEN I AM CREATING A TABLE HELP!!!!!!!!!!!!!!
    CREATE TABLE "revtmdb.pp_glrpt_variable"
    (
    company NUMBER(4) NOT NULL,
    r_name CHAR(2 byte) NOT NULL,
    currency_code CHAR ( 5 byte) NOT NULL,
    site_id CHAR (5 byte) NOT NULL,
    fiscal_year NUMBER (4) NOT NULL,
    acct_period NUMBER(2) NOT NULL,
    r_system CHAR(2 byte) NOT NULL,
    posting_date DATE NOT NULL,
    je_type CHAR(1 byte) NOT NULL,
    control_group NUMBER (8),
    invoice # NUMBER(8),
    report name CHAR(2 byte) NOT NULL,
    report DESC CHAR (2 byte) NOT NULL,
    gl_trans DESC CHAR(2 byte) NOT NULL,
    acct_unit CHAR (15 byte) NOT NULL,
    acct_unit_desc CHAR(30 byte) NOT NULL,
    person_resp CHAR(15 byte) NOT NULL,
    var_levels CHAR (30 byte) NOT NULL,
    account NUMBER(6) NOT NULL,
    gl_acct_desc CHAR (30 byte) NOT NULL,
    beg_amount NUMBER (18,2) NOT NULL,
    glt_fields CHAR(30 byte) NOT NULL,
    glt_debit_amt NUMBER (18,2) NOT NULL,
    glt_credit_amt NUMBER (18,2) NOT NULL,
    end_amount NUMBER (18,2) NOT NULL,
    )

    ReplyDelete
  10. create table Boat(bid number(3) primary key,bname varchar2(30),bcolor varchar2(30));
    insert into Boat values (1,'Misty','Red');

    WHAT IS WRONG IN THE SECOND LINE?

    ReplyDelete
  11. Panther spirit people encourage others positively through their own zest for life and quest for realizing ones dreams and desires. Panther reminds us to release our fears acknowledge our own power and accept our dark shadow selves. Black Panther for birthday

    ReplyDelete
  12. There are many different ways to entertain your party guests at a birthday party. One of the most common ways is to have a costume character walking around. But some children, including the birthday boy or girl, can be scared. hora loca

    ReplyDelete