Rainbow Planet (GT×IT×SP×SA)

本ブログではIT技術を分かりやすく簡潔にまとめ伝える事で効率的な学習の支援を目指します。

01_IT技術 (Technology) 04_PL/SQL

【PL/SQL】”UTL_FILE”パッケージを利用して”.TXT”を読み込む方法

投稿日:2017年9月17日 更新日:

今回はPL/SQLのUTL_FILEパッケージを利用して、サーバ内にあるテキストファイルを読み込むプロシージャを作成したいと思います。

(0) 目次

(1) 事前準備:ディレクトリオブジェクトの作成(CREATE DIRECTORY)
(2) 事前準備:ユーザにディレクトリオブジェクトのREAD/WRITE権限を付与する
(3) 事前準備:ユーザに”UTL_FILE”パッケージのEXECUTE権限を付与する
(4) コーディング
(5) コンパイル・実行
(6) 結果確認
(7) 語句の解説

(1) 事前準備:ディレクトリオブジェクトの作成(CREATE DIRECTORY)

TXTファイルを配備するディレクトリ情報を持つ”ディレクトリオブジェクト”を作成します。ディレクトリオブジェクトはPCの物理的なディレクトリを指定する事が無いよう、Oracleで別名を付けて管理するオブジェクトです。パッケージ等と同様に各種権限をGRANTして利用します。

/* CREATE DIRECTORY [オブジェクト名] AS 'ディレクトリPATH' */
CREATE DIRECTORY RBWEG_0001 AS '/tmp/plsqltest-20170916';


結果確認

目次にもどる

(2) 事前準備:ユーザにディレクトリオブジェクトのREAD/WRITE権限を付与する

上記(1)で付与した権限をスキーマユーザに付与します。コマンドは以下を利用します。

/* GRANT [付与したい権限名] ON DIRECTORY [オブジェクト名] TO [ユーザ名] */
GRANT READ ON DIRECTORY RBWEG_0001 TO RBWEGUSR ;
GRANT WRITE ON DIRECTORY RBWEG_0001 TO RBWEGUSR ;


目次にもどる

(3) 事前準備:ユーザに”UTL_FILE”パッケージのEXECUTE権限を付与する

次にファイル読み込みの肝となる”UTL_FILE”パッケージの実行(EXECUTE)権限を付与します。

/* GRANT [付与したい権限名] ON [オブジェクト名] TO [ユーザ名] */
GRANT EXECUTE ON UTL_FILE TO RBWEGUSR;


目次にもどる

(4) コーディング

以下はコードのサンプルです。
利用する場合はディレクトリ”/tmp/plsqltest-20170916“に”test.txt”を事前に配備しておく必要があります。

CREATE OR REPLACE
PROCEDURE TEST_FILEREAD
IS
  read utl_file.file_type;
  buf varchar2(1022);
  vDirname VARCHAR2(250);
BEGIN
  --/* Open file in 'Read Mode' */
  vDirname := 'RBWEG_0001';
  read := utl_file.fopen(vDirname,'test.txt','r');
  loop
    begin
      --/* read the file */
      utl_file.get_line(read,buf);
      dbms_output.put_line('data: '||buf);
    exception
      --/* end the loop when we come to the end of file */
      when no_data_found then
      exit;
    end;
  end loop;    
  --/* file close */
  utl_file.fclose(read);    
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('ER-0000 : Other Exception');
END TEST_FILEREAD;

目次にもどる

(5) コンパイル・実行

テストに用いた”test.txt”の内容は以下。

コンパイル・実行
「1」のボタンが実行、「2」がコンパイルです。

目次にもどる

(6) 結果確認

サンプルコードの実行結果は以下のようになります。
“test.txt”の内容が表示されています。

Connecting to the database RBWEGUSR.
data: 123
data: 456
data: 789
data: ABC
data: DEF
data: GHI
data: JKL
data: MNO
data: PQR
data: STU
data: VWX
data: YZ0
Process exited.
Disconnecting from the database RBWEGUSR.

目次にもどる

(7) 語句の解説

●PL/SQLとは?
PL/SQLとはOracle独自のプログラミング言語で、通常のSQLではできない「手続き処理」を行う事ができます(例:ある処理の結果を後続の処理の入力条件にするなど)。そして他の言語と異なり、プログラム中でSQLを発行するのにAPIによるコールが不要のため、その分のオーバーヘッドも少なくなります。Oracleが開発した言語のため、Oracleで使用可能なすべてのデータ型をサポートしており、PL/SQLの中で使用可能である点や、Oracleが動作する環境であればOSが異なっても移植が可能な点、更には他言語からSQLを実行した場合と比較してプロセス間の通信のオーバーヘッドが少ないという利点もあります。

 

●PL/SQLのパフォーマンス
PL/SQL以外の言語からSQL文を発行すると(C言語,Java,VBなど)、DB接続ミドルウェアを経由してSQL文が文単位で送信されるため、クライアントとサーバーのプロセス間のオーバーヘッドが発生します。一方でPL/SQLの場合はブロック内の記述全体(プログラム単位)をまとめてOracleに送信するため、通信回数を減少させることでオーバーヘッドの減少を実現しています。

●DB接続ミドルウェア
SQLを利用するアプリとDBの仲介を行うミドルウェアを「DB接続ミドルウェア」と呼びます。使用するミドルウェアは言語によって異なり、例えばCOBOLの場合は「PRO*COBOL」を利用し、.NET系のVB.NETでは「ODP.NET」などを利用します。Oracleへの接続という観点でもミドルウェアの選択肢は複数あり、例えば「OLE DB.NET」や「ODBC.NET」など以下のミドルウェアがあります。以下は主な特徴をまとめた比較表です。

データアクセス・ブリッジIF名 Oracle側コンポ 対応する主な言語 対応OS 概要 通信のオーバーヘッド
– (※ネイティブ) ODP.NET .NET言語(VB.NET/VC#) Windowsのみ .NETフレームワーク上で動作するミドルウェア。Oracleに特化している。 Oracleデータベースへのネイティブ接続が可能(AP→ODP.NET→OracleDB)
OLE DB.NET Oracle OLE DB VB/VC++など Windowsのみ Oracle以外のRDBMSへも接続可能。Excel等のOffice系で使用される。 ODPと比較すると一階層余計に通過(データアクセス・ブリッジを経由)するため、オーバーヘッドが発生する。
ODBC.NET Oracle ODBC VB/C言語/C++など Windows/Linux SQL ServerやDB2など他社のDBとOracleの接続にて利用される。 ODPと比較すると一階層余計に通過(データアクセス・ブリッジを経由)するため、オーバーヘッドが発生する。

OLEはCommandオブジェクトを使ってSQLオブジェクトを直接発行しているため、処理の終了までOracleとの接続を保持する必要があります。それに対してODPは非接続型のデータアクセス方式を採用しており、DataAdapter経由でメモリ内のDataSetを利用するため、切断後もデータへのアクセスが可能です。

目次にもどる

Adsense審査用広告コード


Adsense審査用広告コード


-01_IT技術 (Technology), 04_PL/SQL

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

Linuxの”No space left on device”エラーの対処方法

(0)目次&概説 >(1) 障害・不具合の概要 >(2) 障害・不具合の原因 >(3) 障害・不具合の対処 (1) 障害・不具合の概要 Linuxでファイルを作成した時などに”No spa …

Linuxで一般ユーザ(rootスイッチ権限有り)を作成する方法

Linuxでユーザを作成する方法を書きます。 (0)目次 >(1)作業の背景・目的・前提条件 >(2)ユーザの作成 >(3)ユーザの「Wheel」グループへの追加 (1)作業の背景・目的・前提条件 「 …

Linux基礎コマンド(第2回) ls・mkdir・cp・mv

初めてLinuxを触る人向けにTeraTermのログイン方法からコマンドを入力する方法まで数回にわたり解説していきます。本記事はその第2弾です!第1弾の記事はこちら(第1弾)です。 (0)目次&概説 …

OracleDB11g 新規スキーマの作成_★IT0018

(0) 目次 (1) 表領域の作成 (2) スキーマの作成 (3) スキーマの接続権限を付与 (4) 用語解説 (1) 表領域の作成 表領域とデータベースファイル(dbf)を以下のコマンドで作成します …

scpコマンドを使った異なるLinuxサーバ間のファイル転送の方法

異なるLinuxサーバ間(サーバA⇔サーバB)でファイルを転送する方法について書きます。 (0)目次&概説 >(1) 転送コマンドの構文 >(2) 転送コマンドの実行  >(2-1) 転送元の転送前( …

Top