Ora 00911 invalid character ошибка

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?

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)

The correct way is to remove those character and try again

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

The correct query is

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

Oracle has improved this 11g and above

The correct way is to use single semi colon

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

Oracle has improved this 11g and above

The correct way is

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.

Correct way

We should enclose them in double quotes “”

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

Источник

ORA-00911: invalid character

Hi, I am using odp.net 11g beta2 accessing Oracle 10g database. I use TableAdapter to call a stored procedure in a package. In my stored procedure, if I use static SQL statement

it works fine. If I use an equivalent dynamic sql statement,

it gives me ORA-00911 error. Does odp.net support dynamic sql statement ?
Can I use static sql statement to generate Table Adapter, then swap to use dynamic sql statement at runtime ? If yes, how ?

Below is the error and stack trace

Thanks for the help.

What text is at line 78 of «VITALSTATSADM.PK_LOOKUP_TABLE»?

Can you call the code using SQL*Plus?

As this is PL/SQL code, ODP.NET is fairly insulated from the code — in this case it should only be passing input and output parameters to the PL/SQL code. I took your code and created a simple procedure in an 11.1.0.6 database and did not receive any errors using the values that I passed. If the code fails from SQL*Plus perhaps you can determine if a value with «special characters» or similar is being passed into the PL/SQL code.

On line 78, the code is

I can call the code in SQL Plus without problem.
Here is how I call it is SQL Plus.

The whole procedure is shown below.

If I swap the statement
OPEN l_cur FOR l_select;
with
open l_cur
for select lookup1_id as «ID»
, lookup1_desc as «DESC»
, isactive as «ACTIVE»
from lookup1 «LookUpTable»
order by Upper(‘DESC’);
then it works fine.

However, I need to use dynamic SQL statement to retrieve data from
different tables at different times.

I like the idea that PL/SQL code is insulated from odp.net, and that is my expectation too. In that case, I can fill one .Net datatable from multiple
Oracle tables that has similar structure through the use of stored procedure and dynamic SQL statement.

For the above problem, it looks like the error occurs on Oracle.DataAccess.Client.OracleCommand.ExecuteReader.
Seems that it has problem passing l_select.

I see you are passing NULLs in the SQL*Plus session. how are you passing NULLs from the .NET application?

Here’s a quick and dirty idea that may help to narrow down why you are getting the error. Basically you just create a table, a simple procedure, and then you call the procedure to insert the text.

For example:
Then you would add the following line to your VITALSTATSADM.PK_LOOKUP_TABLE.P_GET_LOOKUP_TABLE_DATA code:
Then run the .NET and once it completes/fails with the error you should be able to simply «select c from temp_text» to see what the created sql looks like. This might help determine why the ORA-911 is being raised. There are other ways to do this sort of thing too of course, but this is a pretty quick method if you can create the table and the new procedure.

Your debug code rocks. Now I know what went wrong.

In .Net, when I call the stored procedure, I’ve passed empty string or Nothing for Null, and both works fine.

My Scenario is as follows:-
In my web page, I have a drop down box and a gridview.
The dropdown box displays a list of table names. User selects one of them, and the gridview displays table data.

For the dropdown box, I added a blank entry (value 0) so that when the page loaded, the blank entry is the default value. As a result, Gridview has nothing to display because no table selected. The Gridview is wired up with TableAdapter via ObjectDataSource. While configuring ObjectDataSource, I specify the i_table parameter to use the selectedvalue of the dropdown box as its value.

I expect binding to the gridview will occur only after a new value is selected from dropdown box. But, when the page loads, the selectedvalue of the dropdown box is sent to TableAdapter, which in turn attempts to retrieve data from database.
Because tablename’s value is 0, following statement is executed.

select 0_id as «ID», 0_desc as «DESC», ISACTIVE as «ACTIVE» from 0 LookUpTable order by Upper(‘DESC’)

Obviously, this will cause ora-00911:invalid character error.

So my option here is either
1) Reconfigure ObjectDatasource not to use dropdown box value for parameter i_table, and write a few lines of code to call the Table Adapter’s GetData method in the SelectedIndexChanged event of the dropdown list method.
or
2) Create a dummy table that contains no data, and map the dropdown box blank entry’s value to the table name.

Thanks for the help again.
Wish you Merry X’mas and Happy New Year.

Источник

Ora 00911 error invalid character

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.

Источник

ORA-00911: invalid character

I am creating procedures in ODI so I could execute PL/SQL code over my tables in ODI.

I have tested this Store Procedure directly over SQL Developer and it works, but once I put in ODI on a procedure it does not work, and gives me ORA-00911

So what I have done is that I have taken that same code and executed it on SQL Developer and it execute quite properly!

Any ideas? or sugestion? I do really appreciate your time and guidance.

Parto of the SP (Store Procedure) code:
All the X are character from the ABC, so it will have just letters and vowels from A to Z.

create or replace
PROCEDURE MY_BEAUTIFUL_SP AS

XXXXXX XXXX XXX_X_XXX_2
XXXXXX XXXX XX XXXXX_XX,
XXXX XX XXXXXXX_XX,
XXXX XX XXX_XXX_XX,
XXX(XXX_XXXXX) XX X_XXX_XXX_X,
XXXX XX X_XXX_XXX_XXX_X,
XXXXXXX XX XXXXXXX_XXXX,
‘XXXXXXXXXXXX XXX XXXXXXXXXXXX’ XX XXX_XXX_XXXX
XXXX XXX_X_XXX_XXX
XXXXXXX XXXXXXX XXXXX
(
XXX_XXXXX XXX XXXXXXX XX
(
XXXXXXXXXX_XXXXX_XXXX XX ‘XXXXXXXXX XXXXX + XXXX’,
XXXXXXXXX_XXXXX XX ‘XXXXXXXXX XXXXX’,
XXXX XX ‘XXXX’,
XXXXXX_XXXXX XX ‘XXXXX’,
XXXXXXXX XX ‘XXXXXXXX’,
XXXXXXXX XX ‘XXXXXXXX’,
XXXXX XX ‘XXXXX’,
XXX_XXXXXXXXXXX XX ‘XXX XXXXXXXXXXX’,
XXXXXXXX XX ‘XXXXXXXX’,
XXX_XXXXXXX XX ‘XXX_XXXXXXX’,
XX XX ‘XX’,
XXXXXXXXXXXX XX ‘XXXXXXXXXXX’,
XXX XX ‘XXX’
)
)
XXXXX XX_XX_XXX = ‘XXXXXXXXXXXX XXX XXXXXXXXXXXX’
XXXXX XX XX_XX_XXX, XXXXXXX;

Источник

ORA-00911: invalid character in Dynamic SQL

I think I need another set of eyes. I’m not seeing the invalid character in this dynamically generated SQL statement:

Here is the full setup:

Here is result when I run this code:

Any help appreciated.

Best Answer

Remove semicolon at the end of the string and put single quotes while calling the function.

Answers

I think you should use

so you can see the content of it

I did use dbms_output.put_line — the result is at the very top of my post.

CAST(str2tbl( ‘1,2,4’ ) instead of CAST(str2tbl( 1,2,4 )

That is not working.

i_owner_tbl_arc varchar2(61) := ‘source_arc’;

i_owner_tbl varchar2(61) := ‘source’ ;

v_qry varchar2(32767) := ‘INSERT INTO ‘ || i_owner_tbl_arc ;

g_content_list varchar2(32767) := ‘1,2,4’;

v_qry := v_qry || chr(10) ||

‘select v.*, sysdate, »archived» ‘ || chr(10) ||

‘from ‘ || i_owner_tbl || ‘ v ‘ || chr(10) ||

‘where v.id IN ( SELECT * ‘ || chr(10) ||

‘ FROM TABLE( SELECT ‘ || chr(10) ||

‘ CAST(str2tbl( »’ || g_content_list || »’ ) ‘ || chr(10) ||

‘ AS myTableType ‘ || chr(10) ||

INSERT INTO source_arc

select v.*, sysdate, ‘archived’

where v.id IN ( SELECT *

FROM TABLE( SELECT

Now when I execute with g_content_list as a string I still get the same error:

i_owner_tbl_arc varchar2(61) := ‘source_arc’;

i_owner_tbl varchar2(61) := ‘source’ ;

v_qry varchar2(32767) := ‘INSERT INTO ‘ || i_owner_tbl_arc ;

g_content_list varchar2(32767) := ‘1,2,4’;

v_qry := v_qry || chr(10) ||

‘select v.*, sysdate, »archived» ‘ || chr(10) ||

‘from ‘ || i_owner_tbl || ‘ v ‘ || chr(10) ||

‘where v.id IN ( SELECT * ‘ || chr(10) ||

‘ FROM TABLE( SELECT ‘ || chr(10) ||

‘ CAST(str2tbl( »’ || g_content_list || »’ ) ‘ || chr(10) ||

‘ AS myTableType ‘ || chr(10) ||

execute immediate v_qry;

ERROR at line 1:

ORA-00911: invalid character

ORA-06512: at line 17

Remove semicolon at the end of the string and put single quotes while calling the function.

Could you add an exeption block into str2tbl function.

When building dynamic sql I’ve always been fond of using this structure ..

Makes things a lot easier to read, maintain and manipulate

Also avoids quote issues, and most other issues.

Why ? would be the relevant question.

Adding an exception block for the sake of adding an exception block = not very useful.

Adding it to help capture additional debug info = maybe useful ..

however, in this case, I don’t see the benefit. As others have pointed out, he just needs to spit out the value of the v_qry first . see what he’s actually building.

Don’t really need an exception block for that (although I suppose you *could* put that dbms_output in the exception block O_o )

Источник

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.