Data Loading and Unloading - Expert Oracle Database Architecture - page 705

You have to explicitly enable this optimization, and you should think about whether it is safe for you to use or not
(only you know enough about your application and its processing to answer the question “is it safe?”). Using the
earlier example with the bad line of data added, we would expect to see the following output upon querying our
external table:
EODA@ORA12CR1> select dname from SYS_SQLLDR_X_EXT_DEPT;
DNAME
--------------
Sales
Accounting
Consulting
Finance
EODA@ORA12CR1> select deptno from SYS_SQLLDR_X_EXT_DEPT;
DEPTNO
----------
10
20
30
40
We know the bad record has been logged into the BADFILE . But if we simply ALTER the external table and tell
oracle to only project (process) the referenced columns as follows, we get different numbers of rows from
each query:
EODA@ORA12CR1> alter table SYS_SQLLDR_X_EXT_DEPT
2 project column referenced
3 /
Table altered.
EODA@ORA12CR1> select dname from SYS_SQLLDR_X_EXT_DEPT;
DNAME
--------------
Sales
Accounting
Consulting
Finance
XYZ
EODA@ORA12CR1> select deptno from SYS_SQLLDR_X_EXT_DEPT;
DEPTNO
----------
10
20
30
40
the DNAME field was valid for every single record in the input file, but the DEPTNO column was not. if we do not
retrieve the DEPTNO column, it does not fail the record—the resultset is materially changed.