Oracle | External Tables

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;