OracleScript Hints for SQL*Plus Users
In This Topic
OracleScript class allows you to execute several SQL statements one by one. For example, you can use it to execute large scripts from script files. However, if these scripts were written for SQL*Plus, they may contain some commands, unsupported in OracleScript. This article offers some hints on how to execute such scripts with OracleScript.
OracleScript does not support SQL*Plus commands, for example SET or EXECUTE. When it encounters such a command, it throws an exception. Suppose, we have the following script with such commands:
set define off;
select * from dept;
execute stored_procedure('parameter');
Each SQL*Plus command must end with the new line ('\n') character. It allows OracleScript to detect the end of the SQL*Plus command and the beginning of a new statement.
To process such a script, you may use the SqlStatementExecute event of the OracleScript object. Create an OracleScript object with the following code:
OracleConnection conn = new OracleConnection("connection string");
static void Main(string[] args)
{
OracleConnection conn = new OracleConnection("connection string");
conn.Open();
OracleScript script = new OracleScript("script text", conn);
script.SqlStatementExecute += new SqlStatementExecuteEventHandler(script_SqlStatementExecute);
script.Execute();
conn.Close();
}
Private conn As New OracleConnection("connection string")
Shared Sub Main(args As String())
Dim conn As New OracleConnection("connection string")
conn.Open()
Dim script As New OracleScript("script text", conn)
script.SqlStatementExecute += New SqlStatementExecuteEventHandler(script_SqlStatementExecute)
script.Execute()
conn.Close()
End Sub
The SqlStatementExecuteEventArgs type has the SqlPlusStatementType property, which determines the type of the SQL*Plus command. So, we can determine and either skip different kinds of SQL*Plus commands or process them manually in the SqlStatementExecute event handler.
In our example we skip the SET commands and enclose EXECUTE commands in PL/SQL blocks.
static void script_SqlStatementExecute(object sender, SqlStatementExecuteEventArgs e)
{
//we skip executing Set commands
if (e.SqlPlusStatementType == SqlPlusStatementType.Set)
{
e.StatementStatus = Devart.Common.SqlStatementStatus.SkipStatement;
return;
}
//we process the execute command - it must be executed inside the PL/SQL block
if (e.SqlPlusStatementType == SqlPlusStatementType.Execute)
{
var script=(OracleScript)sender;
e.StatementStatus = Devart.Common.SqlStatementStatus.SkipStatement;
OracleCommand comm = new OracleCommand();
comm.Connection = script.Connection;
comm.CommandText = "begin " + e.Text.Remove(0, "execute".Length).Trim() + "; end;";
comm.ExecuteNonQuery();
comm.Dispose();
return;
}
}
Private Shared Sub script_SqlStatementExecute(sender As Object, e As SqlStatementExecuteEventArgs)
'we skip executing Set commands
If e.SqlPlusStatementType = SqlPlusStatementType.[Set] Then
e.StatementStatus = Devart.Common.SqlStatementStatus.SkipStatement
Return
End If
'we process the execute command - it must be executed inside the PL/SQL block
If e.SqlPlusStatementType = SqlPlusStatementType.Execute Then
Dim script = DirectCast(sender, OracleScript)
e.StatementStatus = Devart.Common.SqlStatementStatus.SkipStatement
Dim comm As New OracleCommand()
comm.Connection = script.Connection
comm.CommandText = "begin " & e.Text.Remove(0, "execute".Length).Trim() & "; end;"
comm.ExecuteNonQuery()
comm.Dispose()
Return
End If
End Sub
See Also
Devart.Data.Oracle.OracleAlerter class