CODEWITHSUNDEEP
c#oracle-databaseddl
user2516186
user2516186

Reputation: 483

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

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

Upvotes: 9

Views: 15902

Answers (4)

Marco Rodrigues
Marco Rodrigues

Reputation: 31

Thanks for the semicolon tip!

My final code to run oracle scripts!

1) It accepts: - empty lines / comment ( -- ) lines - Multiline DDl / DML commands ending with ;

2) In case of error it throws an exception with the line number and sql command!

    public async Task ExecuteScript(string _connectionString, string script)
    {
        using (StringReader sr = new StringReader(script))
        {
            var connection = new OracleConnection(_connectionString);
            connection.Open();

            string sqlCommand = "";
            string sqlLine; byte lineNum = 0;

            while ((sqlLine = sr.ReadLine()) != null)
            {
                sqlLine = sqlLine.Trim(); ++lineNum;

                if (sqlLine.Length > 0 && !sqlLine.StartsWith("--"))
                {
                     sqlCommand += (sqlCommand.Length > 0 ? Environment.NewLine : "") + sqlLine;  // Accept multiline SQL

                    if (sqlCommand.EndsWith(";"))
                    {
                        sqlCommand = sqlCommand.Substring(0, sqlCommand.Length - 1);

                        var command = new OracleCommand(sqlCommand, connection);

                        try
                        {
                            await command.ExecuteNonQueryAsync(); 
                        }
                        catch (OracleException ex)
                        {
                            connection.Close();
                            var e2 = new Exception($"{lineNum} - {sqlCommand} <br/> {ex.Message}");
                            throw e2;
                        }
                    }
                }
            }

            connection.Close();

            return;
        }
    }

Upvotes: 3

Anwer Matter
Anwer Matter

Reputation: 386

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

Upvotes: 12

GriffeyDog
GriffeyDog

Reputation: 8376

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.

Upvotes: 1

Steve
Steve

Reputation: 216293

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

Upvotes: 1

Related Questions