External Tables, SQL*Loader and Database Links

34.1 External Tables

Access drivers: ORACLE_LOADER, ORACLE_DATAPUMP (unload with CTAS), ORACLE_HDFS, ORACLE_HIVE, ORACLE_BIGDATA (Parquet, ORC, Avro on object storage).

Inline external tables (18c), partitioned and hybrid partitioned tables (19c).

DBMS_CLOUD.CREATE_EXTERNAL_TABLE for object storage.

CREATE DIRECTORY ext_dir AS '/u01/ext';
CREATE TABLE ext_customers (
    id NUMBER, name VARCHAR2(100), city VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_dir
    ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE SKIP 1
                       BADFILE 'cust.bad' LOGFILE 'cust.log'
                       FIELDS CSV WITH EMBEDDED TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
                       MISSING FIELD VALUES ARE NULL)
    LOCATION ('customers.csv')
) REJECT LIMIT UNLIMITED;

INSERT /*+ APPEND */ INTO customers (first_name, city) SELECT name, city FROM ext_customers;

34.2 SQL*Loader

Conventional vs direct path, discard files, control file options, SQL expressions on fields.

-- customers.ctl
OPTIONS (SKIP=1, DIRECT=TRUE, ERRORS=100)
LOAD DATA
INFILE 'customers.csv'
APPEND INTO TABLE customers
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(first_name, last_name, email, city, created_at DATE "YYYY-MM-DD")

sqlldr hr/hr@pdb control=customers.ctl log=customers.log bad=customers.bad
sqlldr hr/hr@pdb TABLE=customers DATA=customers.csv     # express mode

34.3 Database Links

Heterogeneous Services / Database Gateways for non-Oracle sources (SQL Server, ODBC).

Security: avoid public links and use least-privilege remote accounts.

CREATE DATABASE LINK sales_link CONNECT TO reader IDENTIFIED BY "pwd" USING 'SALESDB';
CREATE PUBLIC DATABASE LINK ...;
SELECT * FROM orders@sales_link;
INSERT INTO local_orders SELECT * FROM orders@sales_link WHERE order_date > SYSDATE - 1;
-- DRIVING_SITE hint, distributed transactions (two-phase commit, RECO, DBA_2PC_PENDING)