c# - .NET / Oracle: How to execute a script with DDL statements programmatically

Question

I want to do some programmatical schema manipulation against an oracle database in C#. Therefore, I struggle with some basic issues.

The ddl sql statements are located in a script file. I do not want to use sqlplus.exe, but I want to use OracleCommand out of the ODP.NET assemblies (System.Oracle.DataAccess). Here's an example of my script file:

script.sql:

DROP TABLE ABCDEF; 

DROP TABLE GHIJKL;

I want to point out:

• The script contains DDL statements (data definition language)
• The script contains empty lines
• The script contains more than one statement

The following code should execute my script:

var content = File.ReadAllText("script.sql");

using (var oracleConnection = new OracleConnection(_connectionString))
{
     oracleConnection.Open();

     using (var command = new OracleCommand(content) { Connection = oracleConnection })
     {
          command.CommandType = CommandType.Text;
          command.ExecuteNonQuery();
     }
}

Executing this code, I do get an oracle error:

Oracle.DataAccess.Client.OracleException: ORA-00911: invalid character

Maybe there is some issue with the formatting of the statements, I think. Any hint is appreciated. Thank you.

---EDIT---

To summarize my needs in a simple way: I search for an approach to execute any sql/ddl script, that is executable by SQL Plus, programmatically with C#.

Answer 1

Simply wrap it inside BEGIN and END and it will work smoothly

var content =string.Format("BEGIN {0} END;", File.ReadAllText("script.sql"));
using (var oracleConnection = new OracleConnection(_connectionString))            
{
  oracleConnection.Open();
  using (var command = new OracleCommand(content) { Connection = oracleConnection })
  {
       command.CommandType = CommandType.Text;
       command.ExecuteNonQuery();
  }
}