How to use Oracle External tables to import text file data

How to use Oracle External tables to import text file data

Recently, colleagues are busy with Data Consistency comparison. They need to compare the data in different text files. Some files are large and have many records. If they are opened in a common text editor, it is obvious that, it will be very difficult, or even cannot be opened.

Based on this, you can import the data of the text file to the database for comparison at the collection level.

So how to import text file data into the database? Here, we mainly use the External table feature of Oracle.

Oracle External tables support two types of drivers: ORACLE_LOADER, which means that the data in external tables must be from file files, and ORACLE_DATAPUMP, the data in the External table must be a binary dump file. The dump file is a file previously filled with Oracle internal table data imported into the external table. Obviously, Oracle wants to keep the data in the database for processing.

First, let's take a look at the size and record of the text file.

[Oracle @ node2 ~] $