Autonomous Database で外部表作成（field_listの指定に関する備忘録）

Autonomous Database で外部表作成で、CLOB列があるときのfield_listの指定方法と、ORACLE_LOADERアクセス・ドライバの予約語（YEARなど）についての備忘録です。


外部表とは？外部表を使用したデータロードとは？、や、詳細な手順は、こちらに記載しています。

https://qiita.com/mikika/items/5323651cb5826a5b495e

# やりたいこと

Oracle Cloud Infrastructure（OCI）上の、Autonomous Databaseに、CLOB列にしたいような長い文字列を含む列があるときの、外部表作成のスクリプトを作成する。
最終的にはその外部表から ```insert into 実表 select * from 外部表;``` の形式で実表に対してデータロードを行う。

# ロードで使用したファイル

以下の要領のファイル（複数）を、Object Storageのバケットに配置

```filename.0.dat
ID	YEAR	MONTH	DAY	COL1	COL2
1	2021	10	3	aaaa	aaaa bbb ccc ddd eeeee xxxx yyyy 12345....
2	2021	10	4	bbbb	ccc dd eeeee xxxx yyyy abcdefg.....
```

* タブ区切り、囲み文字無し、1行目がヘッダ
* このようなファイルが複数個（filename.1.dat, filename.2.dat, ... の 5個）
* COL2列は長い文字列で、**CLOB列として定義したい**

# 実行手順

## クレデンシャル作成

ロードで使用するファイルはOCIのバケット上に配置済み。
ADBからバケットにアクセスできるよう、クレデンシャルを作成。

```
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'STAGE_CRED',  -- クレデンシャルの名前(任意の文字列)
    username => 'aaaaaa', -- OCIユーザー名
    password => 'XXXXXXXXXXXXXXXXXX'    -- 生成したAuth Token
  );
END;
/
```

## 外部表作成

```
begin
dbms_cloud.create_external_table(
        table_name => 'TAB1_EXT',
        credential_name => 'STAGE_CRED',
        file_uri_list => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/<namespace>/b/<bucket_name>/o/filename.*.dat',
        format => json_object('type' value 'csv','ignoremissingcolumns' value 'true', 'skipheaders' value '1', 'delimiter' value 'X''9'''),
    column_list => '
        ID     NUMBER,
        YEAR   VARCHAR2(4000),
        MONTH  VARCHAR2(4000),
        DAY    VARCHAR2(4000),
        COL1   VARCHAR2(4000),
        COL2   CLOB',
    field_list => '
    	ID,
        "YEAR",
        "MONTH",
        "DAY",
        COL1 ,
        COL2 char(400000)'
    );
end;
/
```

* CLOB列は field_list で明示的に大きさを指定する（省略すると CHAR(255)になる）
* field_list内のYEAR, MONTH, DAYは、予約語なので ”で囲む（ORACLE_DRIVERの予約語）
* file_uri_list で * で複数ファイルを指定 → format の skipheader=1 指定は全てのファイルに有効だった（全ファイルの先頭行はデータとして扱われずSKIPできた）


## 外部表定義で読めるかVALIDATE

```
BEGIN
DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (
    table_name => 'TAB1_EXT');
END;
/
```

※DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE を実行しエラーになると、ログが確認できる（以下では、"STAGE"."VALIDATE$9_LOG"にログが格納されているのでSELECTで確認できる ）

```
BEGIN
*
ERROR at line 1:
ORA-20003: Reject limit reached, query table "STAGE"."VALIDATE$9_LOG" for error
details
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 1263
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 1549
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 4553
ORA-06512: at line 2
```

## 実表にデータロード

```CREATE TABLE TAB1 from TAB1_EXT(外部表)``` 
または、予めTAB1を作成しておき ```INSERT INTO TAB1 select * from TAB_EXT```

# 参考ドキュメント

ドキュメントより抜粋。今回関係するところを太字で。

## 外部表からのLOBロード

[Oracle Database 19c - ユーティリティ - 18.5 外部表からのLOBのロード]
(https://docs.oracle.com/cd/E96517_01/sutil/using-oracle-external-tables-examples.html#GUID-9D3ED2AE-192D-435A-833E-97899778929E)

## DBMS_CLOUD.CREATE_EXTERNAL_TABLEプロシージャ

[専用Exadataインフラストラクチャ上のOracle Autonomous Databaseの使用 - CREATE_EXTERNAL_TABLEプロシージャ](https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/adbdu/dbms_cloud-create_external_table-procedure.html#GUID-27942374-AEE7-457A-9301-3D9C04657E26)

field_listの書き方は通常のDBのときと同じなのでそちらを参照するようリンクあり↓

## field_listの書き方

[Oracle Database 19c - ユーティリティ - 15.3.4 field_list](https://docs.oracle.com/cd/F19136_01/sutil/oracle_loader-access-driver.html#GUID-5CAF68BD-5C79-4473-AAA3-AB7CDA00B434)

field_list句を使用して、データ・ファイルのフィールドおよびそのデータ型を識別します。field_list句では、次のように評価します。

フィールドにいずれのデータ型も指定されない場合、データ型は、デリミタなしフィールドではCHAR(1)、デリミタ付きフィールドではCHAR(255)である。**

いずれのフィールド・リストも指定されない場合、データ・ファイルのフィールドは外部表と同じ順序である。**データベースの列がCHARまたはVARCHARではない場合、すべてのフィールドのデータ型は、CHAR(255)**となる。 **データベースの列がCHARまたはVARCHARの場合**、フィールドのデータ型はCHARのままであるが、その**長さは255か列の長さでいずれか長い方**の値となる。

いずれのフィールド・リストも指定されず、delim_spec句も指定されない場合、データ・ファイルのフィールドは外部表のフィールドと同じ順序である。すべてのフィールドは、CHAR(255)であり、カンマで終了する。

## ORACLE_LOADERアクセス・ドライバの予約語

[Oracle Database 19c - ユーティリティ - 15.8 ORACLE_LOADERアクセス・ドライバの予約語](https://docs.oracle.com/cd/F19136_01/sutil/oracle_loader-access-driver.html#GUID-4E710755-36CA-4F2D-AFFF-E8A707FF3EBB)

15.8 ORACLE_LOADERアクセス・ドライバの予約語
外部表アクセス・パラメータにおいて列名や表名などの識別子が指定される場合、特定の値はアクセス・パラメータ・パーサーによって予約語とみなされます。

**予約語を識別子として使用するには、二重引用符で囲む** 必要があります。ORACLE_LOADERアクセス・ドライバの予約語は、次のとおりです。

(全文は割愛しますが、YEAR, MONTH, DAY は予約語でした)


## DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE

[共有Exadataインフラストラクチャ上のOracle Autonomous Data Warehouseスタート・ガイド - データ検証のログの表示](https://docs.oracle.com/cd/E83857_01/paas/autonomous-data-warehouse-cloud/cswgs/query-view-logs.html)

外部表を検証するには、DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE、DBMS_CLOUD.VALIDATE_EXTERNAL_PART_TABLEおよびDBMS_CLOUD.VALIDATE_HYBRID_PART_TABLEプロシージャを使用します。

ソース・ファイルを検証した後、ロード操作表を問い合せることで、検証操作の結果を確認できます:

dba_load_operations: すべての検証操作が表示されます。

user_load_operations: スキーマ内の検証操作が表示されます。

これらのファイルを使用してロード検証情報を表示できます。 たとえば、次の選択操作を使用してuser_load_operationsを問い合せます: