In our projects, we sometimes need to fetch the data from Oracle database and use it in our projects.
We will see a very simple executable script which will allow us to connect to the database very quickly and efficiently, and store it in a data set so that we don’t have to connect the database every time to get the data, as that will make the script really slow if you have thousands of data.
Let’s see how can we do it.
Steps
- Open Windows PowerShell Modules as an Administrator.
![]()
- Paste the following code 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"
-
- #Load the Oracle Data in a Dataset
- $OracleCommand = New-Object -TypeName Oracle.DataAccess.Client.OracleCommand
- $OracleCommand.CommandText = $CommandText
- $OracleCommand.Connection = $OracleConnection
- #Use the Oracle Data Adapter
- $OracleDataAdapter = New-Object -TypeName Oracle.DataAccess.Client.OracleDataAdapter
- $OracleDataAdapter.SelectCommand = $OracleCommand
- #Create a new Data set
- $DataSet = New-Object -TypeName System.Data.DataSet
- $OracleDataAdapter.Fill($DataSet) | Out-Null
- #Dispose the connection
- $OracleDataAdapter.Dispose()
- $OracleCommand.Dispose()
- $OracleConnection.Close()
- $data= $DataSet.Tables[0]
- #You will have all the data without being connected stored in the dataset
- Write-Host $DataSet.Tables[0].Rows.Count
Prerequisites
- Source URL
- Source port number
- Source database name
- Admin User id
- Password
The above parameters 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 data set will help you to have the complete Oracle data offline, therefore you don’t have to read the database over time, hence, saving your instance and time duration on it.
Here, in this article, we saw how to get Oracle Database content on a data set, using PowerShell Script.
Keep reading & keep learning!