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.

Answer 1

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

Answer 2

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;")

Answer 3

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.

Answer 4

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.