Connecting to an Oracle Db from PowerShell

Sometimes you need to be able to connect to databases from PowerShell, in this case an I needed to connect to an Oracle database. First, you’ll need to download and install the Oracle client. In my case, I used the win64 11gR2 client. Make sure you get the appropriate version to suit the version of Oracle (e.g. Oracle 11g) and the Windows machine you are installing it on (win64). You can download clients from here.

After that, all you need is the PowerShell code and knowledge of the database you’re going to connect to. The PowerShell code below can be used to connect by either an Oracle SID or Service Name – choose which works best for you.

## To connect by Service Name
$ora_server = "hostname"
$ora_user = "username"
$ora_pass = "password"
$ora_servicename = "servicename"

## To connect by SID
$ora_server = "hostname"
$ora_user = "username"
$ora_pass = "password"
$ora_sid = "sid"

## by SID
$connection = new-object system.data.oracleclient.oracleconnection("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$ora_server)(PORT=1521)) (CONNECT_DATA=(SID=$ora_sid)));User Id=$ora_user;Password=$ora_pass;")

## by ServiceName
$connection = new-object system.data.oracleclient.oracleconnection("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$ora_server)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=$ora_servicename)));User Id=$ora_user;Password=$ora_pass;")

$connection.open()

$query = "select attribute from table where (attribute = 'value') order by attribute"

$list_set = new-object system.data.dataset
$list_adapter = new-object system.data.oracleclient.oracledataadapter($query, $connection)
$list_adapter.Fill($list_set) | Out-Null
$list_table = new-object system.data.datatable
$list_table = $list_set.Tables[0]
foreach ($entry in $list_table) {
}

$connection.close()
Advertisements