Connect to Oracle DB with PowerShell

Here's template source code that connects to Oracle DB from PowerShell.

[void][reflection.assembly]::loadWithPartialName("System.Data.OracleClient")
$con = "Data Source=XXX;User ID=XXX;Password=XXX"
$connection = new-object system.data.oracleclient.oracleconnection($con)
$connection.Open()

#read from table
$sql = "select * from table where TAG = :TAG"
$cmd = system.data.oracleclient.oraclecommand
$cmd.connection = $connection
$cmd.commandtext = $sql
[void]$cmd.parameters.addwithvalue("TAG",$tag)
$reader = $cmd.executereader()
while($reader.read()){
	echo ("TAG = " + $read["TAG"])
}

$cmd.dispose()
$connection.close()

The Connection can be established without registration of oracle net service on client.

$con = "Data Source=("DESCRIPTION="
$con += "(CONNECT_DATA=(SERVICE_NAME=xxx))"
$con += "(ADDRESS=(PROTOCOL=TCP)(HOST=xx.xx.xx.xx)(PORT=xxx))"
$con += ");"
$con += "User Id=XXX;"
$con += "Password=XXX"

Off course, we can handle transaction. The SQL instructions in range from beginTransaction to Commit are treated as one procedure.
Not only AddWithValue function but also AddRange function is available to set bind parameters. Since we can specify the type of variable on argment of AddRange, by explicit type definition, the SQL becomes faster than with function AddWithValue.

[void][reflection.assembly]::loadWithPartialName("System.Data.OracleClient")
$con = "Data Source=XXX;User ID=XXX;Password=XXX"
$connection = new-object system.data.oracleclient.oracleconnection($con)
$connection.Open()
$transaction = $connection.begintransaction()

#update table
$sql = "insert into table (TAG) values (:TAG)"
$cmd = system.data.oracleclient.oraclecommand
$cmd.connection = $connection
$cmd.transaction = $transaction
$cmd.commandtext = $sql

#prepare parameters
$sqlparams = new-object system.data.oracleclient.oracleparameter [] 1
$sqlparams[0] = new-object system.data.oracleclient.oracleparameter(":TAG",[system.data.oracleclient.oracletype]::Char,10)
$cmd.parameters.addrange($sqlparams)

#execute
$sqlparam[0].value = "data"
[void]$cmd.executenonquey()
$transaction.commit()

$cmd.dispose()
$connection.close()