PowerShell example for Oracle.DataAccess.Client

Add assembly to session

add-type -path $env:ORACLE_HOME\odp.net\bin\4\Oracle.DataAccess.dll

Connect to Oracle

$con   = new-object Oracle.DataAccess.Client.OracleConnection 'Data Source=ORA19;user id=rene;password=rene'
# $con = new-object Oracle.DataAccess.Client.OracleConnection 'Data Source=ORA19;user id=rene;proxy user id=/'

$con.Open()

$con.state # epxected: Open

Create a table

$stmt = $con.CreateCommand()
$stmt.CommandText = 'create table tq84_table (a number, b varchar2(20))'

if ($stmt.ExecuteNonQuery() -ne -1) {
 #
 # ExecuteNonQuery() returns the number of records processed.
 # For non-DML statements, such as this create table statement,
 # or generally, statments that don't contribute
 # to a rowcount, ExecuteNonQuery() returns -1.
 #
   write-host "unexpected"
}

Insert data

$ins_stmt = $con.CreateCommand()
$ins_stmt.CommandText = 'insert into tq84_table(a, b) values (:a, :b)'
$param_a  = $ins_stmt.Parameters.Add(':a' , [Oracle.DataAccess.Client.OracleDbType]::Decimal)
$param_b  = $ins_stmt.Parameters.Add(':b' , [Oracle.DataAccess.Client.OracleDbType]::Varchar2)

$param_a.value = 42
$param_b.value ='Hello world'
if ($ins_stmt.ExecuteNonQuery() -ne 1) {
   write-host "Expected: 1"
}

$param_a.value = 99
$param_b.value ='ninety-nine'
$ins_stmt.ExecuteNonQuery()
if ($ins_stmt.ExecuteNonQuery() -ne 1) {
   write-host "Expected: 1"
}

Commit

$stmt = $con.CreateCommand()
$stmt.CommandText = 'commit'

if ($stmt.ExecuteNonQuery() -ne -1) {
   write-host "expected: -1"
}

Select records

$sel_stmt = $con.CreateCommand()
$sel_stmt.CommandText = 'select b from tq84_table where a > :a'

$param_a = $sel_stmt.parameters.add(':a', [Oracle.DataAccess.Client.OracleDbType]::Decimal)
$param_a.Value = 40

$rdr = $sel_stmt.ExecuteReader()
while ($rdr.Read()) {
   write-host "$($rdr.GetOracleString(0).Value)"
}

Drop table

$stmt = $con.CreateCommand()
$stmt.CommandText = 'drop table tq84_table'

if ($stmt.ExecuteNonQuery() -ne -1) {
   write-host "expected: -1"
}

Calling a function (stored procedure)

$rnd_stmt = $con.CreateCommand()

$rnd_stmt.CommandType = [System.Data.CommandType]::StoredProcedure
$rnd_stmt.CommandText = 'dbms_random.value'

#
#  Returned parameter needs to be added first
#    (see https://support.oracle.com/knowledge/Oracle%20Database%20Products/261084_1.html)
#
$par_ret  = $rnd_stmt.parameters.add('ret'   ,  [Oracle.DataAccess.Client.OracleDbType]::Decimal, [System.Data.ParameterDirection]::ReturnValue)
$par_low  = $rnd_stmt.parameters.add('LOW'   ,  [Oracle.DataAccess.Client.OracleDbType]::Decimal, [System.Data.ParameterDirection]::Input      )
$par_high = $rnd_stmt.parameters.add('HIGH'  ,  [Oracle.DataAccess.Client.OracleDbType]::Decimal, [System.Data.ParameterDirection]::Input      )

$par_low.Value  = 10000
$par_high.Value = 10005

$null = $rnd_stmt.ExecuteNonQuery()
write-host "Random value from dbms_random: $($par_ret.Value.ToDouble())"

#
#  Get another random number
#
$par_low.Value  = 20000
$par_high.Value = 20005
$null = $rnd_stmt.ExecuteNonQuery()
write-host "Random value from dbms_random: $($par_ret.Value.ToDouble())"

Cleaning up

$con.Close()
$con.Dispose()

See also