Instant Oracle Database and PowerShell How-to - Instant Oracle Database and PowerShell How-to - page 7

Timeout=10" -f $dataSource, $user, $pass)
}

$conn = Connect-Oracle (Get-ConnectionString HR pass localhost 1521
XE)
"Connection state is {0}, Server Version is {1}" -f $conn.State,
$conn.ServerVersion
$conn.Close()
"Connection state is {0}" -f $conn.State

How it works...

First a Connect-Oracle function is deined to create and open a connection. It requires
a connection string by throwing an error if it is not set. An OracleConnection objection
is then created using that connection string and the connection is opened. Finally, the
connection object is written to output with Write-Output $conn ; this could have also been
done with return $conn or just $conn . Using return can be deceiving because any output
of the function that is not captured will become part of the function return value and not just
what follows the return statement.

Get-ConnectionString takes in the key server and user data as arguments and uses the
-f format operator to concatenate a connection string for ODP.NET.

Next these functions are invoked with $conn = Connect-Oracle (Get-
ConnectionString HR pass localhost 1521 XE) ; note the parentheses to ensure
the appropriate order of operations and output. Also note that you may need quotes for some
argument values in certain cases (that is, $pass ). Finally the connection will be closed, with
some connection properties being output both before and after. The connection code could be
reduced but breaking the steps into functions makes the script more maintainable and reusable.

There's more...

For connection string help, try http://connectionstrings.com/oracle .

Let's explore some additional connection techniques to use.

Connecting with TNS Names
To connect via TNS you can change the $dataSource variable in Get-ConnectionString
to point to a valid TNS Names alias.

1. For example, $dataSource = "LOCALDEV" with LOCALDEV deined in ORACLE_

BASE\ORACLE_HOME\Network\Admin\tnsnames.ora can be deined as follows:
LOCALDEV =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))