Executing an Oracle sql script that has multiple statements

[sp00nyG]

I have a few SQL scripts that need to be executed on an Oracle DB server.
They each contain multiple SQL statements that contain ALTER TABLEs, CREATE
TABLEs, and stored procedures that need to be compiled. There are no
SELECT-type commands; all the scripts alter the database schema in some way.

Using sqlplus.exe the scripts run with no problem. However, these scripts
are executed as part of a product deployment (from an MSI). In order to
remove the dependency on sqlplus on the target machine (which potentially
could be missing) I am using C# (3.5) to connect to the DB directly via
OleDb.

Basically I will read in the entire contents of the SQL script as a string
and then pass it to OleDbCommand.ExecuteNonQuery(). The problem is that the
ExecuteNonQuery() method does not know how to handle multiple SQL statements.
This means that any semicolon ( or slash (/) statement separators cause
the entire transaction to fail with an "invalid character" exception.

I am able to execute a single-statement query on the DB without any
problems, so everything is wired up properly.

I cannot modify the SQL scripts that are being executed-- they are provided
to me from a separate development group and are expected to say intact,
meaning, the way they appear in source control is how they should appear on
the target machine. My only involvement in the process is to execute the
scripts when the product is installed.

I cannot seem to find a definitive answer on how this could be accomplished
with either System.Data.OleDb or the System.Data.OracleClient, or even
Oracle.DataAccess.Client. Surely there must be a way to emulate the
functionality of sqlplus.exe in code?

Thanks for any help in advance!

 

[Jonny Bergdahl]

That problem is not limited to Oracle. I had a similar problem with SQL
scripts for SQL Server, where embedded "GO" statements gave a similar
problem even though SQL Server supports multiple statements.

What I ended up doing was reading the script file one line at a time,
concatenate to form a complete SQL statements, and execute the statement
once complete. It goes like someting like this (but you would need to check
for the ";" statement terminator instead of "GO"):

using (StreamReader reader = new StreamReader(stream))
{
string sqlCommand = "";
string line;

while ((line = reader.ReadLine()) != null)
{
line = line.Trim();

if (line == "GO")
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = sqlCommand;
command.ExecuteNonQuery();
}
}
sqlCommand = "";
}
else
{
sqlCommand += line + "\n";
}
}

// This one is for any trailing SQL command not followed by a GO
if (sqlCommand != "")
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = sqlCommand;
command.ExecuteNonQuery();
}
}

Regards;
/jb

 

[Ray]

Actually Jonny "Go" is not part of the SQL Language. "GO" is a key word used
by various GUI and command Processors such as Query Analyzer and SSMS to
signify the end of a SQL statement batch consisting of one or more statements.
SQL server does not receive or process the "Go" command.