Ora error ora 00911 invalid character

As per OERR,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 double quotes may contain any character other than a double quote. Alternative quotes (q’#…#’) cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.
Action: None

ORA-00911 exception is very common and usually occurs for common syntax mistakes. Some of the common causes and resolution are given below

Check list to run for ORA-00911 error

1. Sometimes when you copy the sql from another editor,it may non-printable/special character added (usually Acute instead of quote)

SQL> select * from APPS.FND_PROFILE_OPTION_NAME where profile_name like   'USER%`;

select * from APPS.FND_PROFILE_OPTION_NAME where profile_name like  'USER%`;
*
ERROR at line 1:
ORA-00911: invalid character

The correct way is to remove those character and try again

SQL> select * from APPS.FND_PROFILE_OPTION_NAME where profile_name like 'USER%';

2. This error occurs when a special character is used in a SQL WHERE clause and the value is not enclosed in single quotations.

SQL> select * from APPS.FND_PROFILE_OPTION_NAME where profile_name like USER%;

select * from APPS.FND_PROFILE_OPTION_NAME where profile_name like USER%;
*
ERROR at line 1:
ORA-00911: invalid character 

The correct query is

SQL> select * from APPS.FND_PROFILE_OPTION_NAME where profile_name like 'USER%';

3. when a extra semicolon (;) is added to end the query

SQL> select * from APPS.FND_PROFILE_OPTION_NAME where profile_name like 'USER%';;

select * from APPS.FND_PROFILE_OPTION_NAME where profile_name like 'USER%';
*
ERROR at line 1:
ORA-00911: invalid character 

Oracle has improved this 11g and above

select CHECKPOINT_CHANGE# from v$database;;

select CHECKPOINT_CHANGE# from v$database;
*
ERROR at line 1:
ORA-00933: SQL command not properly ended 

The correct way is to use single semi colon

SQL> select * from APPS.FND_PROFILE_OPTION_NAME where profile_name like 'USER%';
SQL> select CHECKPOINT_CHANGE# from v$database;

4. when semicolon (;) is added to end the query in execute immediate of pl/sql

SQL> begin
execute immediate 'select * from v$database;';
end;
/
begin
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 2 

Oracle has improved this 11g and above

begin
execute immediate 'select * from v$database;';
end;
/ 
begin
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at line 2 

The correct way is

begin
execute immediate 'select * from v$database';
end;
/

5. it also 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 oracle table, the name must be enclosed in double quotations.

create table example (j% number);  

create table example (j% number) 
*
ERROR at line 1: 
ORA-00911: invalid character 

Correct way

We should enclose them in double quotes “”

 SQL> create table example ("j%" number);  
Table created.  

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

Hope you like this content to resolve the ORA-00911: invalid character in oracle database.Please do provide the feedback to improve and include more stuff in this post

Related Articles
ORA-00936 missing expression

ORA-01017: invalid username/password

ora-29913: error in executing odciexttableopen callout

ORA-00001 unique constraint violated

ORA-00257: archiver error. Connect internal only, until freed.

ORA-03113: end-of-file on communication channel

Oracle Documentation

ORA-00911: invalid character error occurs when a special character or a non-printable character is added to the SQL Statement. If a special character other than $, _, or # is used in the column or table name, it must be surrounded by double quotation marks. Oracle SQL statements do not allow special characters, non-printable characters, or non-ascii characters. Otherwise, an error ORA-00911: invalid character will be thrown

When SQL statements are copied, non-printable characters are occasionally introduced to the SQL statement. Non-ascii characters are added to sql statements if you use an editor that supports Unicode. Oracle will throw the error ORA-00911: invalid character if it detects any special characters other than $, _, or #, as well as non-printable or non-ascii characters.

When the ORA-00911 error occur

Oracle will throw this error if any special characters other than $, _, or #, as well as non-printable or non-ascii characters, are discovered in the oracle sql query. The SQL Statement must be written in ascii characters. Non-ascii strings should be surrounded by single or double quotation marks.

Problem

create table dept(
id% number primary key,
name varchar2(100)
);

Error

Error report -
ORA-00911: invalid character
00911. 00000 -  "invalid character"
*Cause:    The identifier name started with an ASCII character other than a
           letter or a number. After the first character of the identifier
           name, ASCII characters are allowed including "$", "#" and "_".
           Identifiers enclosed in double quotation marks may contain any
           character other than a double quotation. Alternate quotation
           marks (q'#...#') cannot use spaces, tabs, or carriage returns as
           delimiters. For all other contexts, consult the SQL Language
           Reference Manual.
*Action:   Check the Oracle identifier naming convention. If you are
           attempting to provide a password in the IDENTIFIED BY clause of
           a CREATE USER or ALTER USER statement, then it is recommended to
           always enclose the password in double quotation marks because
           characters other than the double quotation are then allowed.

Root Cause

The identifier name started with an ASCII character other than a letter or a number. After the first character of the identifier name, ASCII characters are allowed including “$”, “#” and “_”. Identifiers enclosed in double quotation marks may contain any character other than a double quotation. Alternate quotation marks (q’#…#’) cannot use spaces, tabs, or carriage returns as delimiters.

Solution 1

If a special character is added to a column or table name, the special character should be deleted for consistency. Otherwise, a double quotation mark should be used around the column or table name. In this instance, the column or table name must always be surrounded in double quotation marks.

Problem

create table dept(
id% number primary key,
name varchar2(100)
);

Error report -
ORA-00911: invalid character
00911. 00000 -  "invalid character"

Solution

create table dept(
id number primary key,
name varchar2(100)
);

create table dept(
"id%" number primary key,
name varchar2(100)
);

Solution 2

The string in the SQL Statement should be surrounded by single quotation marks. Oracle will give an error if the string is not enclosed in single quotes. This occurs when you copy SQL statements from one editor to another. The single quotation can be replaced with other characters. The error will be fixed if you enclose the strings in the SQL statement with single quotation marks.

Problem

select * from dept where name = `a`;

Error report -
ORA-00911: invalid character
00911. 00000 -  "invalid character"

Solution

select * from dept where name = 'a';

Solution 3

The error will be thrown if you use any special characters in the SQL Statement. If any special characters are used in the sql query, they must be deleted. If special characters are used in a string, they should be surrounded by single or double quotation marks.

Problem

select * from dept where name like a%;

Error report -
ORA-00911: invalid character
00911. 00000 -  "invalid character"

Solution

select * from dept where name like 'a%';

Solution 4

When you copy a SQL Statement from one editor to another, the non-printable character is occasionally included. These characters are not visible. The error will be thrown when you execute the SQL Statement. To fix this issue, the SQL Statement needs be manually rewritten. This issue may be resolved by copying the ascii-based editor and pasting it into the sql editors.

Hello,

I am trying to make sense of an error I am seeing on my production environment but not in my test environment. The procedure has an error when it goes to re-create the table it dropped earlier. This table is a temp data table that is populated with another process. What I don’t understand is why this doesn’t work in production but does test when the procedure is identical in both places. This procedure was also running just fine for months then started having this error on Friday.

I am seeing the ORA-00911: invalid character when the procedure gets to EXECUTE IMMEDIATE LS_CRTABLE;

My test environment is virtually identical and has the same data yet I don’t see any issues. If anyone has any ideas I would sure be grateful.

Thanks,
-Jase

Procedure:

PROCEDURE SP_SUM_PKPD(
ai_status out number,
as_errm out varchar2)
IS

cursor C_GET_PK_SPECIES is
select distinct SPECIES
from ADMET.V_IS_PKPD_RSLT
WHERE SPECIES <>’NA’
and SPECIES not in (
select SPECIES from (select count(*), SPECIES
from ADMET.V_IS_PKPD_RSLT
group by SPECIES
having count(*) <20));

cursor C_GET_PK_RSLT_TYPE IS —(ls_species varchar2) IS
select distinct ADMET_RSLT_TYPE_CD
from ADMET.V_IS_PKPD_RSLT
WHERE ADMET_RSLT_TYPE_CD NOT LIKE ‘Brain%’;
— where SPECIES=ls_species;
—and tissue_ID=li_tissue;

li_table PLS_INTEGER := 0;
li_loop PLS_INTEGER := 0;
li_exist pls_integer := 0;
— li_species pls_integer := 0;
LS_ERRM varchar2(512) := »;
LS_PK_DIS varchar2(32767) := »;
LS_PK_LIST varchar2(32767) := »;
LS_PK_FROM varchar2(32767) := »;
LS_PK_SELECT varchar2(32767) := »;
ls_table_name varchar2(125):= »;
LS_ANA_TABLE VARCHAR2(4000) := »;
LS_DROP_TABLE VARCHAR2(512) := »;
LS_CRTABLE VARCHAR2(32767) := »;
LS_GRANT_TABLE VARCHAR2(4000) := »;
LI_STATUS PLS_INTEGER;
LE_PROBLEM EXCEPTION;
BEGIN
— create table for PK PO
—FOR LR IN C_GET_PK_SPECIES LOOP
—
—DBMS_OUTPUT.put_line(‘beging ‘||lr.SPECIES);
FOR LR1 IN C_GET_PK_RSLT_TYPE LOOP —(lr.SPECIES) LOOP
IF LI_LOOP = 0 THEN
LS_PK_LIST := ‘ WM_CONCAT(DECODE(ADMET_RSLT_TYPE_CD,»’
||LR1.ADMET_RSLT_TYPE_CD||»’, » »|| RSLT)) AS ‘||LR1.ADMET_RSLT_TYPE_CD;
— LS_PK_DIS := LR.SPECIES||’_’||LR1.ADMET_RSLT_TYPE_CD;
LS_PK_DIS := LR1.ADMET_RSLT_TYPE_CD;
ELSE
LS_PK_LIST:= LS_PK_LIST||’, ‘||’WM_CONCAT(DECODE (ADMET_RSLT_TYPE_CD,»’
||LR1.ADMET_RSLT_TYPE_CD||»’, » »|| RSLT)) AS ‘||LR1.ADMET_RSLT_TYPE_CD;
LS_PK_DIS := LS_PK_DIS||’, ‘||—|LR.SPECIES||’_’||
LR1.ADMET_RSLT_TYPE_CD;
END IF;
—DBMS_OUTPUT.put_line(‘LI_LOOP = ‘||LI_LOOP);
LI_LOOP := LI_LOOP + 1;

END Loop;
LI_LOOP := 0;
/* IF LI_TABLE = 0 THEN
DBMS_OUTPUT.put_line(‘LS_PK_LIST = ‘||LS_PK_LIST);
LS_PK_SELECT:= ‘SELECT distinct T.ADMET_PROJECT_NAME,T.CMPD_BATCH, T.BATCH_ID, ‘||
‘ T.STRAIN,T.SPECIES,T.TISSUE, T.ROA,T.DOSE, ‘
||LS_PK_DIS;
—DBMS_OUTPUT.put_line(‘LS_PK_SELECT 0 = ‘||LS_PK_SELECT);
LS_PK_FROM:='(SELECT ADMET_PROJECT_NAME,CMPD_BATCH,BATCH_ID, STRAIN,SPECIES,TISSUE,ROA,DOSE,’
||LS_PK_LIST
||’ from ADMET.V_IS_PKPD_RSLT GROUP by ADMET_PROJECT_NAME,CMPD_BATCH, BATCH_ID,STRAIN,SPECIES,TISSUE,ROA,DOSE) T’||LI_table;

LS_PK_FROM:=’ ADMET.V_IS_PKPD_RSLT T ‘
||’ LEFT JOIN ‘||LS_PK_FROM||’ ON T.CMPD_BATCH =T’||LI_table||’.CMPD_BATCH’;

—DBMS_OUTPUT.put_line(‘LS_PK_FROM 0 = ‘||LS_PK_FROM);
ELSE
LS_PK_SELECT:= LS_PK_SELECT||’,’||LS_PK_DIS;
— DBMS_OUTPUT.put_line(‘LS_PK_SELECT = ‘||LS_PK_SELECT);
LS_PK_FROM:= LS_PK_FROM
||’ LEFT JOIN (SELECT ADMET_PROJECT_NAME, CMPD_BATCH, BATCH_ID,STRAIN,SPECIES,ROA,TISSUE’
||LS_PK_LIST||’ from ADMET.V_IS_PKPD_RSLT ‘
— || ‘ where SPECIES = »’||LR.SPECIES
||»’ GROUP by ADMET_PROJECT_NAME,CMPD_BATCH, BATCH_ID,STRAIN,SPECIES,ROA,TISSUE) T’||LI_table
||’ ON T.CMPD_BATCH =T’||LI_table||’.CMPD_BATCH ‘;
— DBMS_OUTPUT.put_line(‘LS_PK_FROM = ‘||LS_PK_FROM);
— DBMS_OUTPUT.put_line(‘LS_PK_SELECT = ‘||LS_PK_SELECT);
END IF;
*/ LI_table:=LI_table+1;
— DBMS_OUTPUT.put_line(‘LS_PK_SELECT = ‘||LS_PK_SELECT);
— dbms_output.put_line(‘FROM = ‘||SUBSTR(LS_PK_FROM, -1, 11));
— END LOOP;
ls_table_name := ‘IS_PKPD’;
DBMS_OUTPUT.put_line(‘ls_table_name = ‘||ls_table_name);
select COUNT(*)
into li_exist
from all_objects
where object_type = ‘TABLE’
and owner=’ADMET’
and object_name = ls_table_name;
—dbms_output.put_line(‘li_exist = ‘||li_exist);
dbms_output.put_line(‘ls_table_name = ‘||ls_table_name);
if li_exist > 0
then
LS_DROP_TABLE := ‘DROP TABLE ADMET.’||ls_table_name;
EXECUTE IMMEDIATE LS_DROP_TABLE;
—dbms_output.put_line(‘PK table droped ‘||LS_DROP_TABLE);
end if;

—DBMS_OUTPUT.put_line(‘LS_PK_SELECT = ‘||LS_PK_SELECT);
—DBMS_OUTPUT.put_line(‘LS_PK_from = ‘||LS_PK_from);
DBMS_OUTPUT.put_line(‘CRETE TABLE = ‘||ls_table_name);
LS_CRTABLE:= ‘CREATE TABLE ADMET.’||ls_table_name
||’ AS ‘||LS_PK_SELECT||’ FROM ‘||LS_PK_FROM;

LS_CRTABLE := ‘CREATE TABLE ADMET.’||ls_table_name||
‘ AS SELECT ADMET_PROJECT_NAME,ADMET_STUDY_CD, CMPD_BATCH, BATCH_ID,’
||’ STRAIN,SPECIES,TISSUE,ROA,DOSE, VEHICLE,PK_FREQUENCY,END_POINTS,N,RELATED_CMPD,PK_FOR_CMPD,’
||LS_PK_LIST||’ from ADMET.V_IS_PKPD_RSLT ‘
||’ GROUP by ADMET_PROJECT_NAME,ADMET_STUDY_CD,CMPD_BATCH, BATCH_ID,’
||’ STRAIN,SPECIES,TISSUE,ROA,DOSE,VEHICLE, PK_FREQUENCY,END_POINTS,N,RELATED_CMPD,PK_FOR_CMPD’;

—DBMS_OUTPUT.put_line(‘LS_CRTABLE = ‘||LS_CRTABLE);
EXECUTE IMMEDIATE LS_CRTABLE;
— DBMS_OUTPUT.put_line(‘ANALIZE = ‘||ls_table_name);
LS_GRANT_TABLE := ‘GRANT SELECT ON ADMET.’||ls_table_name
||’ TO BIOASSAY WITH GRANT OPTION’;
EXECUTE IMMEDIATE LS_GRANT_TABLE;
LS_GRANT_TABLE := ‘GRANT SELECT ON ADMET.’||ls_table_name
||’ TO PIPELINE WITH GRANT OPTION’;
EXECUTE IMMEDIATE LS_GRANT_TABLE;
LS_GRANT_TABLE := ‘GRANT SELECT ON ADMET.’||ls_table_name
||’ TO ADMETWEB WITH GRANT OPTION’;
EXECUTE IMMEDIATE LS_GRANT_TABLE;
LS_ANA_TABLE := ‘ANALYZE TABLE ADMET.’||ls_table_name
|| ‘ ESTIMATE STATISTICS SAMPLE 25 PERCENT’;
EXECUTE IMMEDIATE LS_ANA_TABLE;
—dbms_output.put_line(‘LI_table = ‘||LI_table);
— dbms_output.put_line(‘LI_LOOP = ‘||LI_LOOP);

ai_status := 1;
as_errm := ‘ PKPD table is created.’;
EXCEPTION
when others then
DBMS_OUTPUT.put_line(‘Problem ‘|| SQLERRM );
DBMS_OUTPUT.put_line(‘Problem ‘|| SQLCODE );
ai_status := SQLCODE;
as_errm := ‘Error is ‘|| SQLERRM ;
— rollback;
END SP_SUM_PKPD;