Accessing Oracle from Powershell

I've used perl extensively - and other scripting languages from time to time - to write little unitities or simulations for Oracle.  Perl is available on windows, but it doesn't provide easy access to all the Windows API and .NET utilities (such as accessing WMI counters).  For that PowerShell would be the best choice.

It's not entirely obvious how to connect to oracle from Powershell.  Here's how I did it.

You need to install the Oracle Data Provider for .NET.  Once that is done, you load it in powershell using this command:

# Load the ODP assembly
[Reflection.Assembly]::LoadFile("C:\oracle\10g\ODP.NET\bin\2.x\Oracle.DataAccess.dll")

Of course, "c:\oracle\10g" represents my Oracle home.  It's non-trivial to get this from the registry so make sure that this path is valid for you.

Now we can use the ODP.NET variants of standard ADO.NET  calls to connect to Oracle and extract results.  I setup my connection to Oracle as follows:

#connect to Oracle
$constr = "User Id=system;Password=manager;Data Source=gh10gb"
$conn= New-Object Oracle.DataAccess.Client.OracleConnection($constr)
$conn.Open()

My TNS alias was "gh10gb".  Now I have a connection handle $conn, I can create a data reader object for a SQL query:

# Create a datareader for a SQL statement
$sql="select * from all_users"
$command = New-Object Oracle.DataAccess.Client.OracleCommand( $sql,$conn)
$reader=$command.ExecuteReader()

From this point on, everything is ADO.NET standard.  You can look at the structure of the result set as so:

# Write out the result set structure
for ($i=0;$i -lt $reader.FieldCount;$i++) {
    Write-Host  $reader.GetName($i) $reader.GetDataTypeName($i)
}

And write out the results like this:

# Write out the results
while ($reader.read()) {
    $username=$reader.GetString(0) 
    $userid=$reader.GetDecimal(1)
    $createDate=$reader.GetDateTime(2)
   
    Write-Host "$userid $username $createDate "
}

The ADO.NET constructs are fairly clumsy compared to perl DBI, but they get the job done. And if you want to write some code that integrates Windows APIs with Oracle database access, Powershell is probably a good choice (A C# console application would be the alternative I suppose).  I have to perform some experiments shortly on latch contention in which I'll be tracking WMI counters and v$latch statistics concurrently and PowerShell should provide a useful means of doing that.

Quest sponsors one of the powershell GUIs out there.  If you need a GUI to dabble in powershell, check out www.powergui.org.

Comments

[Diego Ucedo]

Very useful piece of code.I am wonder if it is possible to call a stored procedure from powershell scripting. This is a requirement for performance reason. Thanks.

[Guy Harrison]

Two methods for calling stored procedures. Firstly, if you just surround the stored procedure call with BEGIN .. END, then you can issue it as a NonQuery command. Eg:

$sql="begin mystoredproc; end;"$command = New-Object Oracle.DataAccess.Client.OracleCommand( $sql,$conn)$command.ExecuteNonQuery();

A slightly more correct way to do this would be to set the CommandType to "StoredProcedure". Then you don't need the BEGIN END:

$sql="mystoredproc"$spCommand=New-Object Oracle.DataAccess.Client.OracleCommand( $sql,$conn)$spCommand.CommandType="StoredProcedure"$spCommand.ExecuteNonQuery();