Connecting PowerShell scripts to a database gives us a huge buster to process automation and processes/systems monitoring.
We can apply it to many different solutions as well as bypass, extend, or improve the functionality of other systems.
A few practical/real examples how this can be applied :
- to store a script data and configuration (it is more flexible than hard-coded parameters or configuration (.ini) files,
- read and process data from other systems (great for processes automation),
- monitor other systems activity and alert users and admins when some events occur,
- enter manipulated/calculated data directly to other systems’ databases.
I also created a solution that monitors all configuration changes in different systems and creates an external audit trail functionality that can detect changes (and send a notification) applied directly to the database.
But let’s talk about efficient technics of working with databases from PowerShell.
The Oracle Database is one of the most advanced, enterprise-level database systems. I use PowerShell very often to create monitoring or data manipulation solutions for advanced applications, that are not easily maintain or the maintenance cost is very high.
You can find a few different methods of how to accessing Oracle database from PowerShell (e.g. using sqlplus or ExecuteReader(), but I will describe the easiest and the fastest method.
Module installation
You will need ADO .NET provider (Oracle.ManagedDataAccess.dll) for Oracle.
You can find here information about how to download it: https://docs.oracle.com/database/121/ODPNT/installODPmd.htm#ODPNT8149
No module installation is needed. Just add the .NET Core class (Oracle.ManagedDataAccess.dll file) as to the PowerShell session by adding at the beginning of your script:
Add-Type -Path "<path>\Oracle.ManagedDataAccess.dll"
Use example
After loading the .NET Core class, follow the steps:
$OraConn = New-Object Oracle.ManagedDataAccess.Client.OracleConnection("<connection string>")
$OraCmd = New-Object Oracle.ManagedDataAccess.Client.OracleCommand
$OraCmd.FetchSize = $OraCmd.FetchSize * 64
$OraCmd.Connection = $OraConn
$OraCmd.CommandText = "<query>"
$OraConn.Open()
$OraAdap = New-Object Oracle.ManagedDataAccess.Client.OracleDataAdapter($oraCmd)
$DataTable = New-object "System.Data.DataTable"
$OraAdap.Fill($DataTable) | Out-Null
$results = $DataTable | Select $DataTable.Columns.ColumnName
$oraConn.Close()
The explanation of a few things:
"<connection string>" – this is the connection string to the database, like:
“Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ServerName)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ServiceName)));User Id=User; Password=Password“
$OraCmd.FetchSize = $OraCmd.FetchSize * 64 – to speed up a bigger data reading, I increased the reading buffer 64 times (from 127KB to 8MB). You will see a huge difference in data read speed.
$OraAdap.Fill($DataTable) | Out-Null – pumps data to an in-memory data table (I added “| Out-Null” to prevent displaying messages on the screen – it is important if you create a function that returns $results)
I hope that this post will be helpful in database access directly from PowerShell.
