Equivalent to LOAD DATA INFILE in Oracle

Question

I just searched if there is MySQL equivalent LOAD DATA INFILE statemnent in Oracle? I need this because I want to read from a huge text file to a database table.

Answer 1

Oracle provides the SQLLoader command line utility . But it relies on the correct formatting of the data file.

You can look at the Oracle external tables (for example, you can link the csv file as an external table and see it as a table inside Oracle).

Both solutions have their pros and cons, but the big disadvantages are that they still rely on the data input format (therefore, if you have a ready-made file for mysql, you may need to configure it a bit).

Answer 2

Example for Windows 10 and Oracle 12c

if you have a text file with entries of each table, separated by a 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) 

Then on Windows, you have to open Cmd and load the data in each table, and then load the data remotely, for example, on the 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 cannot start because oranfsodm12.dll is missing on your computer. Try reinstalling the program to fix this problem."

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

• Must go to C: \ oracle \ client \ user \ product \ 12.1.0 \ client_1 \ BIN folder

• Make a copy of the oraodm12.dll file by calling the new oranfsodm12.dll file and paste it into the same BIN folder.

• Run the command again from cmd.