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