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

  1. Open Windows PowerShell Modules as an Administrator.



  2. Paste the following code as .ps1 file and execute it.

Code

  1. #Add the PowerShell snap in code
  2. Add-PSSnapIn Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue | Out-Null
  3. #Oracle Database Connection
  4. #Load the assembly file (Oracle.DataAccess.dll) from the server you are running the script from
  5. $AssemblyFile = “C:\oracle\product\11.2.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll"
  7. #Connection to the Oracle Database
  8. $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"
  10. #Select the columns you want from the Oracle Database
  11. $CommandText = "SELECT Emp FROM EmpDB"
  13. #Initiate the process
  14. [Reflection.Assembly]::LoadFile($AssemblyFile) | Out-Null
  15. $OracleConnection = New-Object -TypeName Oracle.DataAccess.Client.OracleConnection
  16. $OracleConnection.ConnectionString = $ConnectionString
  17. $OracleConnection.Open()
  19. #Your Database is connected
  20. Write-Host "Oracle Database Connected"
  22. #Load the Oracle Data in a Dataset
  23. $OracleCommand = New-Object -TypeName Oracle.DataAccess.Client.OracleCommand
  24. $OracleCommand.CommandText = $CommandText
  25. $OracleCommand.Connection = $OracleConnection
  26. #Use the Oracle Data Adapter
  27. $OracleDataAdapter = New-Object -TypeName Oracle.DataAccess.Client.OracleDataAdapter
  28. $OracleDataAdapter.SelectCommand = $OracleCommand
  29. #Create a new Data set
  30. $DataSet = New-Object -TypeName System.Data.DataSet
  31. $OracleDataAdapter.Fill($DataSet) | Out-Null
  32. #Dispose the connection
  33. $OracleDataAdapter.Dispose()
  34. $OracleCommand.Dispose()
  35. $OracleConnection.Close()
  36. $data= $DataSet.Tables[0]
  37. #You will have all the data without being connected stored in the dataset
  38. Write-Host $DataSet.Tables[0].Rows.Count

Prerequisites

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!