Home » RDBMS Server » Server Utilities » How to load rejected records in SQLLDR into a table (SQLLDR)
How to load rejected records in SQLLDR into a table [message #388623] Wed, 25 February 2009 13:09 Go to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 #388625 is a reply to message #388623] Wed, 25 February 2009 13:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try to use external table combined with other feature like LOG ERROR clause.

Regards
Michel
Re: How to load rejected records in SQLLDR into a table [message #388627 is a reply to message #388623] Wed, 25 February 2009 13:18 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
You may want to look at external tables. Much more flexible.

From there you can load all the records into an external table and then load them into the table you want, with the rejected records going into another table.
Re: How to load rejected records in SQLLDR into a table [message #388629 is a reply to message #388623] Wed, 25 February 2009 13:21 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As ThomasG kindly suggested,
it all depends on why the records failed to load.
Using external tables is better option.
In certain cases like failed constraints, you can try this
http://www.orafaq.com/forum/m/70632/42800/#msg_70632
Re: How to load rejected records in SQLLDR into a table [message #388632 is a reply to message #388624] Wed, 25 February 2009 13:41 Go to previous messageGo to next message
chilk_faq
Messages: 3
Registered: February 2009
Junior Member
Thanks Thomas,

These rejected records are duplicate data. So I want these records to be loaded into a seperate table with no primary key.
Re: How to load rejected records in SQLLDR into a table [message #388640 is a reply to message #388623] Wed, 25 February 2009 14:25 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>So I want these records to be loaded into a seperate table with no primary key.
Will not happen!


I do not recommend doing the following,
but you could always disable the PK prior to loading the data.
Of course this defeats the purpose of having the PK in 1st place.

[Updated on: Wed, 25 February 2009 14:27]

Report message to a moderator

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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
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 #388651 is a reply to message #388647] Wed, 25 February 2009 19:11 Go to previous messageGo to next message
chilk_faq
Messages: 3
Registered: February 2009
Junior Member
Thank you! I will try yor suggestion.

I have one more question. Is there any way in sqlldr, where in we can identify if the row being inserted is a duplicate row and direct it into a different table if it is duplicate.

Re: How to load rejected records in SQLLDR into a table [message #388667 is a reply to message #388651] Wed, 25 February 2009 21:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
Instead of reloading the bad data, you could create an external table that points to that file, but it would replace, instead of appending to, any additional data in that table. All SQL*Loader is capable of doing directly is dumping anything that it can't load to a bad file. SQL*Loader cannot directly load rejects into another table.

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 Go to previous message
Barbara Boehmer
Messages: 8633
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

Previous Topic: import
Next Topic: How to use Dynamic data file name in SQL*Loader control file? (merged)
Goto Forum:
  


Current Time: Tue Dec 06 14:23:01 CST 2016

Total time taken to generate the page: 0.10214 seconds