LOAD DATA INFILE equivalent in Oracle

Question

I just was wodering if there's an equivalent to MySQL LOAD DATA INFILE statemnent in Oracle? I need it because I want to read from a huge textfile into a database table.

Answer 1

Oracle gives the SQLLoader commandline utility. But it relies on a proper formatting of the data file.

You can try to look at Oracle External Tables (e.g. you can link a csv file as an external table and see it as a table within Oracle).

Both solutions have pros and cons, but the big cons is that they still rely on data input format (so if you have a file ready for mysql, you may need to tweak it a bit).

Answer 2

Example on windows 10 and Oracle 12c

if you have a text file with records of each table delimited by comma, you can do this:

Create a control file for each table, called table_name.ctl (C:\Users\user\Desktop\directory\table_name.ctl)

load data 
infile 'C:\Users\user\Desktop\directory\table_name.txt' 
append
into table table_name
fields terminated by ","
(id, field2,field3)

After, In windows you should open Cmd and load data in each table, and then load data remotely for example in a aws server.

sqlldr userid=USER@AWS_PDB1/password
control='C:\Users\user\Desktop\directory\table_name.ctl' log='C:\Users\user\Desktop\directory\table_name.log' direct=true

or

sqlldr control='C:\Users\user\Desktop\directory\table_name.ctl' log='C:\Users\user\Desktop\directory\table_name.log' direct=true
and then ask them the user and password

If you have the following error:“The program can’t start because oranfsodm12.dll is missing from your computer. Try reinstalling the program to fix this problem.”

it is because SQL * Loader is disabled and can not be used in the console windows, this is solved enabling the following steps (as http://www.dallasmarks.com/installing-two-oracle-12c-clients-on-one-server/):

1. Should go to the folder C:\oracle\client\user\product\12.1.0\client_1\BIN

2. Make a copy of oraodm12.dll file, calling the new file oranfsodm12.dll, and paste it in the same BIN folder.

3. Run the command again from cmd.