Database Reference
In-Depth Information
The most common method is to use the
BADFILE
option. Here, Oracle will record all records that failed
processing. For example, if our control file contained a record with
DEPTNO 'ABC'
, that record would fail and end up
in the bad file because
'ABC'
cannot be converted into a number. We'll demonstrate that in the following example.
First, we add the following as the last line of
demo1.ctl
(this will add a line of data that cannot be loaded to
our input):
ABC,XYZ,Hello
Next, we run the following command, to prove that the
demo1.bad
file does not yet exist:
EODA@ORA12CR1> host ls -l demo1.bad
ls: demo1.bad: No such file or directory
Then we query the external table to display the contents:
EODA@ORA12CR1> select * from SYS_SQLLDR_X_EXT_DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
10 Sales Virginia
20 Accounting Virginia
30 Consulting Virginia
40 Finance Virginia
Now we will find that the file exists and we can review its contents:
EODA@ORA12CR1> host ls -l demo1.bad
-rw-r----- 1 oracle dba 14 Mar 9 10:38 demo1.bad
EODA@ORA12CR1> host cat demo1.bad
ABC,XYZ,Hello
But how can we programmatically inspect these bad records and the log that is generated? Fortunately, that is
easy to do by using yet another external table. Suppose we set up this external table:
EODA@ORA12CR1> create table et_bad
2 ( text1 varchar2(4000) ,
3 text2 varchar2(4000) ,
4 text3 varchar2(4000)
5 )
6 organization external
7 (type oracle_loader
8 default directory SYS_SQLLDR_XT_TMPDIR_00000
9 access parameters
10 (
11 records delimited by newline
12 fields
13 missing field values are null
14 ( text1 position(1:4000),
15 text2 position(4001:8000),
