External Tables
Week 9: Articles
External Tables
Learning Outcomes
- Learn how to work with external tables.
- Learn how to define, set ownership, and privileges of external source files.
- Learn how to define and grant access to Oracle’s virtual directories.
Lesson Materials
This article relies on your understanding general concepts about Oracle’s external tables, which can be found in the following two blog posts:
Oracle external tables are a neat device because they let you define a table inside the database that is dependent on an external physical file. You can import data through that file. This post explains the processes and potential problems with Oracle External Tables.
You have two options when you create Oracle External Tables. They may be defined as SQL*Loader or Oracle Data Pump files. SQL*Loader files are read-only and independent of any proprietary Oracle code and are best represented by Comma Separated Value (CSV), Tabbed Separated Value (TSV), or position specific files. Oracle Data Pump files are read-write and require an Oracle executable to read and write them.
Preparing to Deploy an External CSV Table →
Expand this section to see the steps to create, define and grant permissions, and prepare external elements for a CSV dependent external table.
External files are read-only when you use SQL*Loader. You must use Oracle Data Pump when you want to make them read and write files.
Task #1
You must first create a virtual directory, which requires that you have also created a physical directory (or folder) on your file system. After you’ve created the physical directory, file, and virtual directory, you grant schema privileges to read or to read and write the contents of the virtual directory. If you haven’t created the physical file or directory, that won’t impede creating the virtual directory and granting permissions because they act independently.
If you don’t plan on having any log, bad record failure, or discard files, you can grant read only permissions on the virtual directory. You must grant read and write privileges when you also want to generate log and exception files. It is actually a Best Practice to separate the source and log files.
- Create the
IMPORTER
user account by signing on as theSYSTEM
administrative user. TheIMPORTER
user is referred to thoughout the post but you may use a different user if you would like to do so.
CREATE USER importer IDENTIFIED BY password; GRANT CONNECT, resource, CREATE any VIEW TO importer; |
- Create the virtual directory as a privileged user,
SYS
, and don’t forget to create the physical directory and file on the file system (the file management steps aren’t shown).
CREATE DIRECTORY download AS 'C:\Download'; |
- As the privileged user,
SYS
, grant the read only privilege to the schema that will read the data:
GRANT READ ON DIRECTORY download TO importer; |
Alternatively, if you want to write log or exception files to the same directory, you grant read and write like the following. This example will require you to grand both read and write permissions.
GRANT READ, WRITE ON DIRECTORY download TO importer; |
- Create a Comma Separated Value (CSV) file, like this, in the target directory:
1,'Indiana','Jones' 2,'Ravenwood','Marion' 3,'Marcus','Brody' 4,'Rene','Belloq' |
Implementing an External CSV Table →
Expand this section to see the steps to create an external table for importing from a CSV dependent external table.
Task #2
- Create an External Table that depends on a Comma Separated Value (CSV) file, but doesn’t write errors during the read from the file. This example depends on your using the
IMPORTER
user, created in a prior preparation steps. The followingcharacter
table usesoracle_loader
method to read an external file.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE TABLE CHARACTER ( character_id NUMBER , first_name VARCHAR2(20) , last_name VARCHAR2(20)) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY download ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII NOBADFILE NODISCARDFILE NOLOGFILE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" MISSING FIELD VALUES ARE NULL ) LOCATION ('character.csv')) REJECT LIMIT 0; |
You should note that the rejection limit is set to zero because logging is disabled. This is a good practice because you’re should be assuming an all or nothing on the load without the log information.
Create an external table that uses the SQL*Loader method to read a file. The following requires that you’ve granted write privileges because it log activity and errors to external files.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE TABLE CHARACTER ( character_id NUMBER , first_name VARCHAR2(20) , last_name VARCHAR2(20)) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY download ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE CHARACTER DISCARDFILE CHARACTER LOGFILE CHARACTER FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" MISSING FIELD VALUES ARE NULL ) LOCATION ('character.csv')) REJECT LIMIT UNLIMITED; |
The rejection limit is unlimited because they’ll be captured in the enabled log files.
Unlike the ".csv"
in the LOCATION
argument, you can’t append a ".log"
, ".bad"
, or ".dis"
extensions for log, bad, or discard files unless you enclose them in single quotes. You’ll raise an ugly exception if you include those exceptions when you query the table. Unfortunately, the syntax error doesn’t throw an exception when you create the table.
Here’s an example of the runtime exception stack:
SELECT * FROM character * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-00554: error encountered while parsing access parameters KUP-01005: syntax error: found "dot": expecting one of: "badfile, byteordermark, characterset, colon, column, data, delimited, discardfile, disable_directory_link_check, fields, fixed, load, logfile, language, nodiscardfile, nobadfile, nologfile, date_cache, processing, readsize, string, skip, territory, vari" KUP-01007: at line 2 column 20 |
You can override the ".log"
, ".bad"
, or ".dis"
extensions with the following syntax, which also states the virtual directory where they should be written. Please note that the virtual directory MUST BE IN UPPERCASE only!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE TABLE CHARACTER ( character_id NUMBER , first_name VARCHAR2(20) , last_name VARCHAR2(20)) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY download ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE 'DOWNLOAD':'character.bad' DISCARDFILE 'DOWNLOAD':'character.dis' LOGFILE 'DOWNLOAD':'character.log' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" MISSING FIELD VALUES ARE NULL ) LOCATION ('character.csv')) REJECT LIMIT UNLIMITED; |
If you forget to make the virtual directory an uppercase string in the BADFILE
, DISCARDFILE
, or LOGFILE
, you’ll raise the following error stack:
ERROR at line 1: ORA-29913: error IN executing ODCIEXTTABLEOPEN callout ORA-29400: DATA cartridge error KUP-04080: directory object download NOT found |
Implementing an External Position Specific Table →
Expand this section to see the steps to create an external table for importing from a CSV dependent external table.
Alternative Task #2
You can also use position specific files. Position specific files are the only way to import dates that aren’t in conforming patterns. The following is a quick example of leveraging non-conforming date formats:
, release_date CHAR(10) date_format DATE mask "yyyy/mm/dd" |
There are two separate syntax patterns but only one is mentioned in the Oracle 11g Database Utilities manual (the first that uses CHAR(n)
to designate length).
1 Apple 1.49 2 Orange 2 |
You could create a position specific file for the same data but it’s probably easier with a different file example. This example fruits and their price per pound, like this one below.
The following uses field length strengths by data type:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | CREATE TABLE grocery ( grocery_id NUMBER , item_name VARCHAR2(20) , item_amount NUMBER(4,2)) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY download ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE 'DOWNLOAD':'grocery.bad' LOGFILE 'DOWNLOAD':'grocery.log' FIELDS MISSING FIELD VALUES ARE NULL ( grocery_id CHAR(3) , item_name CHAR(20) , item_amount CHAR(4))) LOCATION ('grocery.csv')) REJECT LIMIT UNLIMITED; |
The other approach is to label the starting and ending character positions. It’s more tedious because you need to keep track of both values. Also, don’t forget character positions are 1-based numbers.
This uses absolute start and end position numbers:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | CREATE TABLE grocery ( grocery_id NUMBER , item_name VARCHAR2(20) , item_amount NUMBER(4,2)) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY download ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE 'DOWNLOAD':'grocery.bad' LOGFILE 'DOWNLOAD':'grocery.log' FIELDS MISSING FIELD VALUES ARE NULL ( grocery_id POSITION(1:3) , item_name POSITION(4:23) , item_amount POSITION(24:27))) LOCATION ('grocery.csv')) REJECT LIMIT UNLIMITED; |
You may also want to review this article. It shows you how to check if the external file is there before you query the data.