Example Of PowerShell (V3) & Oracle Data Access Components & ADO.net DbProviderFactory

PowerShell_ODAC.ps1

#
#  PowerShell (v3.0) + ODAC (and ADO.net)
#

function Load-DbProviderFactory([ScriptBlock[]]$loaders){
    $fa = $loaders | foreach {
        try{
            (& $_)| ? {$_ -is [System.Data.Common.DbProviderFactory]}
        }catch{}
    } | Select -First 1

    if($null -eq $fa){
        throw "Not Found DbProviderFactory"
    }else{
        $fa
    }
}

[System.Data.Common.DbProviderFactory]`
$factory = Load-DbProviderFactory {
    # ODAC 64bit
    $ODAC_DLL = "C:\Oracle\ODP.NET_Managed121010\odp.net\managed\common\Oracle.ManagedDataAccess.dll"
    [void][System.Reflection.Assembly]::LoadFile($ODAC_DLL)
    [Oracle.ManagedDataAccess.Client.OracleClientFactory]::Instance
},{
    # ADO.net OracleClient
    [System.Data.Common.DbProviderFactories]::GetFactory("System.Data.OracleClient")
}

# Connection String
$connStr = &{
    $csb = $factory.CreateConnectionStringBuilder()
    $csb["Data Source"] = "192.168.1.71:1521/XE"
    $csb["User Id"] = "SCOTT"
    $csb["Password"] = "TIGER"
    $csb.ConnectionString
}

$DStack = New-Object System.Collections.Generic.Stack[System.IDisposable]

function Release-All{
    $DStack | % {try{$_.Dispose()}catch{$Error[0]}}
    $DStack.Clear()
}

filter Push-D {
    if($null -ne $_){$DStack.Push($_)}
    $_
}

function New-DbConnection {
    $conn = $factory.CreateConnection() | Push-D
    $conn.ConnectionString = $connStr
    $conn.Open()
    $conn
}

function New-DbConnectionAndTransaction {
    $conn = New-DbConnection
    $tran = $conn.BeginTransaction() | Push-D
    @($conn, $tran)
}

function New-DbCommand ($conn, $tran, $commandText) {
    $cmd = $factory.CreateCommand() | Push-D
    $cmd.CommandText = $commandText
    $cmd.Connection = $conn
    $cmd.Transaction = $tran
    $cmd
}

function New-DataReader ($conn, $tran, $commandText) {
    (New-DbCommand $conn $tran $commandText).ExecuteReader()
}

<#
 # Business Logic
 #>
# 接続ユーザがオーナーのテーブル一覧を取得
function Get-TableNames ($conn, $tran) {
    $commandText = "
        SELECT OBJECT_NAME
        FROM USER_OBJECTS
        WHERE OBJECT_TYPE = 'TABLE'
    "
    (New-DataReader $conn $tran $commandText) | % { $_[0] }
}

# テーブル毎のデータ行数を取得
function Get-TableRowCountList ($conn, $tran) {
    
    $tableNames = Get-TableNames $conn $tran

    $subQueries = ($tableNames | % {
        "SELECT '$_' AS TABLE_NAME, COUNT(*) AS ROW_COUNT FROM `"$_`""
    }) -join "$([System.Environment]::NewLine)UNION "

    $commandText = "
        SELECT ROW_COUNT, TABLE_NAME
        FROM ($subQueries)
        WHERE ROW_COUNT > 0
        ORDER BY ROW_COUNT DESC, TABLE_NAME
    "

    (New-DataReader $conn $tran $commandText) | % {
        [PsCustomObject] @{
            ROW_COUNT = $_[0] -as [int]
            TABLE_NAME = $_[1] -as [string]
        }
    }
}

function main {
    $conn, $tran = New-DbConnectionAndTransaction
    Get-TableRowCountList $conn $tran | ft * -AutoSize
    #$tran.Commit()  # when DB Updated
}


try {
    main
}catch{
    $Error[0]
}finally{
    Release-All
}