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

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

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

Tags:

C#

Oracle

Ddl