Wednesday, October 13, 2010

Oracle External Table Examples

If you have some temporary data to import to the database you can use an external table instead.

Lets amuse that you have the following file.

myfile.txt
1
2
3
....

1) You must create a directory and grant read writes to your user.

create or replace directory my_dir as '/home/oracle';

grant read on directory my dir to my_user;

2) Put your file there and


create table mytable_load
(
a int
)
organization external
(
type oracle_loader default directory my_dir
access parameters (RECORDS DELIMITED BY NEWLINE FIELDS)
location ('myfile.txt')
);


... and you have your table.

This is an example of adding a window csv file created by Excel , having Headings and the Windows new line as record terminator:

create table mytable_load
(
a int,
b varchar2(100 char),
c date
)
organization external
(
type oracle_loader default directory my_dir
access parameters (RECORDS DELIMITED BY '\r\n' skip 1 FIELDS TERMINATED BY ';')
location ('myfile.csv')
);

No comments:

Post a Comment