Oracle External Tables | Permissions | Write to External Tables


	CODE Oracle Code Library


	JOBS Find Or Post Oracle Jobs


	FORUM Oracle Discussion & Chat

Search the Reference Library pages:

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle External Tables

Version 11.1

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.

Prior to version 10g, external tables were READ ONLY. Insert, update, and delete could not be performed. Starting with version Oracle Database 10g, external tables can be written to as well as read from. Thanks to "Kathryn" for this update/correction.

Actions As SYS

Related Catalog Objects

dba_external_tables

all_external_tables

user_external_tables

Related System Privileges

create table

create any table

drop any table

Actions As SYS

Create Directory and grant privileges

CREATE OR REPLACE DIRECTORY <name> AS <operating_system_path_and_directory>;

conn / as sysdba

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

GRANT READ ON DIRECTORY ext TO uwclass;
GRANT WRITE ON DIRECTORY ext TO uwclass;

External Table

Create Text File Using a Text Editor

7369,SMITH,CLERK,20
7499,ALLEN,SALESMAN,30
7521,WARD,SALESMAN,30
7566,JONES,MANAGER,20
7654,MARTIN,SALESMAN,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,VP MKTG,10
4444,LOFSTROM,MANAGER,10
5555,ALLEN,SECURITY,30

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

As End User

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>'
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

CREATE TABLE ext_tab (
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
    BADFILE 'bad_%a_%p.bad'
    LOGFILE 'log_%a_%p.log'
    FIELDS TERMINATED 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_tab (
empno CHAR(4),
ename CHAR(20),
job CHAR(20),
deptno CHAR(2))
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;

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>;

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 able_history.exp

DROP TABLE ext_write;

Tab Delimited External Table

CREATE TABLE ext_tab (
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 / as sysdba

CREATE OR REPLACE DIRECTORY bdump AS 'c:\oracle\product\diag dbms\orabase\orabase race\';

CREATE TABLE system.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 '0x0A'
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 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

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;

Records delimited by
RECORDS DELIMITED BY 0x'0A' demo submitted by Tom Burger of Exan Software

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 this:

100001,"7123 HIGHLAND DR","03/28/1965"," Mercer Island, WA 98040","Morgan, Dan A", "","","63034630752", 14,1,"F","T","06/28/2000","",0,"","01/01/1999","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",""

The external file definition to load this data is:

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.

Alter Table

Changing the target file

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');

Create External Table DDL With SQL*Loader

Step 1

copy sqlldr02.ctl and sqlldr02.dat to c: emp
-- 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: emp\sqlldr02 log=c: emp\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

Related Topics

Data Pump

SQL*Loader

Tables

Home : Code Library : Sponsors : Privacy : Terms of Use : Contact Us