ExcelでOracle接続(oo4o使用)

# はじめに
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の参照を追加します。

![p5.jpg](https://qiita-image-store.s3.amazonaws.com/0/189344/e87bbbfd-b35f-8fa4-a993-7d7c02f20edd.jpeg)

##3.2. マクロの実装
参照設定が完了したら、あとはVBAでデータベースに対する操作を実装するだけです。
ここまで来たらあとは、JavaやCなど、ほかのプログラミング言語で実装するのと同様の順で処理を書くだけなので、そう難しいものではありません。
以下は、サンプルコードを記載します。
DUAL表からシステム日付を取得するだけの簡単なサンプルです。
```vb:サンプルコード
'************************************************
' データ検索処理
'************************************************
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セルに日付が入力されることと思います。

■実行前
![p6.JPG](https://qiita-image-store.s3.amazonaws.com/0/189344/49bc8349-e308-b805-b5b1-d371cdaea68f.jpeg)

■実行後
![p7.JPG](https://qiita-image-store.s3.amazonaws.com/0/189344/6f89267b-d0d1-4fb4-cb7c-c796cbd71538.jpeg)

# 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