Cannot executing a SQL query through ODP.NET - invalid character error
odp.net, ora-00911, oracle
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.
Problem
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