Excel(VBA)でOracleとの連携(oo4o)

ExcelでOracle Databaseからデータを取得する機会があり、Oracleに詳しくないこともあり、 VBAでexcelに接続する方法を調べたので纏めておく。

主要な方法としては３つあるらしい

  • ActiveXData Objects（以下ADO）からODBCドライバを使用する方法
  • ADOからOLE DBドライバを使用する方法
  • Oracle Objectsfor OLE（以下oo4o）を使用する方法

今回はoo4oを使用する方法についてまとめる。

環境
Excel 2010(32bit)
OS Windows7(64bit)
Oracle Oracle Database Standard Edition One 11.2.0.4.v16

手順

  1. 公式サイトに行って、ODACパッケージをダウンロード。
    http://www.oracle.com/technetwork/jp/database/windows/downloads/utilsoft-087491-ja.html
    パッケージの中に含まれているOracle Objects for OLE(OO4O)を利用する。

  2. ODACパッケージを任意のディレクトリへ展開
    展開先のディレクトリに存在するinstall.batを起動して、OO4Oをインストール
    コマンド実行例

  3. 再起動 OO4Oインストールはレジストリ登録を伴うため、VBA使用前に念のため再起動

  4. ExcelでVBAプロジェクトの参照可能なライブラリファイルの追加
    ツールから参照設定、Oracle InProc Server 5.0 Type LibraryにチェックしてOK

  5. VBAでマクロを作成

VBA サンプル

下記はOracleに接続して、ホスト名を取得し、セルに出力するサンプル

Sub Sample()
 
    'セッション
    Dim OracleSession As Object
    'データベース
    Dim OracleDB As Object
    'SQL
    Dim sqlString As String
    
    'ホスト名を取得
    sqlString = " SELECT HOST_NAME FROM V$INSTANCE"
    

    'セッションオブジェクト作成(OO4O)
    Set OracleSession = CreateObject("OracleInProcServer.XOraSession")
    'tns名 user pass を指定
    Set OracleDB = OracleSession.OpenDatabase("<tns>", "<user>" & "/" & "<pass>", 0&)
    
    ' 検索実行
    Set objRe = OracleDB.CreateDynaset(sqlString, 0&)

    ' 0件チェック
    If objRe.EOF = False Then
        Dim i As Integer
        ' ホスト名のフィールドに検索結果が存在すればシートのA列1行目に取得したホスト名を出力
        If Not IsNull(objRe.Fields("HOST_NAME").Value) Then
                Cells(1, "A").Value = objRe.Fields("HOST_NAME").Value
        End If
    End If

    '各オブジェクト開放
    objRe.Close
    Set objRe = Nothing
    OracleDB.Close
    Set OracleDB = Nothing
    Set OracleSession = Nothing
    
End Sub

コマンド実行例

(展開ディレクトリ) > install.bat oo4o c:\oracle orahome true

引数１: 個別にインストールするコンポーネント
引数２: ORACLE_HOME パス
引数３: ORACLE_HOME 名
引数４: 依存性のあるコンポーネントを合わせてインストールするかどうか

参考