Thank you for your clarification. I understood it now.
Just to add to your answer, I think we could use SQL*Loader with external_table=execute option to get "the best of both worlds" (external tables and SQL*Loader).
For instance, I've launched SQL*Loader as follows (the important part here is external_table=execute command line parameter):
[velikikh@comp2364 20160822]$ sqlldr userid=velikikh control=demo data=demo external_table=execute
Password:
SQL*Loader: Release 12.1.0.2.0 - Production on Mon Aug 22 11:44:42 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
ORA-28002: the password will expire within 1 days
Path used: External Table
Table SELECTIOTAB:
2 Rows successfully loaded.
Check the log files:
demo.log
demo.log_xt
for more information about the load.
Under the hood, the load created an intermediate directory object and an external table, which may have seen in the log file:
created temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for path /pub/home/velikikh/oracle/tmp
creating external table "SYS_SQLLDR_X_EXT_SELECTIOTAB"
CREATE TABLE "SYS_SQLLDR_X_EXT_SELECTIOTAB"
(
"TXN_ID" VARCHAR2(255),
"ACCOUNT" VARCHAR2(10)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
LOAD WHEN (((1: 2) != "H|") AND ((1: 2) != "T|"))
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo.bad'
LOGFILE 'demo.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "|" LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"TXN_ID" CHAR(255)
TERMINATED BY "|",
"ACCOUNT" CHAR(255)
TERMINATED BY "|"
)
)
location
(
'demo.dat'
)
)REJECT LIMIT UNLIMITED
And then the following SQL statement was generated (the important part here is the APPEND hint - a sign of a possible direct load operation unless we haven't violated any direct-load restrictions):
executing INSERT statement to load database table SELECTIOTAB
INSERT /*+ append */ INTO SELECTIOTAB
(
TXN_ID,
ACCOUNT
)
SELECT
SEQ_IFCE_ID.NEXTVAL,
"ACCOUNT"
FROM "SYS_SQLLDR_X_EXT_SELECTIOTAB"
With the following execution plan (a true direct load):
SQL_ID fcnvxmdwn2vsq, child number 0
-------------------------------------
INSERT /*+ append */ INTO SELECTIOTAB ( TXN_ID, ACCOUNT ) SELECT
SEQ_IFCE_ID.NEXTVAL, "ACCOUNT" FROM "SYS_SQLLDR_X_EXT_SELECTIOTAB"
Plan hash value: 3995769479
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 342 (100)| |
| 1 | LOAD AS SELECT | | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 102K| 697K| 342 (1)| 00:00:01 |
| 3 | SEQUENCE | SEQ_IFCE_ID | | | | |
| 4 | EXTERNAL TABLE ACCESS FULL | SYS_SQLLDR_X_EXT_SELECTIOTAB | 102K| 697K| 342 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
I've got 2 rows in the table with the TXN_ID column populated by SEQUENCE.NEXTVAL:
SQL> select * from selectiotab;
TXN_ID ACCOUNT
---------- ------------------------------
10 account2
15 account3