How to add an update to existing SQL Loader control file?

Question

How to create an external table from my text file and use that to update to real table, rather than creating a (normal) staging table and using SQL*Loader.

sample.ctl

load data 
infile 'data.txt'
append into table newtable
fields terminated by '|' 
TRAILING NULLCOLS
(ACCOUNTNBR)

data.txt

some raw datas..

54545554
54542145
65656566
58787788

Answer 1

1. The file, info.dat, created with data identified above.

2. Set-up a default directory (which contains the data source) and to grant access to it (need the CREATE ANY DIRECTORY privilege):

   CREATE DIRECTORY ext_tab_dir AS '/usr/apps/datafiles';

   GRANT READ ON DIRECTORY ext_tab_dir TO SCHEMA_OWNER;

3. Create an external table named newtable_load:

   CREATE TABLE newtable_load
   (ACCOUNTNBR NUMBER
   )
      ORGANIZATION EXTERNAL
      (TYPE ORACLE_LOADER
      DEFAULT DIRECTORY default_directory
      ACCESS PARAMETERS
      (RECORDS DELIMITED BY NEWLINE
       FIELDS (ACCOUNTNBR NUMBER)
      )
      LOCATION ('info.dat')
   );
   

4. Load data from the external table newtable_load into the table newtable:

INSERT INTO newtable (ACCOUNTNBR) (SELECT ACCOUNTNBR FROM newtable_load);