Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Execute Oracle SQL DB Stored Procedure using PowerShell and ODAC.NET (so no Oracle Client required). Associated blogpost
# Path to ODAC.NET Installation
Add-Type -Path "c:\ODAC\\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 = @"
result varchar2(100);
error varchar2(100);
your.stored.procedure('$($any)', '$($variables)', '$($tobepassed)', result, error);
$connectionString = 'User Id=' + $username + ';Password=' + $password + ';Data Source=' + $datasource
$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
# Open Connection
$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"
else {
write-host -ForegroundColor Red "Stored Procedure call failed"
} catch {
write-host -ForegroundColor Red $_.Exception.Message
} finally {
if ($connection.State -eq ‘Open’) {
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.