Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
You can’t perform that action at this time.