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
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)
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
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. */
-- 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
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;