Run .sql file containing PL/SQL in PowerShell

Question

I would like to find a way to run .sql file containing PL/SQL in PowerShell using .NET Data Proider for Oracle (System.Data.OracleClient). I would deffinitely avoid using sqlplus for this task.

This is where I am now

add-type -AssemblyName System.Data.OracleClient

function Execute-OracleSQL
{
    Param
    (
        # UserName required to login
        [string]
        $UserName,

        # Password required to login 
        [string]
        $Password,

        # DataSource (This is the TNSNAME of the Oracle connection)
        [string]
        $DataSource,

        # SQL File to execute.
        [string]
        $File
    )

    Begin
    {
    }
    Process
    {   
        $FileLines = Get-Content $File
        $crlf = [System.Environment]::NewLine
        $Statement = [string]::Join($crlf,$FileLines)

        $connection_string = "User Id=$UserName;Password=$Password;Data Source=$DataSource"
        try{
            $con = New-Object System.Data.OracleClient.OracleConnection($connection_string)
            $con.Open()    
            $cmd = $con.CreateCommand()
            $cmd.CommandText = $Statement
            $cmd.ExecuteNonQuery();
        } catch {
            Write-Error (“Database Exception: {0}`n{1}” -f $con.ConnectionString, $_.Exception.ToString())
            stop-transcript
            exit 1
        } finally{
            if ($con.State -eq ‘Open’) { $con.close() }
        }
    }
    End
    {
    }
}

but I keep getting following error message "ORA-00933: SQL command not properly ended

The content of the file is pretty basic:

DROP TABLE <schema name>.<table name>;
create table <schema name>.<table name>
(
    seqtuninglog    NUMBER,
    sta             number,
    msg             varchar2(1000),
    usrupd          varchar2(20),
    datupd          date
);

Answer 1

The file does not contain PL/SQL. It contains two SQL statements, with a semicolon statement separator between (and another one at the end, which you've said you've removed).

You call ExecuteNonQuery with the contents of that file, but that can only execute a single statement, not two at once.

You have a few options. Off the top of my head and in no particular order:

a) split the statements into separate files, and have your script read and process them in the right order;

b) keep them in one file and have your script split that into multiple statements, based on the separating semicolon - which is a bit messy and gets nasty if you will actually have PL/SQL at some point, since that has semicolons with one 'statement' block, unless you change everything to use /;

c) wrap the statements in an anonymous PL/SQL in the file, but as you're using DDL (drop/create) those would also then have to change to dynamic SQL;

d) have your script wrap the file contents in an anonymous PL/SQL block, but then that would have to work out if there is DDL and make that dynamic on the fly;

e) find a library to deal with the statement manipulation so you don't have to work out all the edge cases and combinations (no idea if such a thing exists);

f) use SQL*Plus or SQLcl, which you said you want to avoid.

There may be other options but they all have pros and cons.