How To Connect Oracle Database Using PowerShell Script

Welcome to a blog on how to connect Oracle database, using PowerShell script. In our projects, we sometimes need to retrieve the data from Oracle database and use it in our projects.

We will see a very simple executable script, which will allow you to connect to the database very quicky and efficiently.

Steps

1. Open Windows PowerShell modules as an administrator.
   
   
   
   
2. Paste the code, given below as .ps1 file and execute it.

Code

#Add the PowerShell snap in code
Add-PSSnapIn Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue | Out-Null
#Oracle Database Connection
#Load the assembly file (Oracle.DataAccess.dll) from the server you are running the script from
$AssemblyFile = “C:\oracle\product\11.2.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll"

#Connection to the Oracle Database
$ConnectionString = "Data Source=Provide your source:Provide your port number / Provide your source name;User Id=Your User ID;Password=Your Password;Persist Security Info=True"

#Select the columns you want from the Oracle Database
$CommandText = "SELECT Emp FROM EmpDB"

#Initiate the process
[Reflection.Assembly]::LoadFile($AssemblyFile) | Out-Null
$OracleConnection = New-Object -TypeName Oracle.DataAccess.Client.OracleConnection
$OracleConnection.ConnectionString = $ConnectionString
$OracleConnection.Open()

#Your Database is connected
Write-Host "Oracle Database Connected"

Pre-requisites

• Source URL
• Source port number
• Source database name
• Admin User Id
• Password

The parameters shown above are required from your end, while connecting to the database. Once you get the correct parameters and execute the script, you will get a message “Oracle Database Connected”.

This connection can be used to view or edit the data in any Oracle database. Here, in this blog, we saw how to connect Oracle database, using PowerShell Script.

Keep reading & keep learning!