ODBCドライバーを使ったSQL実装

前回も似たようなの作った気がする

odbc.ps1

# Load the required .NET assembly
Add-Type -Path "path/to/Oracle.ManagedDataAccess.dll"

# Connection string and query parameters
$connectionString = "User Id=your_user;Password=your_password;Data Source=your_data_source"
$query = "SELECT your_blob_column FROM your_table WHERE your_condition"
$outputFilePath = "path/to/output/file"

try {
    # Create and open the Oracle connection
    $conn = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
    $conn.Open()

    # Create the Oracle command
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = $query

    # Execute the query and get the data reader
    $reader = $cmd.ExecuteReader([System.Data.CommandBehavior]::SequentialAccess)

    if ($reader.Read()) {
        $bufferSize = 1024 * 1024 # 1MB buffer
        $buffer = New-Object byte[]($bufferSize)
        $fieldOffset = 0
        $bytesRead = 0

        # Create a file stream to write the BLOB data
        $fs = [System.IO.File]::Create($outputFilePath)

        do {
            $bytesRead = $reader.GetBytes(0, $fieldOffset, $buffer, 0, $bufferSize)
            $fs.Write($buffer, 0, $bytesRead)
            $fieldOffset += $bytesRead
        } while ($bytesRead -gt 0)

        # Close the file stream
        $fs.Close()

        Write-Output "BLOB data has been successfully written to the file."
    } else {
        Write-Output "No data found."
    }

    # Close the data reader and the connection
    $reader.Close()
    $conn.Close()
} catch {
    Write-Output "An error occurred: $_"
}