| How to load rejected records in SQLLDR into a table [message #388623] |
Wed, 25 February 2009 13:09  |
chilk_faq
Messages: 3
Registered: February 2009
|
Junior Member |
|
|
I am using sqlldr to load the .DAT file into a table. This works fine, but I want to load the rejected records into another table instead of a log file. Can you please help me.
If it can not be done from sqlldr, Can you please suggest any other way (like from triggers etc..) to load the bad records into a table.
Thanks in advance.
-Chilk
|
|
|
|
| Re: How to load rejected records in SQLLDR into a table [message #388624 is a reply to message #388623] |
Wed, 25 February 2009 13:15  |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Depends on WHY they failed.
When they are bad, that means the format doesn't fit the SQL*Loader configuration.
You could just load then into one table with one big varchar2 column, with an SQL*Loader configuration that loads the entire line as one row and thus doesn't create any error.
So you have to definitely look at WHY they failed first.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to load rejected records in SQLLDR into a table [message #388647 is a reply to message #388632] |
Wed, 25 February 2009 17:14 |
 |
Barbara Boehmer
Messages: 9091
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can direct the rejected data to a badfile, then use that badfile as an infile to load into another table. Please see the example below.
-- test1.dat containing data to load into dept table:
10,dname10,loc10,
11,dname11,loc11,
garbage that does not fit the format,
-- test1.ctl for loading data into dept table:
load data
infile test1.dat
badfile test2.dat
append into table dept
fields terminated by ','
(deptno, dname, loc)
-- test2.ctl for loading rejected duplicates into dept_dups table:
load data
infile test2.dat
badfile test3.dat
append into table dept_dups
fields terminated by ','
(deptno, dname, loc)
-- test3.ctl for loading impropertly formatted data into dept_bad:
load data
infile test3.dat
append into table dept_bad
fields terminated by ','
(all_data)
-- starting data in dept table:
SCOTT@orcl_11g> select * from dept
2 /
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
-- tables for duplicates and other bad data:
SCOTT@orcl_11g> create table dept_dups as
2 select * from dept where 1 = 2
3 /
Table created.
SCOTT@orcl_11g> create table dept_bad (all_data clob)
2 /
Table created.
-- load data into dept table:
SCOTT@orcl_11g> host sqlldr scott/tiger control=test1.ctl log=test1.log
-- load duplicates into dept_dups table:
SCOTT@orcl_11g> host sqlldr scott/tiger control=test2.ctl log=test2.log
-- load badly formatted data into dept_bad table:
SCOTT@orcl_11g> host sqlldr scott/tiger control=test3.ctl log=test3.log
-- results:
SCOTT@orcl_11g> select * from dept
2 /
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
11 dname11 loc11
SCOTT@orcl_11g> select * from dept_dups
2 /
DEPTNO DNAME LOC
---------- -------------- -------------
10 dname10 loc10
SCOTT@orcl_11g> select * from dept_bad
2 /
ALL_DATA
--------------------------------------------------------------------------------
garbage that does not fit the format
SCOTT@orcl_11g>
|
|
|
|
|
|
|
|
| Re: How to load rejected records in SQLLDR into a table [message #388673 is a reply to message #388651] |
Wed, 25 February 2009 22:04 |
|
Barbara Boehmer
Messages: 9091
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Another alternative would be to create a view on the table, direct SQL*Loader to load the data into the view, and use an instead of trigger that would insert valid data into the table, with an exception section that would insert duplicates into another table. I have provided another demonstration below.
-- test1.dat:
10,dname10,loc10,
11,dname11,loc11,
garbage that does not fit the format,
-- test1.ctl:
load data
infile test1.dat
badfile test2.dat
append into table dept_view
fields terminated by ','
(deptno, dname, loc)
-- starting data:
SCOTT@orcl_11g> select * from dept
2 /
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
-- table for duplicates:
SCOTT@orcl_11g> create table dept_dups as
2 select * from dept where 1 = 2
3 /
Table created.
-- view:
SCOTT@orcl_11g> create or replace view dept_view as select * from dept
2 /
View created.
-- trigger on view to direct valid data to dept table and duplicates to dept_dups table:
SCOTT@orcl_11g> create or replace trigger dept_view_trig
2 instead of insert on dept_view
3 for each row
4 begin
5 insert into dept (deptno, dname, loc)
6 values (:new.deptno, :new.dname, :new.loc);
7 exception
8 when dup_val_on_index then
9 insert into dept_dups (deptno, dname, loc)
10 values (:new.deptno, :new.dname, :new.loc);
11 when others then -- to avoid load failing due to bad format
12 null;
13 end dept_view_trig;
14 /
Trigger created.
SCOTT@orcl_11g> show errors
No errors.
-- load:
SCOTT@orcl_11g> host sqlldr scott/tiger control=test1.ctl log=test1.log
-- results:
SCOTT@orcl_11g> select * from dept
2 /
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
11 dname11 loc11
SCOTT@orcl_11g> select * from dept_dups
2 /
DEPTNO DNAME LOC
---------- -------------- -------------
10 dname10 loc10
SCOTT@orcl_11g>
[Updated on: Wed, 25 February 2009 22:12] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
