How do we load files (csv, txt) into Oracle database
- How do we collect the actual execution plan from queries using Extended Event and how do we read its data - 2 December 2024
- How do we find what permissions a user has on an Oracle database - 1 November 2024
- How we enable Unified Auditing in Oracle Database - 7 October 2024
It is possible to import files like csv and txt into an oracle database table. There are two similar ways. With use SQL*Loader or by use External Table. Their mode of operation is similar, but depending on the case it is more appropriate to use one method.
SQL*Loader
With SQL*Loader we should have created the table before importing the file. Through a control file we define the parameters such as the path of the file and the delimiter it uses.
When is SQL*Loader intended to be used?
Its use is optimal when we want to load data remotely, either in an intermediate table containing indexing and finally when we do not need to modify the data during import.
The example using SQL*Loader
We should create the table.
create table import(id integer, name varchar2(100));
Table created.
We make the control file with the parameters for loading named dokimi.ctl.
load data infile '/home/oracle/test.csv' append into table sys.import fields terminated by ";" -- delimiter ( id, name )
To carry out the execution, we call sqlldr as a user who has rights, mentioning the path where the control file we made is located.
sqlldr "'sys/Passw0rd1 as SYSDBA'" control=/home/oracle/dokimi.ctl
External Table
By using External Table we should create a logical directory with the location where the file will be located and we should create the table as organization external.
When is the use of the External Table foreseen?
Using External Table is optimal when we want to import the data in parallel and when we want to format the data as we import it.
The example using the External Table
We create a logical directory.
SQL> create directory imp3 as '/home/oracle';
Directory created.
We execute the create table statement with the organization external option and the file parameters.
CREATE TABLE import2( id integer, name VARCHAR2(100)) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY imp3 ACCESS PARAMETERS (FIELDS TERMINATED BY ';') LOCATION ('test.csv'))parallel 5;