OraFAQ Forum: Server Utilities » Bad records into a Table using sql*loader

Home » RDBMS Server » Server Utilities » Bad records into a Table using sql*loader (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Bad records into a Table using sql*loader [message #565564]

Wed, 05 September 2012 04:55

rajasekhar857
Messages: 500
Registered: December 2008

Senior Member

Hi Is there any process to capture rejected data(bad data) in a table when data is moved using SQL Loader.

Please let me know if not with sql*loader any other approcah will be very much helpful.

Regards,
Rajasekhar M.

Report message to a moderator

Re: Bad records into a Table using sql*loader [message #565566 is a reply to message #565564]

Wed, 05 September 2012 04:58

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No.
Bad means they can't be read as you say it should be read, so if it can't read it can't be insert into a table.

Regards
Michel

Report message to a moderator

Re: Bad records into a Table using sql*loader [message #565588 is a reply to message #565566]

Wed, 05 September 2012 07:10

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can do something like the following with external tables, saying your table contains 2 fields id (integer) and val (varchar2(100):

SQL> create table t1 (id integer, val varchar2(100))
  2  organization external (
  3    type oracle_loader
  4    default directory FILESDIR
  5    access parameters (
  6      records delimited by X'0A'
  7      nobadfile
  8      nologfile
  9      nodiscardfile
 10      fields terminated by ' ' optionally enclosed by '"'
 11      missing field values are null
 12      (id, val)
 13    )
 14    location ('t.txt')
 15  )
 16  reject limit unlimited
 17  /

Table created.

SQL> create table t2 (dummy varchar2(4000))
  2  organization external (
  3    type oracle_loader
  4    default directory FILESDIR
  5    access parameters (
  6      records delimited by X'0A'
  7      nobadfile
  8      nologfile
  9      nodiscardfile
 10      fields terminated by X'0A'
 11      missing field values are null
 12      (dummy)
 13    )
 14    location ('t.txt')
 15  )
 16  reject limit unlimited
 17  /

Table created.

SQL> host type t.txt
1 toto
I am Michel
2 titi

SQL> select * from t1;
        ID VAL
---------- -------------------------------------------------------
         1 toto
         2 titi

2 rows selected.

SQL> select * from t2;
DUMMY
------------------------------------------------------------------
1 toto
I am Michel
2 titi

3 rows selected.

SQL> select * from t2
  2  minus
  3  select id||' '||val from t1
  4  /
DUMMY
------------------------------------------------------------------
I am Michel

1 row selected.

The last query gives you the bad rows.
Adapt it to your needs.

Regards
Michel

[Updated on: Wed, 05 September 2012 07:11]

Report message to a moderator

Re: Bad records into a Table using sql*loader [message #565591 is a reply to message #565588]

Wed, 05 September 2012 07:27

rajasekhar857
Messages: 500
Registered: December 2008

Senior Member

Thanks.

Report message to a moderator

Re: Bad records into a Table using sql*loader [message #565902 is a reply to message #565591]

Fri, 07 September 2012 22:57

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

Instead of creating the t2 table using the whole t.txt file and using minus, you can create the t2 table using a badfile from the t1 table, as demonstrated below. You could also do the same using SQL*Loader with a badfile and two control files.

-- c:\my_oracle_files\t.txt:

1       toto
I       am Michel
2       titi

-- Oracle directory object:

SCOTT@orcl_11gR2> create or replace directory filesdir as 'c:\my_oracle_files'
  2  /

Directory created.

-- t1 table with badfile:
SCOTT@orcl_11gR2> create table t1 (id integer, val varchar2(30))
2 organization external (
3 type oracle_loader
4 default directory FILESDIR
5 access parameters (
6 records delimited by X'0A'
7 badfile 'FILESDIR':'t.bad'
8 nologfile
9 nodiscardfile
10 fields terminated by ' ' optionally enclosed by '"'
11 missing field values are null
12 (id, val)
13 )
14 location ('t.txt')
15 )
16 reject limit unlimited
17 /

Table created.

-- t2 table using t1's badfile:
SCOTT@orcl_11gR2> create table t2 (dummy varchar2(4000))
2 organization external (
3 type oracle_loader
4 default directory FILESDIR
5 access parameters (
6 records delimited by X'0A'
7 nobadfile
8 nologfile
9 nodiscardfile
10 fields terminated by X'0A'
11 missing field values are null
12 (dummy)
13 )
14 location ('t.bad')
15 )
16 reject limit unlimited
17 /

Table created.

-- results:

SCOTT@orcl_11gR2> select * from t1
  2  /

        ID VAL
---------- ------------------------------
         1 toto
         2 titi

2 rows selected.

SCOTT@orcl_11gR2> select * from t2
  2  /

DUMMY
--------------------------------------------------------------------------------
I       am Michel

1 row selected.

Report message to a moderator

Re: Bad records into a Table using sql*loader [message #565903 is a reply to message #565902]

Fri, 07 September 2012 23:10

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

The following does the same using SQL*Loader.

-- t.txt:

1       toto
I       am Michel
2       titi

-- t1.ctl:

load data
infile t.txt "str x'0A'"
into table t1
fields terminated by ' ' optionally enclosed by '"'
trailing nullcols
(id, val)

-- t2.ctl:
load data
infile t.bad "str x'0A'"
into table t2
fields terminated by X'0A'
(dummy)

-- tables:

SCOTT@orcl_11gR2> create table t1 (id integer, val varchar2(30))
  2  /

Table created.

SCOTT@orcl_11gR2> create table t2 (dummy varchar2(4000))
  2  /

Table created.

-- loads:
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=t1.ctl bad=t1.bad

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=t2.ctl

-- results:

SCOTT@orcl_11gR2> select * from t1
  2  /

        ID VAL
---------- ------------------------------
         1 toto
         2 titi

2 rows selected.

SCOTT@orcl_11gR2> select * from t2
  2  /

DUMMY
--------------------------------------------------------------------------------
I       am Michel

1 row selected.

Report message to a moderator

Previous Topic:	ORA: 31633 unable to create master table :SYS.SYS_EXPORT_SCHEMA_05"
Next Topic:	oradba in WIN-NT

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 22:14:46 CST 2025