Sql error 911 22019 ora 00911 неверный символ - Исправление ошибок и поиск оптимальных решений проблем

Are you getting an “ORA-00911 invalid character” error when running an SQL statement? Find out what causes it and how to resolve it in this article.

ORA-00911 Cause

So, you’ve tried to run an SQL statement, such as INSERT or SELECT, and gotten this error:

ORA-00911: invalid character

Why did this happen?

According to the Oracle error message:

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.

This error occurred because there was a special character in your SQL statement. It could be a special character in the WHERE clause that is not enclosed in single quotes.

Oracle mentions that identifiers (such as table names) cannot start with any character other than letters or numbers. A few symbols (such as $#_) are allowed after the first character.

To resolve this error, you need to remove the special character from your statement or enclose it in single quotes.

Let’s take a look at some examples of where you might get this error, and how to resolve it.

ORA-00911 invalid character While Inserting

If you’re getting this error when running an INSERT statement, it could be that you have:

Added a special character to one of the column names
Added a special character to the VALUES without enclosing it in single quotes.

An example of a query that would cause this error is:

INSERT INTO student (student_id, first_name, last_name)
VALUES (21, ##, 'Hanson');

To resolve it, change your query to remove the special character:

INSERT INTO student (student_id, first_name, last_name)
VALUES (21, 'Maria', 'Hanson');

Or, enclose it in single quotes so it is treated like a string, if you need the value:

INSERT INTO student (student_id, first_name, last_name)
VALUES (21, '##', 'Hanson');

You can read my guide on the INSERT statement for more information.

ORA-00911 invalid character in Oracle SELECT

If you’re getting this error in a SELECT statement, then it’s also probably because there is a special character where there shouldn’t be.

An example of a query that causes this error is:

SELECT student_id, first_name, last_name
FROM student
WHERE student_id = #9;

To resolve it, you can change your query to remove the special character:

SELECT student_id, first_name, last_name
FROM student
WHERE student_id = 9;

ORA-00911 invalid character In Toad

If you’re running Toad, you might be seeing some strange behaviour.

Your query might look like this:

INSERT INTO student (student_id, first_name, last_name)
VALUES (21, 'Maria', 'Hanson');--COMMIT;

If you run this command, you might be getting an ORA-00911: invalid character in Toad.

But, if you look closely, there’s no special characters in the query!

Why is this happening?

It’s because Toad has some strange behaviour when it comes to semicolons and comments (which you can read more about here)

The error is happening because the semicolon from the commented-out section is being included – even though it is commented out.

To resolve the issue and make your query run, remove the commented-out section.

INSERT INTO student (student_id, first_name, last_name)
VALUES (21, 'Maria', 'Hanson');

Now the query should run successfully.

So, that’s how you resolve the “ORA-00911: invalid character” error in Oracle.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Источник

I’m trying to insert CLOBs into a database (see related question). I can’t quite figure out what’s wrong. I have a list of about 85 clobs I want to insert into a table. Even when inserting only the first clob I get ORA-00911: invalid character. I can’t figure out how to get the statement out of the PreparedStatement before it executes, so I can’t be 100% certain that it’s right, but if I got it right, then it should look exactly like this:

insert all
  into domo_queries values ('select 
substr(to_char(max_data),1,4) as year,
substr(to_char(max_data),5,6) as month,
max_data
from dss_fin_user.acq_dashboard_src_load_success
where source = ''CHQ PeopleSoft FS''')
select * from dual;

Ultimately, this insert all statement would have a lot of into‘s, which is why I just don’t do a regular insert statement. I don’t see an invalid character in there, do you? (Oh, and that code above runs fine when I run it in my sql developer tool.) And I if I remove the semi-colon in the PreparedStatement, it throws an ORA-00933: SQL command not properly ended error.

In any case, here’s my code for executing the query (and the values of the variables for the example above).

public ResultSet executeQuery(String connection, String query, QueryParameter... params) throws DataException, SQLException {
  // query at this point = "insert all
                          //into domo_queries values (?)
                          //select * from dual;"
  Connection conn = ConnectionPool.getInstance().get(connection);
  PreparedStatement pstmt = conn.prepareStatement(query);
  for (int i = 1; i <= params.length; i++) {
    QueryParameter param = params[i - 1];
    switch (param.getType()) { //The type in the example is QueryParameter.CLOB
      case QueryParameter.CLOB:
        Clob clob = CLOB.createTemporary(conn, false, oracle.sql.CLOB.DURATION_SESSION);
        clob.setString(i, "'" + param.getValue() + "'");
        //the value of param.getValue() at this point is:
        /*
         * select 
         * substr(to_char(max_data),1,4) as year,
         * substr(to_char(max_data),5,6) as month,
         * max_data
         * from dss_fin_user.acq_dashboard_src_load_success
         * where source = ''CHQ PeopleSoft FS''
         */
        pstmt.setClob(i, clob);
        break;
      case QueryParameter.STRING:
        pstmt.setString(i, "'" + param.getValue() + "'");
        break;
    }
  }
  ResultSet rs = pstmt.executeQuery(); //Obviously, this is where the error is thrown
  conn.commit();
  ConnectionPool.getInstance().release(conn);
  return rs;
}

Is there anything I’m just missing big time?

Источник

insert all
  into domo_queries values ('select 
substr(to_char(max_data),1,4) as year,
substr(to_char(max_data),5,6) as month,
max_data
from dss_fin_user.acq_dashboard_src_load_success
where source = ''CHQ PeopleSoft FS''')
select * from dual;

In any case, here’s my code for executing the query (and the values of the variables for the example above).

public ResultSet executeQuery(String connection, String query, QueryParameter... params) throws DataException, SQLException {
  // query at this point = "insert all
                          //into domo_queries values (?)
                          //select * from dual;"
  Connection conn = ConnectionPool.getInstance().get(connection);
  PreparedStatement pstmt = conn.prepareStatement(query);
  for (int i = 1; i <= params.length; i++) {
    QueryParameter param = params[i - 1];
    switch (param.getType()) { //The type in the example is QueryParameter.CLOB
      case QueryParameter.CLOB:
        Clob clob = CLOB.createTemporary(conn, false, oracle.sql.CLOB.DURATION_SESSION);
        clob.setString(i, "'" + param.getValue() + "'");
        //the value of param.getValue() at this point is:
        /*
         * select 
         * substr(to_char(max_data),1,4) as year,
         * substr(to_char(max_data),5,6) as month,
         * max_data
         * from dss_fin_user.acq_dashboard_src_load_success
         * where source = ''CHQ PeopleSoft FS''
         */
        pstmt.setClob(i, clob);
        break;
      case QueryParameter.STRING:
        pstmt.setString(i, "'" + param.getValue() + "'");
        break;
    }
  }
  ResultSet rs = pstmt.executeQuery(); //Obviously, this is where the error is thrown
  conn.commit();
  ConnectionPool.getInstance().release(conn);
  return rs;
}

Is there anything I’m just missing big time?

Источник

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.

Источник

ORA-00911 Error Message

Some Oracle errors can seemingly have a million different reasons as to why they occur. The ORA-00911 message would definitely fall into this category. The message, described as an invalid character error, typically is the result of a common syntax mistake.

The cause, according to Oracle docs, can be from starting identifiers with ASCII (American Standard Code) that are not letters or numbers. It is also noted that $, #, and _ are permitted past the first character; that identifiers enclosed in double quotes may contain any characters other than double quotes; and lastly, that alternative quotes cannot use spaces, tabs or carriage returns as delimiters (a separate region of text). With that said, since ORA-00911 is such a common problem with so many potential causes and solutions, let us cover some of the more prevalent fixes to this error.

The first type of ORA-00911 we will look at occurs when attempting to use a special character in an SQL WHERE clause (used to filter results from a particular statement, such as SELECT or UPDATE). If the value is not surrounded by single quotations, the error message will be thrown. For instance, if you have the WHERE clause ‘distributor_name = ?;’, an ORA-00911 will appear. You will need to adjust by placing the end of the clause (the question mark) in quotes (‘distribute_name = ‘?”).

The error message can also result from copying and pasting SQL language into Oracle from another program. The ORA-00911 message may be prompted when this is done, due to non-printable characters from foreign programs not registering with Oracle. The easiest fix for this is to simply retype the SQL content as opposed to pasting, and then trying once again to execute it.

There are also numerous simple syntax errors that can throw an ORA-00911. One problem that can arise is inserting an extra semi-colon to the end of a query. This can easily happen when switching between new programming and copy and pasting from another program. This concept is furthered when accidentally adding an additional semi-colon to an ‘execute immediate’ query, or by adding this character to end a query executing from a program language such as java.

As seen by the set of examples above, an key practice that you should adopt while working in Oracle is being completely aware of when, what and how you are copying and pasting between programs. It can be all too easy to unknowingly string together invalid bits of code simply by writing part of a query, getting distracted, and then copying and pasting directly into the program. Familiarizing yourself with the purpose of semi-colons in Oracle can also go a long way in preventing ORA-00911 errors. If you find that you are uncertain about the proper SQL language to use in Oracle or how to appropriately end a query, you can always speak with an Oracle consultant on how to better familiarize yourself with the dynamics of properly programming an Oracle database.

Источник