dotConnect for Oracle的OracleScript類可以逐一執(zhí)行幾個SQL語句，例如，可以使用它來執(zhí)行來自級腳本文件的大量的腳本。但是如果這些腳本是針對于SQL*Plus的，就可能會包含一些命令，在OracleScript不支持。今天就來看看怎么樣在OracleScript上執(zhí)行這些腳本。

    OracleScript不支持SQL*Plus命令，比如說設置或是執(zhí)行。當OracleScript遇見一個這樣的命令，就會拋出一個異常。對于這樣的異常，dotConnect for Oracle提供了下面的腳本：

set define off;
select * from dept;
execute stored_procedure('parameter');

    每個SQL * Plus命令必須與新線（'\n'）字符結(jié)束，允許OracleScript檢測SQL*Plus命令的結(jié)束以及一個新的語句的開始。

    處理這樣的腳本，可以使用OracleScript對象的SqlStatementExecute事件。使用下面的代碼可以創(chuàng)建一個OracleScript對象。

[C#]

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

[Visual Basic]

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

    SqlStatementExecuteEventArgs類型有SqlPlusStatementType的屬性，這就決定了SQL * Plus命令的類型。因此我們可以判斷以及跳過各種不同的類型的SQL*Plus命令，或是在SqlStatementExecute事件處理程序上進行處理。下面的例子中，就是跳過命令以及在PL/SQL塊上終止執(zhí)行命令。

[C#]

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

[Visual Basic]

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