Using CSV data as external table in Oracle DB
source link: https://schneide.blog/2020/06/16/using-csv-data-as-external-table-in-oracle-db/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
If you want to import CSV data into an Oracle database you can use the SQL*Loader command line tool. You simple create a control file that describes how to load the data and then call the sqlldr
command with the control file name as an argument:
example.ctl
LOAD DATA INFILE example.csv INTO TABLE example_table FIELDS TERMINATED BY ';' (ID, NAME, AMOUNT, DESCRIPTION)
> sqlldr username/password example.ctl
But there’s another way to load CSV data into an Oracle database: External tables.
External tables
Oracle’s external tables feature allows you to query data from a file on the filesystem like a regular database table.
First you have to create a directory in the file system and put your CSV file inside:
mkdir -p /path/to/directory
example.csv
1;Water;250 2;Beer;500 3;Wine;150
Now connect to the database as “SYS as SYSDBA”, define the directory as a database object and grant read/write access to your user:
CREATE OR REPLACE DIRECTORY external_tables_dir AS '/path/to/directory'; GRANT READ,WRITE ON DIRECTORY external_tables_dir TO example_user;
Now you can connect as example_user
and create an external table for the CSV file:
CREATE TABLE example_table ( id NUMBER(4,0), name VARCHAR2(50), amount NUMBER(8,0) ) ORGANIZATION EXTERNAL ( DEFAULT DIRECTORY external_tables_dir ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ';' ) LOCATION ('example.csv') );
The relevant part here is the ORGANIZATION EXTERNAL
block. It references the directory and the CSV file inside the directory and allows you to specify format parameters of the CSV file such as record and field delimiters.
Now you can query the table like a regular table:
SELECT * FROM example_table
ID NAME AMOUNT -- ----- ------ 1 Water 250 2 Beer 500 3 Wine 150
Access information and errors such as bad or discarded records are stored in log files in the specified directory. The default names of these log files consist of the table name and an ID, e.g. example_table_12345.log
, example_table_12345.bad
and example_table_12345.dsc
.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK