You might have already googled other pages to help you on this. You even probably have found some PowerShell function like the one below:

1
2
3
4
5
6
7
8
9

function OracleQuery($Instance,$User,$Password,$Query)
{
    $private:tmp = [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")
    $private:con = "Data Source=$instance;User Id=$user;Password=$password"   
    $private:ad = New-Object System.Data.OracleClient.OracleDataAdapter($query,$con)
    $private:ds = New-Object System.Data.DataSet
    $private:tmp = $ad.Fill($ds)
    return $ds.Tables[0]
}

Still something makes this one different. Here are some tricks used here:

1. Notice the variables are declared with private keyword. It means they will not be accessible outside of your function’s scope. You don’t see what you don’t need to see. It’s that simple.
2. Result of LoadWithPartialName function is saved into a temporary variable. Do you need its value? Most of the times, no. The point here is to prevent your pipeline from getting this object. Same is true for Fill method of OracleDataAdapter. Remember, PowerShell does not require a return statement to actually return something. What is not captured by you, goes to the output, which may lead into problems with later data processing.
3. A bit hard to find, but the objects being returned by this function are natively supported by PowerShell. It means you can select, where, sort, measure etc. with plain PowerShell, without having to know you’re actually dealing with ADO.NET objects.

Strangely enough, these rows always display as a list by default. To make a table from it, you need to use some PowerShell cmdlets and then add | ft to the query. Probably a bug in PowerShell 2.0, or I was just unlucky with the debugger.

Usage:

1

$q = OracleQuery -i "//MYSERVER/MYINSTANCE" -u "User" -p "Password" -q "SELECT * FROM my_table"