- How to bulk detach / attach databases in SQL Server - February 2, 2024
- How to enforce password policy in an Oracle database - 4 January 2024
- How do we change the name of the user / schema in an Oracle database - 1 December 2023
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.
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));
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
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';
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;