ExcelデータをOracleに格納する3つの手法：Excel‐Oracle連携（1）（3/3 ページ）

» 2005年07月08日 00時00分公開

[宮本尚志，日本オラクル]

Oracle Generic Connectivityを使用する方法

　Oracle Generic Connectivityは、Oracle以外のデータベースやExcelファイルなど、外部ソースをOracleデータベースのテーブルのように扱うことができる機能です。この機能をExcelファイルに対して使用すると、Excelファイル内のデータを外部表のように使用できます。SELECT文で直接Excelファイル内のデータを参照したり、UPDATE文でOracleデータベースからExcelファイルの中身を更新することが可能です。

　この機能は、複数人で共有しているExcelファイルのデータをOracleデータベースに格納する場合に適しています。Excelファイルをマスタ・データとして、Oracleデータベース内の表にExcelデータを反映させることも可能です。例えば、Excelファイルをファイルサーバ上で共有している場合に、ExcelからExcelファイルの更新を続けつつ、そのExcelファイルからOracleデータベース内の表にデータを定期的にコピーすることができます。また、SELECT文でExcelから検索できるため、特定の条件を満たすデータのみを抽出するといった柔軟な処理が可能です。

　ただし、Oracleデータベースが稼働するマシンがクライアントとしてExcelファイルにODBC接続するため、前提条件としてOracleデータベースのOSはWindowsである必要があり、データベース・サーバからアクセス可能なファイルシステム上にExcelファイルが置かれている必要があります。OracleGeneric ConnectivityはOracleデータベースの標準機能ですが、Windows（32bit）版の場合はOracleデータベースのバージョンが9.2以上である必要があります。

　この方法でExcelデータをOracleデータベース内に格納する際の手順は、以下のとおりです。

Excelファイルでの設定
データソースの設定
Oracleデータベースの設定
Excelファイルからのデータコピー

　設定部分は、一度設定してしまえばその後Excelファイルのデータが更新されても、Excelファイルの名前や場所が変わらない限り変更は不要です。OracleデータベースからバイナリのExcel（.xls）ファイルのデータを直接参照できるため、CSVに変換するというような手間もかかりません。

Excelファイルでの設定

　Excelファイルでの設定ですが、まずOracleデータベースから表として見る範囲に名前を付けます。図6のように、表となる範囲を選択状態にし、画面左上の名前ボックス（赤丸部分）に名前を入力してリターンキーを押下します（または、ツールバーから「挿入」→「名前」→「定義」とたどることでも入力可能です）。ここで入力した名前が、Oracleデータベースから接続したときの表名になります。ここでは「案件一覧表」と命名しています。

図6　Excelのデータ範囲に名前を付ける（画像をクリックすると拡大します）

　また、OracleデータベースからExcelファイルのデータを参照する際、同時にほかのユーザーがExcelファイルを開く可能性がある場合は、「ツール」→「ブックの共有」で、複数ユーザーによる同時編集を許可しておきます。

データソースの設定

　次に、Excelファイルをデータソースとして登録します。Oracleデータベースが稼働するサーバで、「コントロールパネル」→「管理ツール」→「データソース（ODBC）」とたどり、ODBCデータソースアドミニストレータを起動します。

　起動後「システムDSN」のタブを選択し、「追加」ボタンをクリックすると、「データソースの新規作成」という画面でドライバの一覧が表示されます。その中から「MicrosoftExcel Driver（*.xls）」を選択し、「完了」をクリックします。

　すると「ODBC Microsoft Excelセットアップ」というウィンドウが表示されます。ここでは「データソース名」に任意の名前を指定し、「ブックの選択」で先ほど設定作業を行ったExcelファイルを選択します。また、ここで「オプション」を選択し「読み取り専用」のチェックを外すことで、OracleデータベースからExcelファイルのデータを更新することも可能です。

Oracleデータベースの設定

　Oracleデータベース側では、初期化パラメータの確認と、設定ファイルの変更、データベース・リンクの作成を行います。

　まず初期化パラメータ「global_names」の値が「FALSE」になっていることを確認します。「TRUE」になっている場合は「FALSE」に変更する必要があります（デフォルト値は「FALSE」です）。

　次に設定ファイルの変更ですが、3つのファイルを編集する必要があります。1つ目は「%ORACLE_HOME%\network\admin\listener.ora」で、すでに記述されているリスナーの設定にリスト3の赤字部分を追記します（ORACLE_HOMEの値はすでに書かれているものと同一にします）。追記後リスナーの再起動を行います。

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SERVICE_NAME = orcl)

      (ORACLE_HOME = D:\oracle\ora10g)

    )

    (SID_DESC =

      (SID_NAME = hsodbc)

      (ORACLE_HOME = D:\oracle\ora10g)

      (PROGRAM = hsodbc)

    )

  )

リスト3　listener.oraの設定

　2つ目は「%ORACLE_HOME%\network\admin\tnsnames.ora」で、以下を追記します。ホスト名はデータベース・サーバのホスト名、ポート番号は前出のlistener.oraに記載されているポート番号（デフォルトは1521）を記述します。

ogctest =

  (DESCRIPTION = 

    (ADDRESS=(PROTOCOL=tcp)(HOST=<ホスト名>)(PORT=<ポート番号>))

    (CONNECT_DATA=

      (SID=hsodbc)

    )

    (HS=OK)

  )

リスト4　tnsnames.oraの設定

　最後に「%ORACLE_HOME%\hs\admin\inithsodbc.ora」ファイルを編集し、以下を記述します（<データソース名>には、「データソースの設定」で作成したデータソースの名前を記述します）。

HS_FDS_CONNECT_INFO = <データソース名>

HS_FDS_TRACE_LEVEL = 1

HS_FDS_TRACE_FILE_NAME = D:\Oracle\10.1.0.2\hs\HS.trc

リスト5　inithsodbc.oraの設定

　これらの設定ファイルを変更後、以下のようにデータベース・リンクを作成します（データベース・リンクを作成するには、CREATE DATABASE LINKシステム権限が必要です）。

SQL> create database link
atmarkit using 'ogctest';

Excelファイルからのデータコピー

　これでExcelファイルの中身をOracleデータベースから直接検索可能になりました。以下のようなSQL文を発行すれば、Excelファイルからの検索結果を基にデータベース内にテーブルを作成することができます。データ型はExcelシートのセルのデータ型を基に決定されます。

SQL> create table anken
as select * from "案件一覧表"@atmarkit;

　Excelファイルを検索対象にする際は、Excelファイル内で表部分に付けた名前を表名として指定し、その後に「@」に続けてデータベース・リンク名を指定します。また、Excelファイル内の表の列見出しをそのまま列名としてSQL文でも指定できます。検索だけでなく、Oracleデータベースから直接Excelファイル内のデータ更新も行うことができます。

　この機能とOracleデータベースのジョブ機能を組み合わせれば、「毎日24時に、Excelファイルのデータのうち条件を満たすものだけをデータベース内の表に反映させる」とか、「1カ月以上前のデータをExcelから定期的に削除する」といったことも可能です。複数の人で共有するExcelファイルを、Excelからの使用を継続しながらOracleデータベースにもデータを入れたい、という場合には最適です。

　ただし、この機能は複数のExcelファイルからのデータ格納には向きません。1つのExcelファイル内に複数の表形式データがある場合はこの方法で問題ありませんが、大量のExcelファイルからデータをOracleデータベースに取り込みたい、という場合にはSQL*Loaderの機能を使用することになります。