This post is designed to show how to execute Oracle Database queries using scripts written in PowerShell ISE. In this example, the connection details for the Oracle instance are being retrieved from a CSV, and the query is being executed. Once the query is executed, the results of the query are being outputted to a CSV file for consumption by another application.
- PowerShell ISE 5.0 or higher is being used
- Internet Connection is available
- Account with Local Admin privileges is being used
- Firewall access from Windows Machine (The server executing PowerShell Scripts) to Oracle Database is open
Installation Tasks and Steps
Task 1 – Download and Extract the Data Provider for Oracle (ODP.NET)
This first task is to download and install the ODP.NET module into PowerShell. The Module contains certain commands and parameters that need to be executed to complete this script. Follow the below instructions to install the module:
- Download the Oracle Data Provider for .NET (ODP.NET) – If the link does not work, just search for “Oracle ODP.NET Download” on the internet.
- Unzip the downloaded zip file and save it to the local machine
- The folder location will be referenced in the PowerShell script.
Task 2 – Create CSV File with Connection Details (Optional if CSV is not the required input method)
- Open a text editor to create the CSV file
- Create header columns with the connection details as a header
- Below is an example:
- Oracle Server Hostname:
- Below is an example:
- Save the file in a directory that can be navigated to as a CSV file
Task 3 – Complete the Script to execute the command
This script will execute the SQL Query and export it to a CSV file for consumption:
To conclude this article, this script is meant to allow an Administrator to execute Oracle SQL queries against an Oracle instance using PowerShell and export the results to a CSV file. This query is getting a list of all the database users in the Oracle Instance. This script should work with almost any query that can be executed.
When utilizing this script, please remember to check the same query in SQL Developer first to ensure that the results that return are the expected results. User permissions and database rights still apply when using this script.
Looking for additional help with querying the Oracle DB server for users using Powershell? ISX is an elite IAM security firm that offers boundless expertise in a range of cybersecurity and business process services, including Powershell. Take your interoperability to the next level, and contact an ISX consultant today.