【oracle】oracle18c特性内联外部表与19c特性dbms_cloud结合使用

在Oracle18c中添加了一个特性，叫内联外部表，意思就是可以不创建外部表对象，而是在sql中直接引用外部文件来进行查询，
https://docs.oracle.com/en/database/oracle/oracle-database/18/admin/managing-tables.html#GUID-621E5DDE-36D9-4661-9D14-80DE35858C3F
官网的例子

SELECT * FROM   EXTERNAL (   
    (time_id        DATE NOT NULL,     
     prod_id        INTEGER NOT NULL,
     quantity_sold  NUMBER(10,2),
     amount_sold    NUMBER(10,2))     
    TYPE ORACLE_LOADER     
    DEFAULT DIRECTORY data_dir1
    ACCESS PARAMETERS (
      RECORDS DELIMITED BY NEWLINE
      FIELDS TERMINATED BY '|')     
   LOCATION ('sales_9.csv') REJECT LIMIT UNLIMITED) sales_external;

其实基本上就是把外部表所需要的信息嵌入进去了。

正好前一阵子在折腾使用dbms_cloud来创建基于对象存储的外部表，突然联想到是不是可以把远程oss里的文件直接用这种方式查询出来？想到就干。

之前外部表的ddl是这样的

create table EMP_EXT_JD
(
  empno NUMBER(4),
  ename VARCHAR2(10),
  job   VARCHAR2(9)
)
organization external
(
  type ORACLE_LOADER
  default directory DATA_PUMP_DIR
  access parameters 
  (
    RECORDS DELIMITED BY DETECTED NEWLINE NOLOGFILE NOBADFILE NODISCARDFILE READSIZE=10000000 CREDENTIAL 'OBJ_STORE_CRED_JD' 
    FIELDS CSV WITHOUT EMBEDDED NOTRIM
  )
  location (DATA_PUMP_DIR:'https://darkathenafirst.s3.cn-east-2.jdcloud-oss.com/test_upload.csv')
)
reject limit 0;

如果改成直接查询文件，就变成了下面这样

SELECT * FROM   EXTERNAL (   
    (
  empno NUMBER(4),
  ename VARCHAR2(10),
  job   VARCHAR2(9)
)     
    TYPE ORACLE_LOADER     
    DEFAULT DIRECTORY DATA_PUMP_DIR
    ACCESS PARAMETERS (
      RECORDS DELIMITED BY DETECTED NEWLINE NOLOGFILE NOBADFILE NODISCARDFILE READSIZE=10000000 CREDENTIAL 'OBJ_STORE_CRED_JD' 
    FIELDS CSV WITHOUT EMBEDDED NOTRIM)     
   location (DATA_PUMP_DIR:'https://darkathenafirst.s3.cn-east-2.jdcloud-oss.com/test_upload.csv') 
reject limit 0) EMP_EXT_JD;

还真的可行！其实就只是把外部表ddl中间的“organization external”删掉，然后用 "external ()"把剩下的ddl字符都包起来。

有时候无可避免要读很多个不同的文件，比如云计算那边每天生成一个带日期文件名的文件，然后oracle这边每天都要读今天或者昨天的文件，如果是基于创建外部表对象，那么数据库里会一堆外部表对象，而使用内联外部表即可不用创建对象，只需要进行字符串拼接使用动态sql即可灵活查询不同文件。

fin.