1

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
);
7
  • The error suggests the problem is with the SQL in the file, which you haven't shown. Commented Feb 5, 2021 at 16:41
  • I'd guess there is a terminator problem. Files that end in .sql are usually meant to run in an environment like SQL*Plus that expects SQL to end with ; and PL/SQL to end with a /. But when you run the commands in most clients you must remove those terminators. But as Alex said, it would help to add more information about the contents of the file. Commented Feb 6, 2021 at 5:33
  • I've just edited post where content of the .sql file is mentioned. Commented Feb 8, 2021 at 8:40
  • And the content contains a terminating ; character, which @JonHeller said should be removed. Commented Feb 8, 2021 at 8:46
  • Unfortunately this does not help. Error message is the same. Commented Feb 8, 2021 at 8:59

1 Answer 1

2

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.

Sign up to request clarification or add additional context in comments.

1 Comment

thanks for the options. Indeed, example which I sent was rather simple as I didn't want to add many lines of code, but yes, I have .sql files which do have PL/SQL. I would like to avoid creation of SQL file parser which would give me an option of separate execution of statements and blocks. And if I break down your suggestions, there is nothing out of the box for such simple task. It seems I am stuck with SQL Plus... :(

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.