Instant Oracle Database and PowerShell How-to - Instant Oracle Database and PowerShell How-to - page 58

There is some risk and trial and error using this approach but you may ind the reward worth
it. Note ODT includes other assemblies such as Oracle.VsDevTools.dll but Oracle.
Management.Omo is the only logical choice from PowerShell.

Visualizing data (Advanced)

This recipe will explore visualizing Oracle data with charting using PowerShell.

Getting ready

Use Oracle.DataAccess.psm1 from the recipe Creating reusable script modules
(Advanced) , also included with the sample code iles for this recipe. You will need to
be able to load .NET 4.0 assemblies from PowerShell to use Microsoft Charting, which
is included with .NET Framework 4.0. See the Loading ODP.NET 4.0 section in the Accessing
Oracle recipe for more. Alternatively, you can install Microsoft Charting for .NET Framework 3.5
from http://www.microsoft.com/en-us/download/details.aspx?id=14422
without any changes for PowerShell v2.

How to do it...

1. Create Visualize.ps1 and add these script parameters:

param (
[string]$outputFile = ".\Salaries.png",
[string]$outputFormat = "PNG",
[bool]$interactive = $true,
[bool]$openImage = $false)

2. Deine a function to get average salary data by job id into a DataTable :
function Get-SalaryData {
Import-Module .\Oracle.DataAccess.psm1 -ArgumentList 2
$sql = "select job_id, round(avg(salary), 2) avg_sal from
hr.employees group by job_id order by job_id"
Connect-TNS -TNS LOCALDEV -UserId HR -Password pass
$dt = Get-DataTable -sql $sql
Disconnect; $dt
}

3. Load the assemblies we'll need for charting:

[void][Reflection.Assembly]::LoadWithPartialName("System.Windows.
Forms")
[void][Reflection.Assembly]::LoadWithPartialName("System.Windows.
Forms.DataVisualization")
Create a chart object and set up the title: