|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
External tables in Oracle
External tables can read flat files (that follow some rules) as though they were ordinary (although read-only) Oracle tables. Therefore, it is convinient to use external tables to load flat files into the DB.
The following two examples show how to 'import' a file with external tables.
The first file consists of 4 records whose fields (attributes) are seperated by commas:
1,one,first 2,two,second 3,three,third 4,four,fourth
The second file consists of 4 records whose first field is a fixed length field of 4 characters.
B000Albert B001Basil B002Caesar B003Darius
In order to reference these files, a directory must be created.
create or replace directory ext_dir as '/home/rene/ext_dir';
Of course, /home/rene/ext_dir must point to the directory where the file actually resides.
In order to prevent a ORA-29913, someone must grant read and write on the directory to the user that uses the directory:
grant read, write on directory ext_dir to rene;
It should be self explanatory that you cannot (in most cases) grant this privilege to yourself.
Now, we're ready to create the table. We assume that the file name is file.csv.
create table ext_table_csv ( i Number, n Varchar2(20), m Varchar2(20) ) organization external ( type oracle_loader default directory ext_dir access parameters ( records delimited by newline fields terminated by ',' missing field values are null ) location ('file.csv') ) reject limit unlimited;
It's now possible to select from ext_table_csv:
select * from ext_table_csv;
Fixed field length
create table ext_table_fixed ( field_1 char(4), field_2 char(30) ) organization external ( type oracle_loader default directory ext_dir access parameters ( records delimited by newline fields ( field_1 position(1: 4) char( 4), field_2 position(5:30) char(30) ) ) location ('file') ) reject limit unlimited;
It's now possible to select from ext_table_fixed:
select * from ext_table_fixed;
In the directory specified with ext_dir, a log file will as well be written upon selecting from the external table.
Reading the alert log through an external table
See also Loading dates with external tables in Oracle that shows how to specify a date format mask.