Oracle External Tables
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Note If external tables are created with NOLOG then granting READ on the DIRECTORY object is sufficient. If an external table is created without the NOLOG syntax then both READ and WRITE must be granted to SELECT from it.

External tables are READ ONLY. Insert, update, and delete can not be performed.
Dependencies
ALL_EXTERNAL_TABLES DBA_EXTERNAL_TABLES USER_EXTERNAL_TABLES
CDB_EXTERNAL_TABLES    
Object Privileges
SELECT SELECT ANY TABLE  
System Privileges
ALTER TABLE CREATE TABLE DROP ANY TABLE
CREATE ANY TABLE    
 
Actions As SYS
Create Directory and grant privileges CREATE OR REPLACE DIRECTORY <name> AS <operating_system_path_and_directory>;
conn sys@pdbdev as sysdba

CREATE OR REPLACE DIRECTORY ext AS 'c:\external';

GRANT read, write ON DIRECTORY ext TO uwclass;
 
External Table File
Create Text File Using a Text Editor This file should be placed into the operating system file system directory 'c:\external' refernced by the CREATE DIRECTORY command above
7369,KYTE,SME,20
7499,MILLSAP,SALESMAN,30
7521,NORGAARD,SALESMAN,30
7566,KOLK,MANAGER,20
7654,LEWIS,ANALYST,30

Save external file as c:\external\demo1.dat (if UNIX or LINUX use your home directory)

1111,MORGAN,DIRECTOR,10
2222,HARDIE,MANAGER,30
3333,HAVEMEYER,CTO,10
4444,LOFSTROM,DEVELOPER,10
5555,TOWNSEND,MANAGER,30

Save external file as c:\external\demo2.dat (if UNIX or LINUX use your home directory)

-- create a file on the hard disk named cost.txt with the follow 4 lines:

YEAR PID CPU  GROSS REVENUE
2003 def 2.00 123.4567890
2004 ABC 1.00 39.7288841651344
2005 xyz 1.99 1107.5458517352
Save external file as c:\external\cost.txt (if UNIX or LINUX use your home directory)

A comma delimited flat file is export from a UNIX database and transferred for loading into a Windows based Oracle database. The lines are terminated with 0x0A and not the 0x0A 0x0D pair for DOS/Windows. The fields are randomly double quoted, and some contain commas internal to the field's data like the following:


100001,"7123 HIGHLAND DR","03/28/1965"," Mercer Island, WA 98040","Morgan, Dan A", "","","63034630752", 14,1,"F","T","06/28/2009","",0,"","01/01/2010","N", "01/01/1999",""
100020,"5432 SOUTH 28TH ST","01/01/1951"," Mercer Island, WA 98040", "Burger,Tom", "","2566400","ZPW345070938",64,1,"M","B","02/23/2000","",0,"", "12/31/1799","P","12/31/1979",""


Save external file as c:\external\preprocess.dat.gz (if UNIX or LINUX use your home directory)
 
Create Table
Create External Table AS CREATE TABLE <[schema_name.]table_name>
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY <oracle_directory_object_name>
LOCATION ('<file_name>'))
[PARALLEL] AS
CREATE TABLE uwclass.servers_ext
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY ctemp
LOCATION ('servers.dat'))
PARALLEL 2
AS
SELECT * FROM uwclass.servers;
Create Internal Representation of the External Table CREATE TABLE <table_name> (
<column_definitions>)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY <oracle_directory_object_name>
ACCESS PARAMETERS (
RECORDS DELIMITED BY newline
BADFILE <file_name>
DISCARDFILE <file_name>
LOGFILE <file_name>
[READSIZE <bytes>]
[SKIP <number_of_rows>
FIELDS TERMINATED BY '<terminator>'
OPTIONALLY ENCLOSED BY '<character>'
REJECT ROWS WITH ALL NULL FIELDS
MISSING FIELD VALUES ARE NULL
(<column_name_list>))\
LOCATION ('<file_name>'))
[PARALLEL]
REJECT LIMIT <UNLIMITED | integer>;
conn uwclass/uwclass@pdbdev

CREATE TABLE ext_tab1 (
empno  CHAR(4),
ename  CHAR(20),
job    CHAR(20),
deptno CHAR(3))
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY ext
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    BADFILE ext:'bad_%a_%p.bad'
    LOGFILE ext:'log_%a_%p.log'
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (empno, ename, job, deptno))
    LOCATION ('demo1.dat'))
PARALLEL
REJECT LIMIT 0
NOMONITORING;

SELECT * FROM ext_tab;

SELECT table_name
FROM user_tables;

desc user_external_tables

col table_name format a15
col type_name format a15
col reject_limit format a15

SELECT table_name, type_name, default_directory_name,
reject_limit, access_type
FROM user_external_tables;

DROP TABLE ext_tab PURGE;

CREATE TABLE ext_tab2 (
empno CHAR(4),
ename CHAR(20),
job CHAR(20),
deptno CHAR(3))
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY ext
  ACCESS PARAMETERS (
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (empno, ename, job, deptno))
  LOCATION ('demo1.dat','demo2.dat'))
PARALLEL
REJECT LIMIT 0;

SELECT * FROM ext_tab;


Non-character column examples
Date Columns CHAR(17) DATE FORMAT DATE MASK "YYYYMMDD HH24:MI:SS"
Number Columns UNSIGNED INTEGER EXTERNAL(20)
External Table For Writing and Reading CREATE TABLE <table_name> (
<column_name, column_name, ...>)
ORGANIZATION EXTERNAL
(TYPE oracle_datapump
DEFAULT DIRECTORY <oracle_directory_object_name>
LOCATION ('<file_name'))
[PARALLEL]
AS
<SQL Statement>;
conn uwclass/uwclass@pdbdev

CREATE TABLE ext_write (
tab_name, tblspname, numblocks)
ORGANIZATION EXTERNAL
(TYPE oracle_datapump
DEFAULT DIRECTORY ext
LOCATION ('table_history.exp'))
PARALLEL
AS
SELECT table_name, tablespace_name, blocks
FROM user_tables;

SELECT *
FROM ext_write;

SELECT *
FROM ext_write
WHERE numblocks > 100;

SELECT table_name, type_name, default_directory_name,
reject_limit, access_type
FROM user_external_tables;

-- open ext_write_####_####.log files
-- open c:\external\table_history.exp


DROP TABLE ext_write;
Tab Delimited External Table conn uwclass/uwclass@pdbdev

CREATE TABLE ext_tab3 (
empno CHAR(4),
ename CHAR(20),
job CHAR(20),
deptno CHAR(2))
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY ext
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY 0X'09'

MISSING FIELD VALUES ARE NULL
(empno, ename, job, deptno))
LOCATION ('demo1.dat'))
PARALLEL
REJECT LIMIT 0;
External Table For Viewing Alert Logs

Thank you to Frank Beutelschiess, in Germany, for an improvement on my original demo.

Thank you Caleb Small, in Canada, for the view
conn sys@pdbdev as sysdba

CREATE OR REPLACE DIRECTORY bdump AS 'c:\app\oracle\diag\rdbms\orabase\orabase\trace\';

CREATE TABLE log_table (TEXT VARCHAR2(400))
ORGANIZATION EXTERNAL (
 TYPE oracle_loader
 DEFAULT DIRECTORY bdump
 ACCESS PARAMETERS (
   RECORDS DELIMITED BY NEWLINE
   NOBADFILE NODISCARDFILE NOLOGFILE
   FIELDS TERMINATED BY 0x'0A'
   MISSING FIELD VALUES ARE NULL)
 LOCATION ('alert_orabase.log'))
REJECT LIMIT unlimited;

SELECT * FROM system.log_table;

CREATE OR REPLACE VIEW last_200_alerts AS
WITH alert_log AS (
  SELECT rownum as lineno, text FROM system.log_table)
SELECT text
FROM alert_log
WHERE lineno > (SELECT count(*)-200 FROM alert_log)
ORDER BY lineno;

SELECT * FROM last_200_alerts;
Skip First Record CREATE TABLE skip_tab (
fiscal_year        NUMBER(4),
prod_no            VARCHAR2(30),
cost_per_unit      FLOAT(126),
gross_rev_per_unit FLOAT(126))
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY ext
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    SKIP 1
    FIELDS TERMINATED BY ' '
    MISSING FIELD VALUES ARE NULL
    (fiscal_year INTEGER EXTERNAL (4), prod_no CHAR(30),
    cost_per_unit FLOAT EXTERNAL, gross_rev_per_unit FLOAT EXTERNAL))
LOCATION ('cost.txt'));

SELECT * FROM skip_tab;
Skipping header and trailing records

NODISCARDFILE keeps what has been discarded from being logged
CREATE TABLE emp_load (
first_name CHAR(15),
last_name  CHAR(20),
birth_year CHAR(4))
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY ext
  ACCESS PARAMETERS (
    RECORDS FIXED 20 FIELDS (
    first_name CHAR(7),
    last_name  CHAR(8),
    birth_year CHAR(4))
  LOAD WHEN (1:7) != 'HEADER ' AND (1:8) != 'TRAILER ' NODISCARDFILE)
LOCATION ('info.dat'));
Records delimited by RECORDS DELIMITED BY 0x'0A' demo submitted by Tom Burger of Exan Software CREATE TABLE some_data (
ACCOUNT_NUM    VARCHAR2(10),
ADDRESS        VARCHAR2(30),
BIRTH_DATE     VARCHAR2(10),
CITY_STATE_ZIP VARCHAR2(40),
NAME           VARCHAR2(30),
EMPLOYER_NAME  VARCHAR2(30),
GROUP_NAME     VARCHAR2(30),
ID_NO          VARCHAR2(50),
PLAN_NO        VARCHAR2(50),
VAR_REC        VARCHAR2(50),
SEX            VARCHAR2(2),
TYPE           VARCHAR2(10),
UPDATE_DATE    VARCHAR2(10),
EXD_ID         VARCHAR2(30),
PCP            VARCHAR2(30),
PCP_EFFECT     VARCHAR2(30),
CANCEL_DATE    VARCHAR2(10),
COV_DEP        VARCHAR2(30),
EFFECT_DATE    VARCHAR2(10),
ADDL_INFO      VARCHAR2(50))
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY ext
ACCESS PARAMETERS
(RECORDS DELIMITED BY 0x'0A'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL)
LOCATION ('UNIX_DATA.DAT'))
REJECT LIMIT unlimited;

desc some_data

set linesize 121
col account_num format a10
col address format a20
col birth_date format a20
col city_state_zip format a20
col name format a20

SELECT account_num, address, birth_date, city_state_zip, name
FROM some_data;


/* This eliminates the loader error of seeing the file as one single record that exceeds the half MB limit and fields like "Doe,Jane R" are interpreted as a single field. This is from a real life situation I was working on today. All of the data in the example is, of course, altered to privacy. */
Preprocessing Example 1 CREATE TABLE preprocess1 (
EMPNO  NUMBER(4),
ENAME  VARCHAR2(20),
JOB    VARCHAR2(20),
DEPTNO NUMBER)
ORGANIZATION external (
  TYPE oracle_loader
  DEFAULT DIRECTORY ext
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    CHARACTERSET US7ASCII
    PREPROCESSOR exec_file_dir:'gunzip' OPTIONS '-c'
  BADFILE ext:'preprocess.bad'
  LOGFILE ext:'preprocess.log'
  FIELDS TERMINATED BY "|" LDRTRIM
  (EMPNO, ENAME, JOB, DEPTNO))
LOCATION ('preprocess.dat.gz'))
REJECT LIMIT UNLIMITED;

desc preprocess1
Preprocessing Example 2 -- in the *NIX shell place the following shell script named test.sh into the directory identified by the directory object "ext"
#!/bin/bash
/bin/df -h

conn sys@pdbdev as sysdba

GRANT execute ON DIRECTORY ext TO uwclass;

conn uwclass/uwclass@pdbdev

CREATE TABLE preprocess2 (
SCRIPT_OUTPUT VARCHAR2(2000))
ORGANIZATION external (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    PREPROCESSOR ext:'test.sh'
    SKIP 1
  LOGFILE ext:'demo2.log'
  BADFILE ext:'demo2.bad'
  NODISCARDFILE
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  MISSING FIELD VALUES ARE NULL)
LOCATION (ext:'demo2.dat'))
REJECT LIMIT 0
NOPARALLEL
NOMONITORING;

desc preprocess2

SELECT * FROM preprocess2;
 
Create External Table DDL With SQL*Loader
Step 1 copy sqlldr02.ctl and sqlldr02.dat to c:\temp
-- the files they can be found linked on the SQL*Loader page
Step 2 -- open a terminal window and enter the following:
c:\Documents and Settings>sqlldr scott/tiger control=c:\temp\sqlldr02 log=c:\temp\ddl_file.txt external_table=generate_only
Step 3 -- open the newly created file ddl_file.txt and scroll down to the create table statement
 
Alter Table
Access Parameters ALTER TABLE <table_name> ACCESS PARAMETERS (FIELDS TERMINATED BY '<delimiter>');
SELECT table_name, access_parameters
FROM user_external_tables;

ALTER TABLE ext_tab2  ACCESS PARAMETERS (FIELDS TERMINATED BY '|');

SELECT table_name, access_parameters
FROM user_external_tables;

ALTER TABLE ext_tab2  ACCESS PARAMETERS (FIELDS TERMINATED BY ',');

SELECT table_name, access_parameters
FROM user_external_tables;

ALTER TABLE ext_tab2 ACCESS PARAMETERS (FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL (empno, ename, job, deptno));

SELECT table_name, access_parameters
FROM user_external_tables;
Add Column ALTER TABLE <table_name> ADD (<column_name data_type>);
desc ext_tab2

ALTER TABLE ext_tab2 DROP COLUMN deptno;

desc ext_tab2

ALTER TABLE ext_tab2 ADD (deptno VARCHAR2(3));
Default Directory ALTER TABLE <table_name> DEFAULT DIRECTORY <directory_name>;
SELECT table_name, default_directory_owner, default_directory_name
FROM user_external_tables;

ALTER TABLE ext_tab2 DEFAULT DIRECTORY ext;

SELECT table_name, default_directory_owner, default_directory_name
FROM user_external_tables;
Drop Column ALTER TABLE <table_name> DROP COLUMN (<column_name>);
See Add Column Demo Above
Modify Column ALTER TABLE <table_name> MODIFY (<column_name> <column_change>);
desc ext_tab2

ALTER TABLE ext_tab2 MODIFY (deptno VARCHAR2(10));

desc ext_tab2
Parallel Access ALTER TABLE <table_name> PARALLEL <integer>;
SELECT table_name, degree
FROM user_tables;

ALTER TABLE ext_tab2 PARALLEL 2;

SELECT table_name, degree
FROM user_tables;
Project Column ALTER TABLE <table_name> PROJECDT COLUMN <ALL | REFERENCED>;
SELECT table_name, property
FROM user_external_tables;

ALTER TABLE ext_tab2 PROJECT COLUMN REFERENCED;

SELECT table_name, property
FROM user_external_tables;

ALTER TABLE ext_tab2 PROJECT COLUMN ALL;

SELECT table_name, property
FROM user_external_tables;
Reject Limit ALTER TABLE <table_name> REJECT LIMIT <integer>;
SELECT table_name, reject_limit
FROM user_external_tables;

ALTER TABLE ext_tab2 REJECT LIMIT 2;

SELECT table_name, reject_limit
FROM user_external_tables;
Rename To ALTER TABLE <current_table_name> RENAME TO <new_table_name>;
ALTER TABLE ext_tab2 RENAME TO ext_tab9;
Set Unused ALTER TABLE <table_name> SET UNUSED COLUMN <column_name>;
Warning: Do not use this syntax as it is misleading. It is translated directly into a DROP COLUMN command
Target File Name ALTER TABLE <table_name> LOCATION ('<file1>', '<file2>', ....);
SELECT * FROM ext_tab;

ALTER TABLE ext_tab LOCATION ('demo1.dat');

SELECT * FROM ext_tab;

ALTER TABLE ext_tab LOCATION ('demo1.dat', 'demo2.dat');

SELECT * FROM ext_tab;

ALTER TABLE ext_tab LOCATION ('demo2.dat');
 
Drop Table
Drop an External Table DROP TABLE <table_name>;
DROP TABLE ext_tab;
 
External Table Demos
The following demo shows how to parse a single file row into multiple table rows. -- this demo uses the EXT directory created at the top of the page.
-- first create the file multirow.dat in the location corresponding with the EXT directory from the following


12,Morgan,123456@@1234567@@876556612,1200
14,Smithy,1234@@4567@@12345@@45778@34566@23456,1345

-- create an external table pointing to the multirow.dat file
CREATE TABLE multirow (
empno  VARCHAR2(10),
ename  VARCHAR2(20),
projs  VARCHAR2(1000),
salary VARCHAR2(10))
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY ctemp
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET UTF8
    BADFILE ctemp:'bad_%a_%p.bad'
    LOGFILE ctemp:'log_%a_%p.log'
    READSIZE 1048576
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"' LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (empno CHAR(255), ename CHAR(255), projs CHAR(250), salary CHAR(255)))
    LOCATION ('multirow.dat'))
PARALLEL
REJECT LIMIT 0
NOMONITORING;

col projs format a40

SELECT * FROM multirow;

CREATE TABLE target (
empno  NUMBER(3),
ename  VARCHAR2(20),
salary NUMBER(10));

ALTER TABLE target
ADD CONSTRAINT pk_target
PRIMARY KEY (empno);

CREATE TABLE emp_proj (
empno NUMBER(3),
projs NUMBER(10));

ALTER TABLE emp_proj
ADD CONSTRAINT pk_emp_proj
PRIMARY KEY (empno, projs);

ALTER TABLE emp_proj
ADD CONSTRAINT fk_empno
FOREIGN KEY (empno)
REFERENCING target(empno);

SELECT empno, ename, salary, 1,
SUBSTR(projs, INSTR(projs,'@@',1,3)+2, INSTR(projs,'@@',1+3+1)-INSTR(projs,'@@',1,3)-2) projs
FROM (
  SELECT TO_NUMBER(et.empno) EMPNO,
         et.ename,
         TO_NUMBER(et.salary) SALARY,
         COLUMN_VALUE i,
         '@@' || et.projs || '@@' projs
  FROM multirow et, TABLE(CAST(MULTISET(
       SELECT level FROM dual
       CONNECT BY level <= (LENGTH(et.projs)-LENGTH(REPLACE(et.projs,'@@','')))/2+1) AS SYS.ODCINUMBERList)));

INSERT ALL
WHEN (i=1) THEN INTO target (empno, ename, salary) VALUES (empno, ename, salary)
WHEN (i>0) THEN INTO emp_proj (empno, projs) VALUES (empno, projs)
SELECT empno, ename, salary, i,
SUBSTR(projs, INSTR(projs,'@@',1,3)+2, INSTR(projs,'@@',1+3+1)INSTR(projs,'@@',1,3)-2) projs
FROM (
  SELECT TO_NUMBER(et.empno) EMPNO,
         et.ename,
         TO_NUMBER(et.salary) SALARY,
         column_value i,
         '@@' || et.projs || '@@' projs
  FROM multirow et,
       TABLE(CAST(MULTISET(
       SELECT level FROM dual
       CONNECT BY LEVEL <= (LENGTH(et.projs) - LENGTH(REPLACE(et.projs, '@@', '')))/2+1)
       AS sys.odciNumberList)) et);

SELECT * FROM target;
Preprocess Table Unzip conn sys@pdbdev

CREATE OR REPLACE DIRECTORY 'orahome AS '/home/oracle';

GRANT read, write ON DIRECTORY orahome TO uwclass;

conn uwclass/uwclass@pdbdev

-- this demo uses the EXT directory created at the top of the page.
-- first create the file multirow.dat in the location corresponding with the EXT directory from the following


12,DMorgan,10000
14,BPace,25000

CREATE TABLE unzipme (
empno  NUMBER(3),
ename  VARCHAR2(20),
salary NUMBER(10))
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY etl_home
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET UTF8
    BADFILE etl_home:'bad_%a_%p.bad'
    LOGFILE etl_home:'log_%a_%p.log'
    READSIZE 1048576
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"' LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (empno CHAR(60), ename CHAR(60), salary CHAR(60)))
    LOCATION ('etl_file.dat'))
PARALLEL
REJECT LIMIT 0
NOMONITORING;

Related Topics
Big Data
Data Pump
SQL*Loader
Tables
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx