{"id":1490,"date":"2020-07-23T07:00:00","date_gmt":"2020-07-23T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=1490"},"modified":"2026-03-23T17:34:46","modified_gmt":"2026-03-23T14:34:46","slug":"pos-fortonoyme-archeia-csv-txt-se-vasi-dedom","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-fortonoyme-archeia-csv-txt-se-vasi-dedom\/","title":{"rendered":"How do we load files (csv, txt) into Oracle database"},"content":{"rendered":"

It is possible to import files like csv and txt into an oracle database table. There are two similar ways. With use SQL*Loader<\/strong> or by use External Table<\/strong>Their mode of operation is similar, but depending on the case, it is more appropriate to use one method. <\/p>\n\n\n\n

SQL*Loader<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

When is SQL*Loader intended to be used?<\/h5>\n\n\n\n

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.<\/p>\n\n\n\n

The example using SQL*Loader<\/h5>\n\n\n\n

We should create the table.<\/p>\n\n\n\n

create table import(id integer, name varchar2(100));<\/pre>\n\n\n\n
Table created.<\/code><\/pre>\n\n\n\n
We make the control file with the parameters for loading named dokimi.ctl.<\/p>\n\n\n\n
load data\n infile '\/home\/oracle\/test.csv'\n append\n into table sys.import\n fields terminated by \";\" -- delimiter\n ( id, name )<\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
sqlldr \"'sys\/Passw0rd1 as SYSDBA'\" control=\/home\/oracle\/dokimi.ctl<\/pre>\n\n\n\n
External Table<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
When is the use of the External Table foreseen?<\/h5>\n\n\n\n
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.<\/p>\n\n\n\n
The example using the External Table<\/h5>\n\n\n\n
We create a logical directory.<\/p>\n\n\n\n
SQL> create  directory imp3 as '\/home\/oracle';<\/pre>\n\n\n\n
Directory created.<\/code><\/pre>\n\n\n\n
We execute the create table statement with the organization external option and the file parameters.<\/p>\n\n\n\n
CREATE TABLE import2(\n  id integer,\n  name VARCHAR2(100))\n  ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY imp3\n                         ACCESS PARAMETERS (FIELDS TERMINATED BY ';')\n                         LOCATION ('test.csv'))parallel 5;<\/pre>\n\n\n\n
Sources:<\/h2>\n\n\n\n