Knight Talks Tech

Using Powershell to access Oracle

John

Before trying to access an Oracle database with Powershell it is necessary to have the Oracle Data Provide for .Net available, the most common way to get this is to install the Oracle client. If you are already interacting with Oracle databases then you will likely have a version of the client installed, if not use the link below to grab yourself a copy.
Oracle 11gR2 64-bit client – Windows 64-bit
Oracle 11gR2 32-bit client – Windows 32-bit
Oracle 12c 64-bit client – Windows 64-bit
Oracle 12c 32-bit client – Windows 32-bit
The 12c client can be used to connect to both 12c and 11gR2 databases.
When you install the client, you can select the Custom option and just choose the few components that you require including the Oracle Data Provider for .Net. You may also find it useful to include sqlplus and SQL Developer.
With that taken care off it’s time to fire up Powershell ISE (check out this post if you do not have PowerShell-ISE available) and get started…
You need the path to the Oracle.DataAccess.dll which can be found in your Oracle home, see my example below.

[Reflection.Assembly]::LoadFile("C:\Oracle\product\12.1.0\client_1\odp.net\bin\2.x\Oracle.DataAccess.dll")

The basics you need to take care of in order to connect to and query an Oracle DB are:

An Oracle connection object

As we have loaded the Oracle DLL we can do the following to create and open a connection to the database:

 $connString= "Data Source='<Oracle SID>'; User Id='SYS';Password='<password>'; DBA Privilege=SYSDBA;"
 $oraConn = New-Object Oracle.DataAccess.Client.OracleConnection($connString)
 $oraConn.Open()
We create a new variable, $connString, to hold the connection details (Oracle SID, username and password) which is then passed to the newly created OracleConnection object, $oraConn. We then call the Open() method of $oraConn.

A SQL query

For this example, I would like to retrieve the version of Oracle for the connected database. To do this we create a new variable, $sql, and assign the SQL string to it.

  $sql = 'SELECT version FROM v$instance'

An Oracle command object

 $oraCmd = new-Object   Oracle.DataAccess.Client.OracleCommand($sql, $oraConn)

We pass the SQL query ($sql) and the connection object ($oraConn) to OracleCommand which is instantiated as $oraCmd.

A way to obtain the data

The simplest way to read the data from the database is to use a data reader, we can then cycle through the results retrieved – in this example there will only be one record returned.

$reader=$oraCmd.ExecuteReader()
while ($reader.Read()) {
 $reader.GetString(0)
}

The above assumes that only one column of data is being return and is a string. If your query returns multiple columns of data then they can be accessed using a zero-based counter, e.g.

$reader=$oraCmd.ExecuteReader()
while ($reader.Read()) {
 $reader.GetString(0)
 $reader.GetDecimal(1)
 $reader.GetDateTime(2)
}

Finally, close the connection object.

$oraConn.Close()

You can see more of the data types handled by the data reader in this MSDN document.
So, what we should have now is a short and concise way to connect and query an Oracle database. The composite script below pulls together the steps detailed above.

[Reflection.Assembly]::LoadFile("C:\Oracle\product\12.1.0\client_1\odp.net\bin\2.x\Oracle.DataAccess.dll")
$connString= "Data Source=<Oracle SID>; User Id='SYS';Password='<password>'; DBA Privilege=SYSDBA;"
$oraConn = New-Object Oracle.DataAccess.Client.OracleConnection($connString)
$oraConn.Open()
$sql = 'SELECT version FROM v$instance'
$oraCmd = new-Object Oracle.DataAccess.Client.OracleCommand($sql, $oraConn)
$reader=$oraCmd.ExecuteReader()
while ($reader.Read()) {
 $reader.GetString(0)# + ', ' + $reader.GetString(0)
}
$oraConn.Close()

Hopefully you find this helpful. Having laid out the fundamentals, I’ll expand on this to show alternative ways of connecting and interacting with Oracle.
For articles on digital marketing, visit Christ Fellas.

Exit mobile version