28 Apr 15 How to connect Oracle DB with PowerShell

# Load the ODP assembly 
[Reflection.Assembly]::LoadFile("C:\oracle\11.2.0\client_2\odp.net\bin\4\Oracle.DataAccess.dll")|Out-Null

# 
#vars&cons
$stations =@{
"424"="Istanbul";
"421"="İzmir"
}
$dbuser="user"
$dbpasswd="password"

connect to Oracle

$constr =
"
User Id=$dbuser;
Password=$dbpasswd;
Data Source=CITIES
"
$conn= New-Object Oracle.DataAccess.Client.OracleConnection($constr)
$conn.Open()

Create a datareader for a SQL statement

$sql="select stationid
from CITIES.SWITCHPARAMETER
where switchid = '2'
"
$command = New-Object Oracle.DataAccess.Client.OracleCommand( $sql,$conn)
$reader=$command.ExecuteReader()

Write out the results

# 
while ($reader.read()) {
$stationID=$reader.GetString(0)
$stationname=$stations.Get_Item($stationID)
Write-Host
"
Station ID: $stationID ($stationname)"
}

Tags: oracle, powershell