概要

Oracle に限らずデータベースにはバイナリーデータを保存できる型があります。それらは BLOB (Binary Large Object) 型と呼ばれます。

BLOB 型のデータはバイナリーなので、他の型とはずいぶん違う扱いをする必要があります。例えば、文字列でないので単純に INSERT 文を作って実行するなどはできないし、SELECT で持ってきたデータをそのまま表示できないか、表示できたとしても全データの確認は不可能です。

ここでは、Oracle 11g について、BLOB データ (以下の例では画像データ) の挿入と取得について述べますが、Oracle での BLOB データの扱いはやや面倒です。

DIRECTORY オブジェクトが必要（バイナリーファイルを置いておく場所）
データ挿入先の BLOB 型フィールドは、挿入前に初期化しておく必要がある。
ごく短いバイナリーデータ以外は INSERT でデータ挿入できない。このため、専用のプロシージャを作る必要がある。

DIRECTORY オブジェクトの作成

SQL Developer で「ディレクトリ」の下に DIRECTORY オブジェクトを作成します。この例では名前を TEMPDIR、場所を C:\Temp にしています。

BLOB フィールドの初期化 (小さい BLOB データの挿入)

HEXTORAW() 関数を使って、BLOB フィールドにダミーデータを挿入します。アイコン画像程度のサイズなら、ダミーデータでなく、直接、画像データを挿入できます。この関数にはサイズの制限があって、大きい画像 (デジカメ画像とか) ではエラーになってしまいます。このとき、他のフィールドもいっしょに挿入します。

下のサンプルで、ダミーデータ部分は HEXTORAW('00000000') です。テーブルの構造は次のようになっています。

ID : 主キー
TITLE : 画像のタイトル
DATATYPE : データ種別 (JPEG とか)
DATA : バイナリーデータ
WIDTH : 画像の幅 (画像でない場合は 0)
HEIGHT : 画像の高さ (画像でない場合は 0)
ORIGINAL : 元の画像ファイルのパス名
INFO : 画像の説明など

' ダミーのバイナリーデータを挿入
sql = $"INSERT INTO BINARIES(ID, TITLE, DATATYPE, DATA, WIDTH, HEIGHT, ORIGINAL, INFO) VALUES({Id}, '{title}', '{dataType}', HEXTORAW('00000000'), {sz.Width}, {sz.Height}, '{original}', '{info}')"
n = Execute(sql)  ' Execute(sql) は sql を実行する独自ヘルパーメソッド。

アイコン程度の小さい画像なら、次のようにすれば直接、挿入できます。

Private Function GetImageDataHex(ByVal buffer As Byte()) As String

'''<summary>
''' 画像ファイルの内容をヘキサ文字列で返す。
''' </summary>
''' <returns></returns>
Private Function GetImageDataHex(ByVal buffer As Byte()) As String
    Dim sb As New System.Text.StringBuilder
    Dim str As String
    For i = 1 To buffer.Length
        str = String.Format("{0:x2}", buffer(i - 1))
        sb.Append(str)
    Next
    Return sb.ToString()
End Function

Private Function GetImageData(ByVal filePath As String) As Byte()

'''<summary>
''' 画像ファイルの内容をバイト配列で返す。
''' </summary>
''' <param name="filePath"></param>
''' <returns></returns>
Private Function GetImageData(ByVal filePath As String) As Byte()
    Dim b(1024) As Byte
    Dim n As Integer
    Dim buffer As New List(Of Byte)
    Using reader As New FileStream(filePath, FileMode.Open, FileAccess.Read)
        While True
            n = reader.Read(b, 0, b.Length)
            If n = b.Length Then
                buffer.AddRange(b)
            Else
                For i = 1 To n
                    buffer.Add(b(i - 1))
                Next
                Exit While
            End If
        End While
    End Using
    Return buffer.ToArray()
End Function

画像ファイルを読んでフィールドに直接挿入

この場合には、Oracle の DIRECTORY オブジェクトで指定したフォルダは関係ありません。

Dim buffer As Byte() = GetImageData(original)  ' original は元の画像ファイルのパス名
sql = $"INSERT INTO BINARIES(ID, TITLE, DATATYPE, DATA, WIDTH, HEIGHT, ORIGINAL, INFO) VALUES({Id}, '{title}', '{dataType}', HEXTORAW('{hex}'), {sz.Width}, {sz.Height}, '{original}', '{info}')"
Dim hex As String = GetImageDataHex(buffer)
n = Execute(sql)

プロシージャの作成とコンパイル

BLOB フィールドにダミーデータが入っているものとして、それを実際の画像データで置き換えるプロシージャが必要になります。このプロシージャは、DIRECTRY オブジェクトで指定したフォルダ上の画像ファイルを読み込んで、先に挿入しておいた BLOB フィールドのダミーデータを置き換えます。(次の「BLOB データの挿入」で詳細を記載)

作成したプロシージャは、コンパイルして DB に登録します。ここでは、SET_IMAGE_DATA という名前にしています。

-- BINARIES テーブルの ID で指定されたレコードの DATA フィールドの更新
-- sDIR フォルダの sFILE という画像ファイル(バイナリーファイル) を nID で指定した DATA フィールドにロードする。
-- CREATE DIRECTORY TempDir AS 'C:\\Temp' でディレクトリを定義しておく必要がある。
-- (SYSDBA にて GRANT しておく必要がある)
--    GRANT CREATE ANY DIRECTORY TO USER1;
CREATE OR REPLACE PROCEDURE SET_IMAGE_DATA(nID IN NUMBER, sFILE IN VARCHAR2)
IS
  wkBlob  BLOB;
  wkBFile BFILE;
BEGIN
  -- BINARIES テーブルの 主キーが nID という DATA フィールドを更新する準備を行う。
  SELECT DATA INTO wkBlob FROM BINARIES WHERE ID = nID FOR UPDATE;
  -- BFILE を作成
  wkBFile := BFILENAME('TEMPDIR', sFILE);
  -- ファイルを開く。
  DBMS_LOB.FILEOPEN(wkBFile, DBMS_LOB.FILE_READONLY);
  -- ファイルからバイナリーデータをロードする。
  DBMS_LOB.LOADFROMFILE(wkBlob, wkBfile, DBMS_LOB.GETLENGTH(wkBfile));
  -- ファイルを閉じる。
  DBMS_LOB.FILECLOSE(wkBfile);
END;

BLOB データの挿入

タイトルは「データの挿入」になっていますが、実際は前に作成したプロシージャを実行して、ダミーデータを実際の画像データで置き換えます。

画像ファイルは、必ず Oracle の DIRECTORY オブジェクトで指定したフォルダへコピーする必要があります。

    ''' <summary>
    ''' SET_IMAGE_DATA プロシージャを実行する。
    ''' </summary>
    ''' <param name="id">ID</param>
    ''' <param name="fileName">ファイル名</param>
    ''' <returns></returns>
    Public Function SetImageData(ByVal id As Integer, ByVal fileName As String) As Integer
        Dim command1 As New OracleCommand()
        command1.Connection = Connection
        command1.CommandText = "SET_IMAGE_DATA"
        command1.CommandType = CommandType.StoredProcedure
        command1.Parameters.Add("nID", OracleDbType.Int32, ParameterDirection.Input)
        command1.Parameters("nID").Value = id
        command1.Parameters.Add("sFILE", OracleDbType.Varchar2, ParameterDirection.Input)
        command1.Parameters("sFILE").Value = fileName
        Dim n As Integer = command1.ExecuteNonQuery()
        command1.Dispose()
        Return n
    End Function

BLOB データの取り出し

BLOB データの取り出しですが、普通に SELECT 文で取得します。ただ中身は文字列ではなくバイナリーデータなので、ストリーム (MemoryStream) を使って Image オブジェクトに変換します。

    ''' <summary>
    ''' 画像本体を得る。
    ''' </summary>
    ''' <param name="id"></param>
    ''' <returns></returns>
    Public Function GetImageBody(ByVal id As Integer) As Image
        Dim table As DataTable = Query("SELECT ""DATA"" FROM BINARIES WHERE ID = " & id)
        If table.Rows.Count = 0 Then
            Return Nothing
        End If
        Dim row As DataRow = table.Rows(0)
        Dim buffer As Byte() = row(0)
        Dim im As Image = Image.FromStream(New MemoryStream(buffer))
        Return im
    End Function

Oracle BLOB データの扱い (.NET)

概要