.NET / Oracle: How to execute a script with DDL st

问题:

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#.

回答1:

As @Steve said, the semicolons are causing your error. And you can't wrap the entire file into a single execute immediate command, since that can only execute one statement at a time. You will need to parse your file and execute each command on its own, removing the semicolon that delinates commands. Your parsing will have to deal with string literals, as you noted, which in addition to containing semicolons may also contain doubled single quotes ('') within the single quotes (') that begin and end the string literal.

回答2:

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();
  }
}

回答3:

I will try to execute one line at time to see if you have any weird character that blocks the execution. (I am not sure also if you could send all of your commands together on just one call).

Also you should remove the semicolon at the end of the lines

int lineNum = 0;
try
{
    string[] cmdTexts = File.ReadAllLines("script.sql");

    using (var oracleConnection = new OracleConnection(_connectionString))
    {
         oracleConnection.Open();
         OracleCommand command = new OracleCommand();
         command.Connection = oracleConnection;
         foreach(string cmd in cmdTexts)
         {
              lineNum++;
              if(cmd.Trim().Length > 0)
              {
                  if(cmd.EndsWith(";"))
                      cmd = cmd.Substring(0, cmd.Length - 1);

                  command.CommandText = cmd;
                  command.ExecuteNonQuery();
              }
         }
    }
}
catch(Exception ex)
{
    MessageBox.Show("Exception on line: " + lineNum + " message: " + ex.Message);
}