PowerShellでOracleに接続

Oracle Data Provider for .NET(ODP.NET)を利用する。

この辺りを参照

この辺りを参照

この辺りを参照

準備

  • ODP.NetCoreライブラリをダウンロード。

    odpcorepkg
    • ダウンロードしたnupkgファイルの拡張子をzipに変更後、解凍ソフト等で開く。

    • lib/netstandard2.0と辿りOracle.ManagedDataAccess.dllを適当なフォルダ(今回はc:\work)にコピーする。

  • GitHubからサンプルスキーマをダウンロード

    • 必須ではないが動作確認用にサンプルデータを作成するのが面倒だったため。
    • 中のSQLからとりあえずJOBSテーブルを作成。
    • jobsテーブル

PowerShellからDBを操作


#ライブラリファイルのロード
[void][reflection.assembly]::LoadFile("C:\Work\Oracle.ManagedDataAccess.dll")
#OracleDBへ接続
$conn = New-Object Oracle.ManagedDataAccess.Client.OracleConnection
$conn.ConnectionString = "User Id=<ID>;PassWord=<PassWord>;Data Source=<ホスト名またはIP:ポート番号/サービス名>;"
$conn.Open();

#SELECT文の発行
$sql = "select * from jobs"
$cmd = New-Object Oracle.ManagedDataAccess.Client.OracleCommand($sql, $conn)
$cmd.CommandType = [System.Data.CommandType]::Text
$reader = $cmd.ExecuteReader()
while ($reader.read())
{
    $reader.GetString(0)
    $reader.GetString(1)
    $reader.GetDecimal(2)
    $reader.GetDecimal(3)
    write-host ""         
}

#型の指定が面倒だったり、フィールドがわかりにくかったりする場合は以下の記述でも可
while ($reader.read())
{
    $reader["job_id"].tostring()
    $reader["job_title"].tostring()
    $reader["min_salary"].tostring()
    $reader["max_salary"].tostring()
    write-host ""         
}

#INSERT
$insertsql= "INSERT INTO jobs VALUES 
        ( 'SA_REP'
        , 'Sales Representative'
        , 6000
        , 12008
        )"
$cmd = New-Object Oracle.ManagedDataAccess.Client.OracleCommand($insertsql, $conn)
$cmd.CommandType = [System.Data.CommandType]::Text
$res = $cmd.ExecuteNonQuery()
#$resには件数がセットされる
$res

#UPDATE
$updatesql = "UPDATE jobs SET min_salary = 0 where job_id='SA_REP'"
$cmd = New-Object Oracle.ManagedDataAccess.Client.OracleCommand($updatesql, $conn)
$cmd.CommandType = [System.Data.CommandType]::Text
$res = $cmd.ExecuteNonQuery()
$res

#DELETE
$delsql = "DELETE FROM jobs WHERE job_id='SA_REP'"
$cmd = New-Object Oracle.ManagedDataAccess.Client.OracleCommand($delsql, $conn)
$cmd.CommandType = [System.Data.CommandType]::Text
$res = $cmd.ExecuteNonQuery()
$res

#トランザクション処理
#ODP.NETでのSQL操作はデフォルトでは自動コミットなので
#手動コミットやトランザクション処理したい場合は以下のように記述する
$updatesql = "UPDATE jobs SET min_salary = 0 where job_id='SA_REP'"
$cmd = New-Object Oracle.ManagedDataAccess.Client.OracleCommand($updatesql, $conn)
$cmd.CommandType = [System.Data.CommandType]::Text
$txn = $conn.BeginTransaction();
try {
    $res = $cmd.ExecuteNonQuery()
    if ($res -eq 1) {
        $txn.Commit()
        Write-Host "コミットしました"
    } else {
        throw
    }
} catch{
    $txn.Rollback();
    write-host "ロールバックしました"
}

#処理が終わったらコネクション、DataReaderオブジェクトの解放を必ず行う
$reader.Close()
$reader.Dispose()
$cmd.Dispose() #サイトのよってまちまちだったが念のため
$conn.Close()
$conn.Dispose()

以下のような記述でエラーになるので根本的な使用方法が違うかもしれない

#リファレンスを見るとここでAPIの設定を変更して接続時のデータソース名を作成できるはずだがORA-7800が発生して進まなかった。
[Oracle.ManagedDataAccess.Client.OracleConfiguration]::OracleDataSources.Add("CON_TEST","(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)(SERVER=dedicated)))")
$conn.ConnectionString = "User Id=ID;PassWord=PASS;Data Source=CON_TEST;"