Post
Ask Question

How To Get Oracle Data In A Data Set Using PowerShell Script

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"  
  6.  
  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"  
  9.  
  10. #Select the columns you want from the Oracle Database  
  11. $CommandText = "SELECT Emp FROM EmpDB"  
  12.  
  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()  
  18.  
  19. #Your Database is connected  
  20. Write-Host "Oracle Database Connected"  
  21.  
  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

  • 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!
Next Recommended Reading Insert Data Into SQL Server Table Using PowerShell