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
);
.sqlare 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.;character, which @JonHeller said should be removed.