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. |
| |
| 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,CLINE,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\table_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\rdbms\orabase\orabase\trace\';
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:\temp
-- 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 | |