APEX_DATA_PARSERを使って単純なCSV、JSON、XML、XLSXデータをSQL SELECT

Oracle Application Express (Oracle APEX) 19.1 から追加されたAPEX_DATA_PARSERパッケージを使って、CSV、JSON、XML、XLSXファイルをそれぞれSQLのSELECT文で読み込んでみた。

# APEX_DATA_PARSERパッケージ
Oracle APEX 19.1 で追加されたAPEX_DATA_PARSER PL/SQLパッケージは、開発者にファイルを解析するための簡単なインターフェースを提供します。
parserファンクションの返り値は表（テーブル）のように使用することができ、読み込んだファイルの中身をSQL や PL/SQL で利用可能です。
このパッケージにはいくつかのルーチンが含まれていますが、この記事では、PARSEファンクションを紹介します。

※CSV、JSON、XML、XLSX形式の基本的な表形式のデータに焦点を当てているため、すべてのドキュメントに使用される汎用パーサーであるとは期待しないでください。

# APEX_DATA_PARSER.PARSE ファンクション
XML、XLSX、CSVまたはJSONファイルを解析して、次の構造(WWV_FLOW_T_PARSER_TABLE型)の表形式で返すファンクション

```
LINE_NUMBER COL001 COL002 COL003 COL004 ... COL300
```

- 返す値はすべてVARCHAR2形式
- 返される表の行は、最大で300の列で各列の最大長は4000バイト
- サポートされている300列のうち20列をCLOBとして処理(APEX19.2以降)することも可能


## PARSEファンクションのパラメータ

|パラメータ名|型|説明|
|:-----------------|:------------------:|:------------------|
|p_content|blob|BLOBとして解析されるファイル・コンテンツ|
|p_file_name|varchar2|使用されるファイルの名前。P_FILE_NAME、P_FILE_TYPEまたはP_FILE_PROFILEのいずれかを渡す必要があります|
|p_file_type|t_file_type|解析するファイルのタイプ|
|p_file_profile|clob|解析に使用されるファイル・プロファイル|
|p_detect_data_types|varchar2|解析中にデータ型(NUMBER、DATE、TIMESTAMP)を検出するかどうか|
|p_decimal_char|varchar2|NUMBERデータ型を検出する場合の小数点文字。指定しない場合、プロシージャで小数点文字が自動検出されます|
|p_xlsx_sheet_name|varchar2|XLSXワークブックの場合の解析するワークシートの名前|
|p_row_selector|varchar2|JSONまたはXMLファイル内の行の配列/リストへのポインタ|
|p_csv_row_delimiter|varchar2|CSV解析のデフォルトの行デリミタをオーバーライドします|
|p_csv_col_delimiter|varchar2|特定のCSV列デリミタを使用します。省略した場合、ファンクションは最初の行の内容に基づいて列デリミタを検出します|
|p_csv_enclosed|varchar2|CSV解析のデフォルトの囲み文字をオーバーライドします|
|p_skip_rows|pls_integer|解析時に最初のN行をスキップします|
|p_add_headers_row|varchar2|XML、JSONの場合。列ヘッダー(タグ、属性名)を最初の行として表示します|
|p_file_charset|varchar2|ファイル・エンコーディング(UTF-8 (AL32UTF8)以外の場合)|
|p_max_rows|number|P_MAX_ROWSが返された後、解析を停止します|
|p_return_rows|number|返される行数|
|p_store_profile_to_collection|varchar2|解析時に計算されたファイル・プロファイルをコレクションに格納します|

P_CONTENT以外はデフォルト値あり。


# 検証環境
今回の検証は以下のバージョンを使用

- Oracle Database 19c (Version 19.7.0.0.0)
- Oracle Application Express 20.1

※ Oracle APEX 19.1 以降がインストールできる Oracle Databaseであれば、バージョンは問いません。
   PL/SQL パッケージのみの利用が可能のため、Oracle APEXインストールスクリプトを実行しただけで利用可能です。（ORDSの構成不要）

# 事前準備

- Oracle Database の作成
- Oracle APEX 19.1 以降のインストール(apexins.sql の実行)
- ディレクトリオブジェクトの作成
- 作成したディレクトリの操作権限の付与

```
CREATE OR REPLACE DIRECTORY TEMP_DIR AS '/tmp/';
GRANT READ, WRITE ON DIRECTORY TEMP_DIR TO scott;
```


- 読み込むファイルの準備（今回の検証はサンプルファイルとして以下を利用）
    - [emp.csv](https://oracle-base.com/articles/misc/images/apex_data_parser/emp.csv)
    - [emp.json](https://oracle-base.com/articles/misc/images/apex_data_parser/emp.json)
    - [emp.xml](https://oracle-base.com/articles/misc/images/apex_data_parser/emp.xml)
    - [emp.xlsx](https://oracle-base.com/articles/misc/images/apex_data_parser/emp.xlsx)

## ファイルのデータをBLOBにロードするファンクションの作成
PARSEファンクションの引数 P_CONTENT にファイルデータを簡単に与えられるようにファンクションを作成しておく。

```
CREATE OR REPLACE FUNCTION file_to_blob (p_dir       IN  VARCHAR2,
                                         p_filename  IN  VARCHAR2)
  RETURN BLOB
AS
  l_bfile  BFILE;
  l_blob   BLOB;

  l_dest_offset INTEGER := 1;
  l_src_offset  INTEGER := 1;
BEGIN
  l_bfile := BFILENAME(p_dir, p_filename);
  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
  DBMS_LOB.createtemporary(l_blob, FALSE);
  IF DBMS_LOB.getlength(l_bfile) > 0 THEN
    DBMS_LOB.loadblobfromfile (
      dest_lob    => l_blob,
      src_bfile   => l_bfile,
      amount      => DBMS_LOB.lobmaxsize,
      dest_offset => l_dest_offset,
      src_offset  => l_src_offset);
  END IF;
  DBMS_LOB.fileclose(l_bfile);
  RETURN l_blob;
END file_to_blob;
/
```

# 検証結果
## CSVファイル

```
SELECT line_number, col001, col002, col003, col004, col005, col006, col007, col008
FROM   TABLE(
         APEX_DATA_PARSER.parse(
           p_content   => file_to_blob('TEMP_DIR', 'emp.csv'),
           p_file_name => 'emp.csv'
         )
       );

LINE_NUMBER COL001     COL002     COL003     COL004     COL005     COL006     COL007     COL008
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
          1 EMPNO      ENAME      JOB        MGR        HIREDATE   SAL        COMM       DEPTNO
          2 7369       SMITH      CLERK      7902       17-DEC-80  800                   20
          3 7499       ALLEN      SALESMAN   7698       20-FEB-81  1600       300        30
          4 7521       WARD       SALESMAN   7698       22-FEB-81  1250       500        30
          5 7566       JONES      MANAGER    7839       02-APR-81  2975                  20
          6 7654       MARTIN     SALESMAN   7698       28-SEP-81  1250       1400       30
          7 7698       BLAKE      MANAGER    7839       01-MAY-81  2850                  30
          8 7782       CLARK      MANAGER    7839       09-JUN-81  2450                  10
          9 7788       SCOTT      ANALYST    7566       19-APR-87  3000                  20
         10 7839       KING       PRESIDENT             17-NOV-81  5000                  10
         11 7844       TURNER     SALESMAN   7698       08-SEP-81  1500       0          30
         12 7876       ADAMS      CLERK      7788       23-MAY-87  1100                  20
         13 7900       JAMES      CLERK      7698       03-DEC-81  950                   30
         14 7902       FORD       ANALYST    7566       03-DEC-81  3000                  20
         15 7934       MILLER     CLERK      7782       23-JAN-82  1300                  10

15 rows selected.
```

この例では、FILE_TO_BLOB関数を使用して "emp.csv"ファイルの内容をBLOBにロードしP_CONTENTパラメータとして、ファイル名をP_FILE_NAMEパラメータとしてPARSE関数に渡しています。
ロードするデータの種類は、ファイルの拡張子から推測しています。（データの種類を示すために P_FILE_TYPE や P_FILE_PROFILE パラメータを使用することも可能）
セレクトリストには、行番号と最初の8つの列を指定

今回のサンプルファイルは、最初の行にカラムヘッダが含まれており、LINE_NUMBER 1として出力されています。
ファンクション呼び出しに “P_SKIP_ROWS => 1”パラメータを追加することで、カラムヘッダ行をスキップして表示が可能です。


## JSONファイル

```
SELECT line_number, col001, col002, col003, col004, col005, col006, col007, col008
FROM   TABLE(
         APEX_DATA_PARSER.parse(
           p_content         => file_to_blob('TEMP_DIR', 'emp.json'),
           p_file_name       => 'emp.json',
           p_row_selector    => 'results.items',
           p_add_headers_row => 'Y'
         )
       );

LINE_NUMBER COL001     COL002     COL003     COL004     COL005     COL006     COL007     COL008
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
            JOB        MGR        SAL        COMM       EMPNO      ENAME      DEPTNO     HIREDATE
          1 CLERK      7902       800                   7369       SMITH      20         17-DEC-80
          2 SALESMAN   7698       1600       300        7499       ALLEN      30         20-FEB-81
          3 SALESMAN   7698       1250       500        7521       WARD       30         22-FEB-81
          4 MANAGER    7839       2975                  7566       JONES      20         02-APR-81
          5 SALESMAN   7698       1250       1400       7654       MARTIN     30         28-SEP-81
          6 MANAGER    7839       2850                  7698       BLAKE      30         01-MAY-81
          7 MANAGER    7839       2450                  7782       CLARK      10         09-JUN-81
          8 ANALYST    7566       3000                  7788       SCOTT      20         19-APR-87
          9 PRESIDENT             5000                  7839       KING       10         17-NOV-81
         10 SALESMAN   7698       1500       0          7844       TURNER     30         08-SEP-81
         11 CLERK      7788       1100                  7876       ADAMS      20         23-MAY-87
         12 CLERK      7698       950                   7900       JAMES      30         03-DEC-81
         13 ANALYST    7566       3000                  7902       FORD       20         03-DEC-81
         14 CLERK      7782       1300                  7934       MILLER     10         23-JAN-82

15 rows selected.
```

この例では、FILE_TO_BLOB関数を使用して "emp.json"ファイルの内容をBLOBにロードしP_CONTENTパラメータとして、ファイル名をP_FILE_NAMEパラメータとしてPARSE関数に渡しています。
また、行データの配列がどこにあるかを示すためにP_ROW_SELECTORパラメータに "results.items"を指定しています。
"P_ADD_HEADERS_ROW => 'Y'"パラメータを使用してカラム名を含めています。（JSONデータの場合、デフォルトではカラム名は出力に含まれていません。）
セレクトリストには、行番号と最初の8つの列を指定

列の順序が予想していたものと違うことに注意


## XMLファイル

```
SELECT line_number, col001, col002, col003, col004, col005, col006, col007, col008
FROM   TABLE(
         APEX_DATA_PARSER.parse(
           p_content         => file_to_blob('TEMP_DIR', 'emp.xml'),
           p_file_name       => 'emp.xml',
           p_row_selector    => '/RESULTS/ROW',
           p_add_headers_row => 'Y'
         )
       );

LINE_NUMBER COL001     COL002     COL003     COL004     COL005     COL006     COL007     COL008
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
            EMPNO      ENAME      JOB        MGR        HIREDATE   SAL        COMM       DEPTNO
          1 7369       SMITH      CLERK      7902       17-DEC-80  800                   20
          2 7499       ALLEN      SALESMAN   7698       20-FEB-81  1600       300        30
          3 7521       WARD       SALESMAN   7698       22-FEB-81  1250       500        30
          4 7566       JONES      MANAGER    7839       02-APR-81  2975                  20
          5 7654       MARTIN     SALESMAN   7698       28-SEP-81  1250       1400       30
          6 7698       BLAKE      MANAGER    7839       01-MAY-81  2850                  30
          7 7782       CLARK      MANAGER    7839       09-JUN-81  2450                  10
          8 7788       SCOTT      ANALYST    7566       19-APR-87  3000                  20
          9 7839       KING       PRESIDENT             17-NOV-81  5000                  10
         10 7844       TURNER     SALESMAN   7698       08-SEP-81  1500       0          30
         11 7876       ADAMS      CLERK      7788       23-MAY-87  1100                  20
         12 7900       JAMES      CLERK      7698       03-DEC-81  950                   30
         13 7902       FORD       ANALYST    7566       03-DEC-81  3000                  20
         14 7934       MILLER     CLERK      7782       23-JAN-82  1300                  10

15 rows selected.
```

この例では、FILE_TO_BLOB関数を使用して "emp.xml"ファイルの内容をBLOBにロードしP_CONTENTパラメータとして、ファイル名をP_FILE_NAMEパラメータとしてPARSE関数に渡しています。
また、行データの配列がどこにあるかを示すためにP_ROW_SELECTORパラメータに "/RESULTS/ROW"を指定しています。
"P_ADD_HEADERS_ROW => 'Y'"パラメータを使用してカラム名を含めています。（XMLデータの場合、デフォルトではカラム名は出力に含まれていません。）
セレクトリストには、行番号と最初の8つの列を指定


## XLSXファイル
### 単一シートの例

```
SELECT line_number, col001, col002, col003, col004, col005, col006, col007, col008
FROM   TABLE(
         APEX_DATA_PARSER.parse(
           p_content         => file_to_blob('TEMP_DIR', 'emp.xlsx'),
           p_file_name       => 'emp.xlsx'
         )
       );

LINE_NUMBER COL001     COL002     COL003     COL004     COL005     COL006     COL007     COL007     COL008
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
          1 EMPNO      ENAME      JOB        MGR        HIREDATE   SAL        COMM       COMM       DEPTNO
          2 7369       SMITH      CLERK      7902       1980-12-17 800                              20
          3 7499       ALLEN      SALESMAN   7698       1981-02-20 1600       300        300        30
          4 7521       WARD       SALESMAN   7698       1981-02-22 1250       500        500        30
          5 7566       JONES      MANAGER    7839       1981-04-02 2975                             20
          6 7654       MARTIN     SALESMAN   7698       1981-09-28 1250       1400       1400       30
          7 7698       BLAKE      MANAGER    7839       1981-05-01 2850                             30
          8 7782       CLARK      MANAGER    7839       1981-06-09 2450                             10
          9 7788       SCOTT      ANALYST    7566       1987-04-19 3000                             20
         10 7839       KING       PRESIDENT             1981-11-17 5000                             10
         11 7844       TURNER     SALESMAN   7698       1981-09-08 1500       0          0          30
         12 7876       ADAMS      CLERK      7788       1987-05-23 1100                             20
         13 7900       JAMES      CLERK      7698       1981-12-03 950                              30
         14 7902       FORD       ANALYST    7566       1981-12-03 3000                             20
         15 7934       MILLER     CLERK      7782       1982-01-23 1300                             10

15 rows selected.
```

この例では、FILE_TO_BLOB関数を使用して "emp.xlsx"ファイルの内容をBLOBにロードしP_CONTENTパラメータとして、ファイル名をP_FILE_NAMEパラメータとしてPARSE関数に渡しています。
最初のワークシートを出力するのであれば、追加のパラメータを指定せずに利用可能です。
セレクトリストには、行番号と最初の8つの列を指定



### 複数シートの例
複数のワークシートがある場合は、P_XLSX_SHEET_NAMEパラメータを使用してシートを指定します。
GET_XLSX_WORKSHEETS関数を使用して、ドキュメント内のワークシートを一覧表示が可能です。

APEX_DATA_PARSER.get_xlsx_worksheetsファンクションを使用してのXLSXワークブック内のワークシートに関する情報取得

```
COLUMN sheet_display_name FORMAT A30
COLUMN sheet_file_name FORMAT A30

SELECT sheet_display_name, sheet_file_name
FROM   TABLE(
         APEX_DATA_PARSER.get_xlsx_worksheets(
           p_content => file_to_blob('TEMP_DIR', 'emp.xlsx')
         )
       );

SHEET_DISPLAY_NAME             SHEET_FILE_NAME
------------------------------ ------------------------------
emp sheet                      sheet1.xml
```

APEX_DATA_PARSER.PARSEファンクションを使った検索

```
SELECT line_number, col001, col002, col003, col004, col005, col006, col007, col008
FROM   TABLE(
         APEX_DATA_PARSER.parse(
           p_content         => file_to_blob('TEMP_DIR', 'emp.xlsx'),
           p_file_name       => 'emp.xlsx',
           p_xlsx_sheet_name => 'sheet1.xml'
         )
       );

LINE_NUMBER COL001     COL002     COL003     COL004     COL005     COL006     COL007     COL007     COL008
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
          1 EMPNO      ENAME      JOB        MGR        HIREDATE   SAL        COMM       COMM       DEPTNO
          2 7369       SMITH      CLERK      7902       1980-12-17 800                              20
          3 7499       ALLEN      SALESMAN   7698       1981-02-20 1600       300        300        30
          4 7521       WARD       SALESMAN   7698       1981-02-22 1250       500        500        30
          5 7566       JONES      MANAGER    7839       1981-04-02 2975                             20
          6 7654       MARTIN     SALESMAN   7698       1981-09-28 1250       1400       1400       30
          7 7698       BLAKE      MANAGER    7839       1981-05-01 2850                             30
          8 7782       CLARK      MANAGER    7839       1981-06-09 2450                             10
          9 7788       SCOTT      ANALYST    7566       1987-04-19 3000                             20
         10 7839       KING       PRESIDENT             1981-11-17 5000                             10
         11 7844       TURNER     SALESMAN   7698       1981-09-08 1500       0          0          30
         12 7876       ADAMS      CLERK      7788       1987-05-23 1100                             20
         13 7900       JAMES      CLERK      7698       1981-12-03 950                              30
         14 7902       FORD       ANALYST    7566       1981-12-03 3000                             20
         15 7934       MILLER     CLERK      7782       1982-01-23 1300                             10

15 rows selected.
```

この例では、FILE_TO_BLOB関数を使用して "emp.xlsx"ファイルの内容をBLOBにロードしP_CONTENTパラメータとして、ファイル名をP_FILE_NAMEパラメータとしてPARSE関数に渡しています。
ワークシートを指定する ”P_XLSX_SHEET_NAME”パラメータを指定します。
セレクトリストには、行番号と最初の8つの列を指定

# まとめ
APEX_DATA_PARSER パッケージを使ってファイルを行と列形式としてSQL文から操作できることがわかりました。
外部表のようなDDL文が不要なので、簡易に利用ができる形態だと感じました。


### 補足:ファイルサイズが大きい場合の留意点
APEX_DATA_PARSER.PARSE に渡す BLOB が 50 MB 未満の場合は、Oracle Application Express は BLOB を内部のキャッシュされた一時的な LOB にコピーし、すべてのCSV解析はメモリ上で実行しますが、
50MBより大きなBLOBの場合、Oracle Application Expressが元のBLOBロケータとなるディスク上でCSV解析を実行するため、かなり遅くなる可能性があります。

50 MB を超えるファイルでインメモリ解析が必要な場合
DBMS_LOB.CREATETEMPORARY (CACHE => TRUE を渡します) や DBMS_LÖB.COPY プロシージャを使用して、大きなファイルであってもキャッシュされた一時的な LOB を明示的に作成することで元のBLOBの代わりに、キャッシュされた一時的なLOBをAPEX_DATA_PARSER.PARSEに渡すことが可能です。



### 参考情報
- [APEX_DATA_PARSER : Convert simple CSV, JSON, XML and XLSX data to rows and columns](https://oracle-base.com/articles/misc/apex_data_parser)
- [Load data into existing tables with APEX 19.1](https://blogs.oracle.com/apex/load-data-into-existing-tables-with-apex-191)


#### SQL*Plusで列形式の指定

```
COLUMN COL001 FORMAT A10
COLUMN COL002 FORMAT A10
COLUMN COL003 FORMAT A10
COLUMN COL004 FORMAT A10
COLUMN COL005 FORMAT A10
COLUMN COL006 FORMAT A10
COLUMN COL007 FORMAT A10
COLUMN COL008 FORMAT A10
SET LINESIZE 110
```