Home » RDBMS Server » Server Utilities » Bad records into a Table using sql*loader (Oracle 10g)
Bad records into a Table using sql*loader [message #565564] Wed, 05 September 2012 04:55 Go to next message
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.
Re: Bad records into a Table using sql*loader [message #565566 is a reply to message #565564] Wed, 05 September 2012 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Bad records into a Table using sql*loader [message #565588 is a reply to message #565566] Wed, 05 September 2012 07:10 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Thanks.
Re: Bad records into a Table using sql*loader [message #565902 is a reply to message #565591] Fri, 07 September 2012 22:57 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7938
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.

Re: Bad records into a Table using sql*loader [message #565903 is a reply to message #565902] Fri, 07 September 2012 23:10 Go to previous message
Barbara Boehmer
Messages: 7938
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.

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


Current Time: Thu Jul 31 23:49:20 CDT 2014

Total time taken to generate the page: 0.10361 seconds