Use Oracle external tables

Introduction

External tables receive data from the flat files stored outside of the database and allow Oracle® to query the data in those files. Oracle can parse any file format supported by the SQL\*Loader. You can't use data manipulation language (DML) on external tables, but you can use DML for query, join, and sort operations. You can also create views and synonyms against the external tables.

To take advantage of the external table feature, you should use the ORACLE_LOADER access driver and make sure the data files are in the text format. You should also understand Structured Query Language (SQL) so you can create an external table and perform queries against it. 

Make an external table in an Oracle database

Create a directory

Oracle can parse any file format supported by the "SQL\*Loader." You can't use data manipulation language (DML) on external tables, but you can use DML for query, join, and sort operations. You can also create views and synonyms against the external tables.

To take advantage of the external table feature, you should use the ORACLE_LOADER access driver and make sure the data files are in the text format. You should  also understand Structured Query Language (SQL) so you can create an external table and perform queries against it.

Make an external table in an Oracle database

Create a directory

Use the following code to create a directory that points to the location of the comma-separated value (CSV) or text files provided by a user.

 create directory vk_dir as '/opt/oracle/app/admin/je1prd/mods/151214_CHG0004529';

Create an external table

Use the following SQL to create an external table:

    create table kumarv5.VK_4529_ext
    (
      IBMCU   NCHAR(12),
      IBLITM   NCHAR(25),
      IBANPL   NUMBER
    )
    organization external
    (
       type oracle_loader
       default directory vk_dir
       access parameters
       (
         records delimited by newline
         logfile vk_dir:'VK_4529_ext.log'
         badfile vk_dir:'VK_4529_ext.bad'
         discardfile vk_dir:'VK_4529_ext.dcf'
         skip 1
         fields
         terminated by '~'
         missing field values are null
         reject rows with all null fields
         (
           IBMCU,
           IBLITM,
           IBANPL
         )
       )
       location
       (
         'data14.csv'
       )
    )
    reject limit unlimited;

#### Create a local table

Because you can't perform DML on the external tables, use the following code to
create a local table based on the external one:

    --create
    create table kumarv5.VK_4529_int
    (
      IBMCU   NCHAR(12),
      IBLITM   NCHAR(25),
      IBANPL   NUMBER
    )
    tablespace proddtat;
 

Copy data from the external to the local table

Use the following code to copy data from the external to the local table:

    --insert
    Insert into kumarv5.VK_4529_int   select * from kumarv5.VK_4529_ext;
    commit;
    select count(*) from kumarv5.VK_4529_int;

Update the main production table

After you create the local table based on the external one, you can use the following code to update the main production table from the local table:

    update PRODDTA.F43090 a
        set a.PCMCU='         1AM'
        WHERE (a. IBMCU, a. IBLITM, a. IBANPL) in
           (select b. IBMCU, b. IBLITM, b. IBANPL from kumarv5.VK_4529_int b );

Load data from external tables to regular tables

The following image shows you how to load data from an external table to a
regular table:

Image Source

Conclusion

External tables enable you to access data from external sources as if it were in a table in the database. If users provide data in a spreadsheet or CSV format, and you need to update database tables based on the Excel sheet, an external table comes in handy.