How to connect Oracle DB with PowerShell
1 2 |
# Load the ODP assembly [Reflection.Assembly]::LoadFile("C:\oracle\11.2.0\client_2\odp.net\bin\4\Oracle.DataAccess.dll")|Out-Null |
1 2 3 4 5 6 7 8 |
# #vars&cons $stations =@{ "424"="Istanbul"; "421"="İzmir" } $dbuser="user" $dbpasswd="password" |
connect to Oracle
1 2 3 4 5 6 7 8 |
$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
1 2 3 4 5 6 |
$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
1 2 3 4 5 6 7 8 |
# while ($reader.read()) { $stationID=$reader.GetString(0) $stationname=$stations.Get_Item($stationID) Write-Host " Station ID: $stationID ($stationname)" } |