Windows PowerShell for Oracle Oracle Tips by Burleson Consulting July 7, 2009

One of the greatest shortcomings of running Windows with Oracle is the serious limitations of the DOS command line scripting.  There are 3rd party Windows scripting tools such as UNIXDos, a toolkit from Professional Software Solutions that provides all of the UNIX-like functions. 

Microsoft released Windows Services For UNIX (SFU) to more closely emulate a variety of UNIX shells and UNIX utilities to ease the migration from a UNIX to a Windows environment.  While SFU is certainly a more comprehensive solution than the native command prompt, it is a more complicated and does not provide total compatibility for porting UNIX scripts to Windows.

Next, Microsoft has introduced Windows PowerShell for Oracle, a new command line interface, developed by Microsoft, the same people who brought you Windows Vista.  Windows PowerShell  is based on object-oriented programming and the Microsoft .NET framework.

Here is an example of Windows PowerShell script making a connection Oracle to execute SQL:

$connectionString
= "Data Source=XE;User Id=oracle;Password=mypass;
   Integrated Security=no"
[System.Reflection.Assembly]::
   LoadWithPartialName("System.Data.OracleClient")
$connection = New-Object
   System.Data.OracleClient.OracleConnection($connectionString)
$queryString = "SELECT COUNT(LAST_NAME) FROM EMPLOYEES"
$command = new-Object
   System.Data.OracleClient.OracleCommand($queryString, $connection)
$connection.Open()
$employeesNames = $command.ExecuteScalar()
echo "Number of employees: "$employeesNames
$connection.Close()

Result of the script:

PS
C:\scripts> .\connec_oracle.ps1

GAC   Version        Location
---   -------        --------
True  v2.0.50727     C:\WINDOWS\assembly\GAC_32\System.Data.
                     OracleClient\2.0.0.0__b77a5c561934e089\
                     System.Data.Orac...

Number of employees: 107

Let’s see the detail of the script:

$connectionString
   = "Data Source=XE;User Id=oracle;Password=mypass;
   Integrated Security=no"

We create a variable containing the connection string to the database:

• Data Source: the name of the database.
• User Id: username with whom you want to connect.
• Password: password for the user.
• Integrated Security = no.

Guy Harrison has these notes on connecting to Oracle with Windows PowerShell:

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 "
}