dbKoda for MongoDB

dbKoda for MongoDB - a modern open source database IDE, now available for MongoDB. Download it here!

Next Generation
Databases: NoSQL,
NewSQL and Big Data

Buy at Amazon

Read sample at Amazon

Buy at Apress

Latest Postings:

Search

Oracle Performance Survival Guide

Buy It
Read it on Safari
Scripts and Examples
Sample Chapter

                                    

Powered by Squarespace

 MySQL Stored procedure programming

Buy It
Read it on Safari
Scripts and Examples 

                                                

« Using _spin_count to reduce latch contention in 11g | Main | Systematic Oracle Performance Tuning »
Tuesday
Jan292008

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.





References (1)

References allow you to track sources for this article, as well as articles that were written in response to this article.
  • Response
    [...]Guy Harrison - Yet Another Database Blog - Accessing Oracle from Powershell[...]

Reader Comments (2)

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.
September 24, 2008 | Unregistered CommenterDiego Ucedo
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();
October 2, 2008 | Unregistered CommenterGuy Harrison

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>