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

It then prompts for a username using Read-Host , invokes the function to get the password,
and injects those values into the connection string.

Retrieving data (Simple)

In this recipe we will look at the basics of retrieving data from Oracle in PowerShell.

Getting ready

Use code from the preceding recipes to load ODP.NET and establish a connection.

How to do it...

1. First create a function to return a .NET DataTable instance with a connection

and a SQL SELECT statement:
function Get-DataTable
{
Param(
[Parameter(Mandatory=$true)]
[Oracle.DataAccess.Client.OracleConnection]$conn,
[Parameter(Mandatory=$true)]
[string]$sql
)
$cmd = New-Object Oracle.DataAccess.Client.
OracleCommand($sql,$conn)
$da = New-Object Oracle.DataAccess.Client.
OracleDataAdapter($cmd)
$dt = New-Object System.Data.DataTable
[void]$da.Fill($dt)
return ,$dt
}

2. Connect as in the preceding recipe and call the function passing the connection

and SQL:
$conn = Connect-Oracle (Get-ConnectionString)
$dt = Get-DataTable $conn "select employee_id, first_name, last_
name, hire_date from employees where job_id = 'SA_MAN'"

3. Output a heading showing how many records were returned and then the raw data:

"Retrieved {0} records:" -f $dt.Rows.Count
$dt | ft -auto