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

[SeetaRamayya]

Above posting was excelent. I have started working on powershell from last two days. I am able to connect to 10g but the same is not working with oracle 9i. Can you please suggest something here?

[wasabi]

I've been trying to use the dlls from the instant client but i can't seem to get it working. do you have any tips regarding powershell and oracle instant client?

[Mario]

Hi!

I try your code but i have this error... with the good path

C:\WebCheck> [Reflection.Assembly]::LoadFile("C:\Ora11g_Client\product\11.1.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll")

I try to found a solution on google but... see nothing.

Exception calling "LoadFile" with "1" argument(s): "Could not load file or assembly 'Oracle.DataAccess, Version=2.111.7.0, Culture=neutral, PublicKeyToken=89b483f429c47342' or one of its dependencies. An attempt was
made to load a program with an incorrect format."

If i use 1.x, no error but the rest of code not running...

Can you help me?

[Mario]

oups...
see the result with the 1.x
GAC Version Location --- ------- --------True v1.0.3705 C:\WINDOWS\assembly\GAC\Oracle.DataAccess\1.111.7.0__89b483f429c47342\Oracle.DataAccess.dll

[תנור מעבדה]

Excellent post shared by you here regarding oracle and powershell. It's really helpful to me and workout for me. I will access oracle data from powershell definitely in next sessions.

[Rob]

Hi all,

My query works unless I add a WHERE Clause. Any suggestions?

WORKS: "SELECT * from dba_jobs;"
DOES NOT WORK: "SELECT * from dba_jobs WHERE JOB = 1;"
DOES NOT WORK: "SELECT * from dba_jobs WHERE LOG_USER = 'SYS';"

BOTH NON WORKING GIVE ERROR:

Exception calling "Fill" with "1" argument(s): "ORA-00911: invalid character"

[מנהלי חשבונות]

Thanks for sharing technical post. I check out the link of GUI to dabble in powershell. It's very informative I read this blog deeply.