ORACLE: Load Data from a File

If you're asked to load data from a file in Oracle, you can use the external tables. External tables allow you to query data from a file and treat it almost like a table (there are some limitations to external tables such as you can't create index, you can't INSERT or UPDATE an external tables). Let's practice through an example.

Let's say that we have this csv file named records.csv

76,10,0110,0.00,460008
77,10,0210,350000.00,460009
78,30,0110,0.00,430084
79,10,0110,500000.00,430085
80,30,0110,0.00,497014

The first step is to determine which folder you want this file to be put into. What you should concern here is that Oracle should have the read and write access right to the file. For this example, we create a new directory in 'C:\Data'. After physically creating the directory, we then create a directory in Oracle

CREATE DIRECTORY records_dir AS 'C:\Data';

Here, records_dir is the unique name of the directory, you can name you directory to something else.

Then we create the table which defines the external table. In the CREATE TABLE statement, we define what fields are in the external table, where is the directory for the external table, the filename, etc.. For this example we create a table named records_table

CREATE TABLE records_table (

record_no int,

code int,

message varchar2(4),

amount number(8,2),

message2 varchar(6)

)

ORGANIZATION EXTERNAL (

DEFAULT DIRECTORY records_dir

ACCESS PARAMETERS (

records delimited by newline

fields terminated by ','

)

LOCATION ('records.csv')

);

Because the file is a csv file, we define the terminating character for each field as ','. External table can also load data from a fixed column file.

We can then select the data from the external table by using the SELECT statement (you can also add the WHERE clause)

SELECT * FROM records_table

Here is the output example run from toad

You can then insert the data from an external table to your table.

CREATE TABLE in_records_table as

(SELECT * FROM records_table);

You can also set the REJECT LIMIT parameter of the external table. If Oracle found any error while processing a record, the record will be rejected. And if the number of rejected records exceeded the REJECT LIMIT parameter, your select statement will fail. You can change the REJECT LIMIT parameter by the statement

ALTER TABLE table_name REJECT LIMIT value

After processing the select statement of an external table, Oracle creates some files which are the bad file, discard file and the log file. The bad file contains all of the rejected records, while in the log file you can see Oracle activity when processing the external table like why a record was rejected. By default, these files are created in the same directory with the input file.

Another way to load data from a file to Oracle is by using the sqlldr command. You can see here for how to use the command.