ORA-00911: invalid character

Question

I create two table in my oracle (11g) database like this:

    create table "test" ("id" int);
    create table test ("id" int);

Then in my C# program there is a problem :

    OracleConnection conn = new OracleConnection(-myConnectionString-);
    conn.Open();
    OracleCommand command = new OracleCommand("select * from test;", conn);
    var v = command.ExecuteReader(); 
    OracleCommand command = new OracleCommand("select * from \"test\";", conn);
    var v = command.ExecuteReader();

for both command.ExecuteReader() I have an "ORA-00911: invalid character" error.

have you tried to execute this queries in some oracle client(either toad or pl-sql developer)? — alex.b, Sep 04 '12 at 11:09
@NikhilAgrawal quite a few dialects of SQL allow `;` as a separator between queries, so it might be that rather than C# that it's being confused with. — Jon Hanna, Sep 04 '12 at 11:10

score 173 · Accepted Answer · answered Sep 04 '12 at 11:05

173

Remove ; (semi-colon) from the end of SQL string

answered Sep 04 '12 at 11:05

Antonio Bakula

19,349
5
73
97

Just came accross. What if I need to execute 2 Update statements on 2 different tables on the same command? Putting semicolon produces an error. – SleepNot Mar 08 '13 at 09:31
its because the parameters are from a datareader and i need to execute 2 insert statements on 2 different tables while the datareader is reading. – SleepNot Mar 11 '13 at 04:04
I must say that I don't understant your scenario completly, better ask another question, and see this https://groups.google.com/forum/?fromgroups=#!topic/microsoft.public.data.odbc/hHwYSbmsvqc – Antonio Bakula Mar 11 '13 at 10:00
13

Sorry for swearing, but seriously wtf? Why wouldn't they accept semicolons in the query string? I wasted one and a half hours trying to figure out what was wrong with my very very simple db code. – Kang Min Yoo Mar 21 '14 at 05:28
1

I wanna upvote this a zillion times!!! Lost 2 hours beating my head, validating each field for nothing... Studpid Oracle Adapter! (and developer =/) – eestein Aug 07 '14 at 14:20
26

Why can't Oracle just say which character is invalid in their error message? – Jeff Bloom Mar 30 '15 at 18:31
Thank you! Was helpful to this date! :D – Indigo May 14 '15 at 20:48
2

I waste the whole afternoon to recognize my issue. I write multiple statements in a single command at firstly, so it's really a nightmare if a developer meet unfriendly 'invalid character' error message. – Bes Ley Sep 21 '15 at 10:27
They don't tell you what character is invalid because, compared to MSSQL, Oracle is trash. – Lucas925 Feb 11 '19 at 23:32

score 33 · Answer 2 · answered Aug 27 '13 at 03:12

33

In case other people wind up here looking for how to include multiple statements in a single command, you need to wrap your statements within begin and end. This will stop the invalid character errors due to the semi-colons. For example:

var command = new OracleCommand(@"
    begin
    select * from test;
    select * from test2;
    end;")

answered Aug 27 '13 at 03:12

John Galambos

2,763
2
22
23

1

Old answer but very useful! – GuFigueiredo Feb 03 '15 at 13:50
how to do multiple create? – Slovo Jun 29 '15 at 11:21
2

When I try to use this I get the following exception: "PLS-00428: an INTO clause is expected in this SELECT statement" – RBarryYoung Dec 15 '16 at 20:37
just tried it and came to the conclusion that this **does not work for DDL statements** as are in OP. if you need to create tables, views, procedures etc., the only solution seems to be in the accepted answer, remove the last semicolon. – Cee McSharpface Jan 21 '18 at 17:49

score 1 · Answer 3 · answered Sep 04 '12 at 11:05

Why are you using semicolon in the query...It just be taken as invalid character..... You have to remove the semicolon(;) from the query and do like this:

   OracleConnection conn = new OracleConnection(-myConnectionString-);
   conn.Open();
    OracleCommand command = new OracleCommand("select * from test", conn);
    var v = command.ExecuteReader(); 
    OracleCommand command = new OracleCommand("select * from \"test\"", conn);
    var v = command.ExecuteReader();

For more detail of this error, you can read here.

score 1 · Answer 4 · answered Feb 15 '13 at 16:36

This isn't this guy's problem, but hopefully this will help someone out there:

I often have this problem with single quotes hidden inside inline comments, like so:

select foo 
from bar
where 
/* some helpful comment with a "can't" or somesuch */
baz='qux'

The unmatched single quote in the comment causes all kinds of drama, and oracle doesn't go out of its way to help you figure that out.

score -1 · Answer 5 · edited Aug 06 '17 at 15:50

-1

Replace the sqldatasource parameter ? with :Column_name in the delete, update and insert commands.

edited Aug 06 '17 at 15:50

Frits

6,116
10
39
50

answered Aug 06 '17 at 13:37

Sohail

11
3

Are you asking a question or giving an answer here? – UmarZaii Aug 06 '17 at 13:44
Welcome to Stack Overflow! If you have another question, please ask it by clicking the [Ask Question](//stackoverflow.com/questions/ask) button. – ItamarG3 Aug 06 '17 at 14:59

ORA-00911: invalid character

5 Answers5

Linked