はじめに

OracleのデータをExcelに出力したい。または、Excelで編集したデータをOracleに投入したい。
というような場面がたまにあると思いますが、VBAでOracleに接続する方法の一つをまとめておきます。
今回この記事では、oo4oを使用した接続方法を紹介します。

1. 対象環境

今回記事の対象としている環境を記載します。

環境	バージョン
OS	Windows 7(64bit)
Excel	2010
接続先のOracleサーバー	Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
Oracleクライアント	Oracleクライアント11g

2. Oracle Clientのインストール

oo4oによる接続は、Oracleクライアントの機能が必要なので、つなぎたいサーバーに対応したOracleクライアントをインストールしておきます。
大体の開発環境にはすでに入っていると思いますが、もし入ってないという方はインストールしておいてください。
ただし、後述しますが、Excelは32bitアーキテクチャを使用しているので、Oracleクライアントは64bitのOSを使用していても、32bit用をインストールしてください。

3. マクロの実装

ここからは、VBAの実装に入ります。

3.1. マクロの参照設定

oo4oの機能を利用するには、「Oracle InProc Server」を参照する必要があります。
VBAエディターのツール＞ Oracle InProc Server 5.0 Type Libraryの参照を追加します。

3.2. マクロの実装

参照設定が完了したら、あとはVBAでデータベースに対する操作を実装するだけです。
ここまで来たらあとは、JavaやCなど、ほかのプログラミング言語で実装するのと同様の順で処理を書くだけなので、そう難しいものではありません。
以下は、サンプルコードを記載します。
DUAL表からシステム日付を取得するだけの簡単なサンプルです。

'************************************************
' データ検索処理
'************************************************
Sub SearchApperedData()
    On Error GoTo CatchErr

    ' Oracleのセッション
    Dim OraSess As Object
    ' OracleDB
    Dim OraDB As Object

    ' Oracleのセッションクリエイト
    Set OraSess = CreateObject("OracleInProcServer.XOraSession")
    ' データベース名、ユーザID、パスワード
    Set OraDB = OraSess.OpenDatabase("ORCL", "scott" & "/" & "tiger", 0&)

    ' SQLを変数としてセット
    Dim strSql As String
    strSql = "select SYSDATE from DUAL"
    ' 検索実行
    Set objRS = OraDB.CreateDynaset(strSql, 0&)

    ' *****************************************************
    ' 検索結果0件チェック
    ' 検索結果が1件以上であれば、シートに結果を出力します。
    ' *****************************************************
    If objRS.EOF = False Then
        Dim i As Integer
        i = 3

        ' *************************************************
        ' カーソルのフィールドに値があれば、
        ' カーソルの内容をループしてシートに入力します。
        ' このサンプルコードでは、結果は1件です。
        ' *************************************************
        If Not IsNull(objRS.Fields("SYSDATE").Value) Then
            'レコードの最後まで繰り返し
            Do While objRS.EOF = False
                Cells(i, "A").Value = objRS.Fields("SYSDATE").Value
                i = i + 1
                'カーソルを次のレコードに移動
                objRS.MoveNext
            Loop
        End If
    End If

    'オブジェクト開放
    objRS.Close
    Set objRS = Nothing
    OraDB.Close
    Set OraDB = Nothing
    Set OraSess = Nothing

    Exit Sub

    '============================================
    'エラーハンドリング
    '============================================
CatchErr:
    If (OraSess.LastServerErr <> 0) Then     'OraSession でエラー発生
        MsgBox OraSess.LastServerErrText        'エラー内容の表示
        OraSess.LastServerErrReset         'エラーのクリア
        Set OraSession = Nothing                'オブジェクト開放
    ElseIf (OraDB.LastServerErr <> 0) Then
        MsgBox OraDB.LastServerErrText
        OraDB.LastServerErrReset
        Set objRS = Nothing
        Set OraDB = Nothing
        Set OraSess = Nothing
    Else
        MsgBox Err.Description
        Set objRS = Nothing
        Set OraDB = Nothing
        Set OraSess = Nothing
    End If
End Sub

3.3. 実行

実装したサンプルコードを実行してみます。
ExcelのA3セルに日付が入力されることと思います。

■実行前

■実行後

4. サンプルコードの解説

JavaやCでデータベースを扱ったことのある方なら、特に難しいことはなかったのでないかと思いますが、サンプルコードの大まかな流れだけ解説しておきます。

4.1. セッションとDBオブジェクトの生成

Oracleとのセッションとデータベースを生成します。
VBAではセッションとデータベースは「Object」として保持します。
ますはセッションを生成し、そのセッションからデータベースオブジェクトを生成します。

サンプルではコード上に接続情報をそのまま記載していますが、Excel上のシートに入力したものを使用するようにした方が汎用的にできます。

4.2. クエリの実行

生成したデータベースオブジェクトに対し、クエリを実行します。
サンプルではSQLを変数に入れて使用していますが、もちろん直に引数に指定しても構いません。
クエリの実行には、「データベースオブジェクト.CreateDynaset()」を使用します。

4.3. 検索結果の表示

クエリの実行結果は、カーソルオブジェクトとして返却されます。
取得したカーソルオブジェクトの内容は、Do Whileを使用して、すべて参照できます。

サンプルコードでは、検索結果はSYSDATEの1件しかありませんが、複数のレコードを取得した場合は、ループ中で処理します。

4.4. オブジェクトの解放

最後に、お作法としてオブジェクトを解放して終了です。

4.5. エラーハンドリング

簡単なツール程度なら必要ないかもしれませんが、サンプルコードではエラーが発生した場合にエラーの内容を表示するようハンドリングしています。

5. 最後に

これで、Excel VBAでOracleに接続、データの取得が実行できました。
いかがでしたか？そう難しいものではなかったと思いますが、筆者が初めて実装した時は、参照の設定から全てを網羅している参考Webがなく、中々にハマったことを記憶しています。

サンプルには検索処理しかありませんが、当然ながらレコードの挿入や更新などもできますので、そちらも試してみてください。

EX. 参考Web

・小さいころはエラ呼吸
http://replication.hatenablog.com/entry/2014/09/16/090000

・ORACLE設定方法
https://www.microlab.jp/xcutedoc/Documents/sup_db/Oracle.htm

ExcelでOracle接続(oo4o使用)