oracle-databaseodp.netora-00911

Cannot executing a SQL query through ODP.NET - invalid character error


I'm trying to execute a SQL query through ODP.NET to create a table, but I always get an ORA-00911 'invalid character' error. The Errors object in the exception always has the text "ORA-00911: invalid character\n", even if there are no linebreaks in the SQL query itself.

The code I'm executing the SQL is this:

using (OracleConnection conn = new OracleConnection(<connection string>) {
using (OracleCommand command = conn.CreateCommand()) {
    conn.Open();
    command.CommandText = queryString;

    command.ExecuteNonQuery();         // exception always gets thrown here
}

queryString contains a single CREATE TABLE statement, which works fine when executed through SQL Developer

EDIT: the SQL I am executing is this:

CREATE TABLE "TESTSYNC"."NEWTABLE" (
  "COL1" NUMBER(*,0) NULL,
  "COL2" NUMBER(*,0) NULL
);

with linebreaks removed


Solution

  • Other people have come across this issue - ODP.NET does not support multiple SQL statements in a text command. The solution is to wrap it in a PL/SQL block with EXECUTE IMMEDIATE around each statement. This lack of support for ; seems incredibly boneheaded to me, and has not improved my opinion of the oracle development team.

    Furthermore, this seems to be an issue with oracle itself, as I have the same problems with the MS and ODBC oracle clients.