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

Excel‐Oracle連携(1):ExcelデータをOracleに格納する3つの手法 (3/3)

[宮本尚志,日本オラクル]
前のページへ 1|2|3       

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データベース内に格納する際の手順は、以下のとおりです。

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

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

Excelファイルでの設定

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

図6 Excelのデータ範囲に名前を付ける(画像をクリックすると拡大します) 図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の機能を使用することになります。

まとめ

 今回はExcelのデータをOracleデータベースに格納する方法について紹介しました。次回はOracleデータベースのデータをExcelで参照する方法について紹介します。ご期待ください。(次回に続く)


前のページへ 1|2|3       

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。