Retrieve data from Oracle Database using Powershell

I wrote an article on getting data from a MS SQL Database some month ago. Afterwards i had a task to get information from another type of Database System, from an Oracle one. As you can browse my posts you can realize that i’m a fan of the Powershell so int he current case i also try to connect the DB by using Powershell. So here are my experinces…

It’s not a s simple as in case of a MS SQL database. First of all it’s very practical (with other words – necessary) to have an Oracle client on the same machine you are scripting at to have the correct System.Data.OracleClient dll file.

During the installation of the Oracle client you have to take care about entering the name of the given database. You have to provide full name here (provide the domain also). If you enter this parameter without the domain part you can have the next situation: the TNSPing command replies with the correct data while you ping the given database but on the GUI some unwanted errors come in front of you. So the perfect client configuration can be tested via the GUI. If you are able to connect to the given Database via the GUI without any error the Oracle client is perfectly configured. Don’t ask why.. that’s just an experience…

And here we are at the script part:

First load the Sharepoint assembly : [System.reflection.Assembly]::LoadWithPartialName(“Microsoft.SharePoint”)

Then get the SQL Select command from „somewhere” or define one. To tell the truth i’m not a real SQL query-builder so i usually ask for a hand from a TSQL expert to define me the query J So in our case i asked the exőert to build the query for me and place the command to a simple text file. Then i import this SQL command from that file (NOTE: you can place this file to a sharepoint folder and in the future the „customer” can modify his query for his needs by himself). So

$queryString = Get-content .\ORA_SQL_select.txt

Let’s connect to the DB:

#Open connection to the Oracle Database
$connectionString = “Data Source=smriport;User Id=fejleszto;Password=init;Integrated Security=no”
$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)
$command = new-Object System.Data.OracleClient.OracleCommand($queryString, $connection)
$connection.Open()
Write-Host -ForegroundColor White "  Opening Connection to Oracle Database"
Start-Sleep -Seconds 2

It worths to keep the sleep command int he code to be sure that the connection is established before trying to retrieve data from the DB. Let’s get the data!

#Getting Data from Oracle Database
Write-Host
Write-Host -ForegroundColor White "  Getting Data from Oracle database"
$Oracle_data=$command.ExecuteReader()
Start-Sleep -Seconds 2

if ($Oracle_data.read()) {
Write-Host -ForegroundColor Green "    Connection Success"
Write-Host
Write-Host -ForegroundColor White "  Importing Oracle data to MOSS-List (Nyitott Incidensek)"
} else {
Write-Host -ForegroundColor Red "    Connection Failed"
}

# After successfuly getting the data the $Oracle_data.read() method will return to a TRUE for you. Let’s have a look at the data!

foreach ($Oracle_item in $command.ExecuteReader()) {
trap [System.SystemException] {
$ID= $Oracle_item.GetDecimal(0).tostring()
continue
}
$ID= $Oracle_item.GetDecimal(0).tostring()

}

Hey, that’s so simple!  oh no.. you should be sure that so much time was spent to troubleshoot this little method. Well, when you are on to get data by the select command you should know the type of the varius data you would like to retrieve (defined in the select). Types could be: DateTime, String, Decimal etc. It worths to get the type of the given variable: $Oracle_item | gm

If you have a look at the code above carefully you should notice that you have to provide a number. This number represents the value in the row of the data in the „Oracle record”. That also could be found in the select command.

Then how could we decide wether the variable contains real value or not. If there is no real value then the next command will return a big red error message to you 🙂 $ID=$Oracle_item.GetDecimal(0).tostring() so then examine the $Error variable.But ont he other we can ise the try and catch method for that purpose also.

Well i hope that i was able to help you in that task. Keep an eye on LetITknow 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s