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"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"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$ora_server)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=$ora_servicename)));User Id=$ora_user;Password=$ora_pass;")


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

$list_set = new-object
$list_adapter = new-object$query, $connection)
$list_adapter.Fill($list_set) | Out-Null
$list_table = new-object
$list_table = $list_set.Tables[0]
foreach ($entry in $list_table) {


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s