Skip to content

Instantly share code, notes, and snippets.

@darrenjrobinson
Last active October 31, 2020 06:29
Show Gist options
  • Save darrenjrobinson/07ea8902c137423eba10d8f0f777a86a to your computer and use it in GitHub Desktop.
Save darrenjrobinson/07ea8902c137423eba10d8f0f777a86a to your computer and use it in GitHub Desktop.
Execute Oracle SQL DB Stored Procedure using PowerShell and ODAC.NET (so no Oracle Client required). Associated blogpost https://blog.darrenjrobinson.com/using-powershell-to-query-oracle-dbs-without-using-the-oracle-client-oracle-data-provider-for-net/
# Path to ODAC.NET Installation
Add-Type -Path "c:\ODAC\odp.net\managed\common\Oracle.ManagedDataAccess.dll"
$reader = $false
# SQL DB Username, Password and DataSource Alias (as per tnsnames.ora)
$username = "SQLQueryUserName"
$password = "SQLUserPassword"
# Alias from TNSNames.ora
$datasource = "IDM"
# Store Proc variables
$any = 'user'
$variables = 'olddisplayName'
$tobepassed = 'newdisplayName'
$queryStatment = @"
DECLARE
result varchar2(100);
error varchar2(100);
BEGIN
your.stored.procedure('$($any)', '$($variables)', '$($tobepassed)', result, error);
END;
"@
$connectionString = 'User Id=' + $username + ';Password=' + $password + ';Data Source=' + $datasource
$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
# Open Connection
$connection.open()
$command = $connection.CreateCommand()
$command.CommandType = 'Text'
$command.CommandText = $queryStatment
try {
$reader = $command.ExecuteNonQuery()
if ($reader.Equals(-1)) {
write-host -ForegroundColor Green "Stored Procedure successfully called"
$connection.Close()
}
else {
write-host -ForegroundColor Red "Stored Procedure call failed"
}
} catch {
write-host -ForegroundColor Red $_.Exception.Message
} finally {
if ($connection.State -eq ‘Open’) {
$connection.close()
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment