Querying Oracle from Powershell Part 2

In part one we installed and configured the Oracle client software, in this post we will query an Oracle database from Powershell.  In addition we’ll look at one way to handle storing sensitive password information.

Querying and Oracle Database

To query an Oracle database we’ll use a function called Get-OLEDBData. The code listed below and is also available on PoshCode:

function Get-OLEDBData ($connectstring, $sql) {            
   $OLEDBConn = New-Object System.Data.OleDb.OleDbConnection($connectstring)            
   $OLEDBConn.open()            
   $readcmd = New-Object system.Data.OleDb.OleDbCommand($sql,$OLEDBConn)            
   $readcmd.CommandTimeout = '300'            
   $da = New-Object system.Data.OleDb.OleDbDataAdapter($readcmd)            
   $dt = New-Object system.Data.datatable            
   [void]$da.fill($dt)            
   $OLEDBConn.close()            
   return $dt            
}

The Get-OLEDBData function has been tested against SQL Server, Informix, Oracle and Excel data sources. In addition other data source can be addressed all that is needed is a valid connection string, the ability the data source to support OLEDB connections and of course the appropriate drivers. See connectionstring.com for a list of connection string examples. The hard part of querying an Oracle database from Powershell is setting up the Oracle Client software demonstrated in part one. Using the Get-OLEDBData function is simple, just pass a connection string and a query as follows:

$connString = "password=assword;User ID=SYSTEM;Data Source=XE;Provider=OraOLEDB.Oracle"            
$qry= "SELECT * FROM HR.DEPARTMENTS"            
./Get-OLEDBData $connString $qry

This will return all rows from the DEPARTMENTS table in HR schema:

oraclePowershell1

As long as your Oracle client software is installed and configured correctly and you have a valid connection string, specifying a database user with sufficient rights the query works. One issue immediately apparent is the sensitive password information. This especially true if you intend to use this technique for automated batch jobs. To address the password issue we’ll need to encrypt the connection string and the store the password somewhere. Let’s a look at one solution…

Encrypting Connection Strings

Ideally everything would use Windows authentication and you wouldn’t need to store password information. The reality is this simple isn’t the case especially with Oracle databases. Unfortunately there aren’t any native encryption cmdlets in Powershell (I’d love to see a cmdlet that would use certificates in order to avoid pass phrases), there are however a very nice and set of Powershell encryption functions created by Steven Hystad called Library-StringCrytpo.

To use the encryption functions download the Powershell script and source the library, then call the Write-EncryptedString function passing our connection string we want to encrypt with a passphrase. To decrypt the connection string call the Read-EncryptedString function with the encrypted string and passphrase.

#Source Encryption Functions            
. ./Library-StringCrypto.ps1            
#encrypt string using passphrase            
$encrypt = Write-EncryptedString $connString "4#&7yaoff"            
#Show encrypted string            
$encrypt            
#Decrypt string            
Read-EncryptedString $encrypt "4#&7yaoff"

The encrypt functions work well, but I like to do is then take the encrypted string and store it in a SQL Server table that is locked down. To do we’ll need to first create a table in SQL Server database as follows:

CREATE TABLE [dbo].[server_lku](
        [server_name] [varchar](255) NOT NULL,
        [server_type] [varchar](25) NOT NULL,
        [connection_string] [varchar](2000) NOT NULL,
        [is_encrypted] [bit] NOT NULL,
CONSTRAINT [PK_server_lku] PRIMARY KEY CLUSTERED
(
        [server_name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[server_lku] ADD  CONSTRAINT [DF_server_lku_is_encrypted]  DEFAULT ((0)) FOR [is_encrypted]
GO

Note: I’ve created the server_lku table on a server named win2k8r2sql2k8 in the database dbautility

In order to select and insert data into the server_lku table we’ll use the Get-SqlData and Set-SqlData functions from the LibrarySqlData script which I previously blogged about here.

Source LibrarySqlData , and insert the encrypted string along with few other pieces of information into our SQL table using the Set-SqlData function. To retrieve the encrypted connect string use the Get-SqlData function and then call the Read-EncryptedString function.

. ./LibrarySqlData.ps1            
set-sqldata  "win2k8r2sql2k8" dbautility "INSERT server_lku VALUES('XE','oracle','$encrypt',1)"            
#Retrive the unencrypted string            
$decryptedString = Get-sqldata  "win2k8r2sql2k8" dbautility "SELECT * FROM server_lku where server_name = 'XE'" | `
foreach { if($_.is_encrypted) {Read-EncryptedString $_.connection_string "4#&7yaoff"} else {$_.connection_string}}            
#Show decrypted string            
$decryptedString            
#Run query again            
./Get-OLEDBData $decryptedString $qry

Just as in our previous example all rows from the DEPARTMENTS table in the HR schema are returned:

oraclePowershell2

Querying Oracle databases from Powershell requires a little extra setup, but once you put these pieces in place you’re ready to start automating Oracle from Powershell!

About these ads
This entry was posted in PowerShell. Bookmark the permalink.

6 Responses to Querying Oracle from Powershell Part 2

  1. Asim says:

    Hi Chad,

    Thank you for putting thing document. I am new to PowerShell. I tried the same but I did not get anything in return. I am using Oracle 10g client and PowerShell v.2. I update the correct information for my Oracle DB, but when I run the scirpt; I get nothing in return. Any help you can provide will great. My end goal is to query the information related to Req ID and create an email using the information and send it out.

    $connString = “Provider=OraOLEDB.Oracle;Data Source=DM;User Id=MGMT;Password=pass;”

    $qry= “SELECT * FROM DM_REQUEST.REQ_ID”
    ./Get-OLEDBData $connString $qry”

    • cmille19 says:

      I see one small, issue with the code listing. The Get-OleDBData is a function which must be sourced prior to using. Change this:

      ./Get-OLEDBData $connString $qry

      to this

      . ./Get-OLEDBData.ps1
      Get-OLEDBData $connString $qry

      • Asim says:

        Thanks for a quick response. I made the change and reran the script still getting an error message. I have copy and pasted the exact script I am running. End result I want to achieve is query information from multiple table in Oracle and create an email using the information. I have figured out how to send email from PowerShell but can’t figure out how to query DB. I really appreciate any help you can provide.

        Thank you,
        Asim

        function Get-OLEDBData ($connectstring, $sql) {
        $OLEDBConn = New-Object System.Data.OleDb.OleDbConnection($connectstring)
        $OLEDBConn.open()
        $readcmd = New-Object system.Data.OleDb.OleDbCommand($sql,$OLEDBConn)
        $readcmd.CommandTimeout = ‘300’
        $da = New-Object system.Data.OleDb.OleDbDataAdapter($readcmd)
        $dt = New-Object system.Data.datatable
        [void]$da.fill($dt)
        $OLEDBConn.close()
        return $dt
        }

        $connString = “password=password;User ID=username;Data Source=DSN;Provider=OraOLEDB.Oracle”
        $qry= “SELECT * FROM GEN_REQ_DET.REQUEST_ID”

        ../Get-OLEDBData.ps1
        Get-OLEDBData $connString $qry

      • cmille19 says:

        Can you post the error mesage?

        Also you’re not using DSN entry are you? This should the the name of your Oracle database service name as defined in your tnsnames.ora

        In part one of this blog post I use the Oracle utility to define this
        Part One:
        http://cmille19.wordpress.com/2010/03/01/querying-oracle-from-powershell-part-1/
        Screenshot
        http://cmille19.files.wordpress.com/2010/03/oracleclient122.jpg

  2. John B says:

    Thanks very much for this great post. Very informational. I was wondering if anyone has run into the same issue as I have. I’m querying a table for results, where one of the columns is a timestamp format (i.e. MM/DD/YYYY hh:mm:ss.ttttttttt); however whenever I export the results the milliseconds get truncated. I’m fairly certain it’s a type conversion that’s going on, however I’m not certain where in the dataset object I can control this. Any suggestions?

    • cmille19 says:

      Haven’t seen that issue, but we’ve got a couple of things going here. First conversion of Oracle data types to .NET datatypes and second when you say export I’m thinking you mean to some type of text file. There’s a tostring method on datetime which looks to your culture setting. You can control the format of datetime when calling tostring with .NET datetime formats http://msdn.microsoft.com/en-us/library/az4se3k1(v=vs.71).aspx. So I would say its not a Powershell thing, but rather a .NET thing. You may get better answer than mine in on StackOverflow if you frame your question being about ADO.NET datetmine.

Comments are closed.