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.
Comma delimited
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
Here is a procedure which creates an external table that can be used to read the alert.log.
Links
See also Loading dates with external tables in Oracle that shows how to specify a date format mask.
Source=>http://www.adp-gmbh.ch/ora/misc/ext_table.html
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.
Comma delimited
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
Here is a procedure which creates an external table that can be used to read the alert.log.
Links
See also Loading dates with external tables in Oracle that shows how to specify a date format mask.
Source=>http://www.adp-gmbh.ch/ora/misc/ext_table.html
