Autonomous Database で外部表作成で、CLOB列があるときのfield_listの指定方法と、ORACLE_LOADERアクセス・ドライバの予約語（YEARなど）についての備忘録です。
外部表とは？外部表を使用したデータロードとは？、や、詳細な手順は、こちらに記載しています。
やりたいこと
Oracle Cloud Infrastructure（OCI）上の、Autonomous Databaseに、CLOB列にしたいような長い文字列を含む列があるときの、外部表作成のスクリプトを作成する。
最終的にはその外部表から insert into 実表 select * from 外部表; の形式で実表に対してデータロードを行う。
ロードで使用したファイル
以下の要領のファイル（複数）を、Object Storageのバケットに配置
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のロード
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を問い合せます: