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

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

やりたいこと

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プロシージャ

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

field_listの書き方

Oracle Database 19c - ユーティリティ - 15.3.4 field_list

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アクセス・ドライバの予約語

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

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

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

DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE

共有Exadataインフラストラクチャ上のOracle Autonomous Data Warehouseスタート・ガイド - データ検証のログの表示

外部表を検証するには、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を問い合せます: