Querying Oracle through Powershell

I’ve been dealing with some back pain this summer/fall that has made fishing uncomfortable.  I’m still able to do it, but when you’re in constant pain, it takes the fun out of doing pretty much everything but laying down.  That, along with the general busyness of the summer and fall, have kept the fishing down to a minimum.  However, I did get out for a weekend and found that while musky fishing was difficult, slip-bobber fishing was better.  I’d never slip-bobber fished until this year, but I have found it’s really enjoyable.  You can get the bait right above the bottom at any depth, and that presentation seems to work well when the fish aren’t as aggressive.

This brings me to my second note – I love catching smallmouth bass.  They fight really hard for their size, with deep runs and long battles.  Below is one of two that I caught at dusk on a weedy finger that stuck out into the main basin of the lake.

late-summer-smallmouth-bass

As you’d expect, querying Oracle database from Powershell isn’t as easy as querying SQL Server.  Powershell and SQL Server are both Microsoft technologies, so they play together pretty nicely.  With Oracle, their are a couple ways to connect to the database.  You can use the OLE DB database connector or the Oracle Client version.  I tried using the OLE DB connector first, but found I was unable to get it going.  So I switched over to the Oracle Client, which was a bit more complicated in the setup, but worked.

In order to use the Oracle Client, you have to install it on your system first.  I will warn you that this is not a minor install.  The installer itself is 600 MB, and you need to make sure you grab the correct version (32 vs 64 bit), depending on your OS.  Once installed you should be ready to go.  Here is the basic pattern for creating the connection and querying data:

#Create connection to Oracle
 $OracleconnectionString = "Data Source=OracleDatabaseServer;User Id=dgugg;Password=myPass;Integrated Security=No"
 $OracleSchedulerQueryString = "select IMAGE_ID, DESCRIPTION from FMSH_IMAGE WHERE Rownum < 11 ORDER BY IMAGE_ID DESC"
 [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | Out-Null
 $OracleConnection = New-Object System.Data.OracleClient.OracleConnection($OracleConnectionString)
 $OracleCommand = New-Object System.Data.OracleClient.OracleCommand($OracleSchedulerQueryString, $OracleConnection)
 $OracleConnection.Open()
 $OraImages = $OracleCommand.ExecuteReader()
 $OraCounter = $OraImages.FieldCount
 While ($OraImages.Read()) {
    for ($o=0; $o -lt $OraCounter; $o = $o++ ) {
        $OraImages.GetName($o) = $OraImages.GetValue($o)
        }
    }
$OracleConnection.Close()

Here is the gist of the code:

  • Define a connection string to the Oracle database.
  • Define your SQL statement
  • Load the Oracle Client Assembly into this instance of Powershell
  • Define and create your Oracle Connection Object
  • Define and create your Oracle command
  • Open your connection to Oracle
  • Execute your SQL into a recordset
  • Loop through the recordset from beginning to end
    • In each loop, print out the field name and field value

There are a few gotchas to keep in mind:

  1. Don’t end your SQL statement with a semicolon.  I did this and couldn’t figure out why it wasn’t working.  Once I took it out it was smooth sailing.
  2. Don’t forget to escape Powershell special characters.  I was doing a query from the V$Database view and again ran into problems.  Once I escaped the dollar sign ($) with a back tick (`), everything once again started working.

Stay tuned for my final blog in this series where I will use this connection to Oracle as well as the previously posted connection to SQL Server to create a workbook that has daily monitoring of my database health stats.

Querying SQL Server through Powershell

I got Up North this last weekend.  It was the first weekend in a couple months, and I definitely haven’t had the time for fishing like I have in years past.  This last weekend was no exception, as we spent most of the time re-sealing the logs in Grandpa’s cabin.

log-sealing-cabin

I did get out for an hour or two of row-trolling, but didn’t manage any bites.  It’s a bit sad because usually this is my favorite time of year to fish, but there have just been too many other things going on this year.  I did get the top picture above of the lake in the early morning behind the rowboat.  I love the look of a still lake in the morning.

I have found a great use for Powershell in my daily DBA tasks.  I use it to create an Excel workbook each day with various health checks on my SQL Servers.  It is great to look in a single workbook and see all the potential issues across all the servers highlighted.  I have also begun to add my Oracle servers to the same process.  In this post I’m going to share the basics for connecting to SQL Server.  The next post will do the same for Oracle, and the final post in this series will show my full script for creating the workbook.

#Load the dll that contains the SQL Server objects. 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
#Create the SQL Server instance.  This will be the domain name of the server 
$instance = mySQLServer 
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

It’s that easy.  Now within the SQL Server, I can reference different areas I may want to monitor:

#SQL Agent jobs 
$jobs=$srv.JobServer.Jobs 

#Databases 
$dbs = $srv.Databases 
#Loop through each database 
ForEach ($db in $dbs) 
{ 
#Database Name 
$db.Name 
#Last backup date 
$db.LastBackupDate 
}

Stay tuned for my next post which will show how to connect to and query Oracle database.  The final post in this series will show how to use the above script to create an Excel workbook that gives an awesome summary of the health of my SQL Server databases and Oracle databases.