OraFAQ Forum: Server Utilities » SQL Loader with WHEN and DISCARD

Home » RDBMS Server » Server Utilities » SQL Loader with WHEN and DISCARD (11.2.0.1.0 )

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

SQL Loader with WHEN and DISCARD [message #567238]

Tue, 25 September 2012 09:20

Milenna
Messages: 4
Registered: September 2012
Location: USA

Junior Member

Hello!

My data file contains records with RECORD_TYPE '01', '02', '03' and '04' in position (30:31)
I use sql loader to load this data into a table. I need to load ONLY rows with RECORD_TYPE ='04'. I have to output all the other rows into a file, so I decided to use DISCARD file.
Now, those with RECORD_TYPE ='4' have to be loaded into different columns depending on the value in position (267:268).

So, my ctl file should look something like:

WHEN (30:31) = '04'

into table MYDATA
WHEN (267:268) != 'O '

into table MYDATA
WHEN (267:268) = 'O '

and whatever is not '04' goes to discard file.

I tried to use

into table MYDATA
WHEN (30:31) = '04' and (267:268) != 'O '

into table MYDATA
WHEN (30:31) = '04' and (267:268) = 'O '

but I don't get the right result in terms of the discard file.

Is there any way to put together all these conditions?
Please advice

THanks!

Report message to a moderator

Re: SQL Loader with WHEN and DISCARD [message #567239 is a reply to message #567238]

Tue, 25 September 2012 09:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

If raw data file resides on DB Server, EXTERNAL TABLE would be easier alternative

Report message to a moderator

Re: SQL Loader with WHEN and DISCARD [message #567247 is a reply to message #567238]

Tue, 25 September 2012 10:13

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

Senior Member
Account Moderator

Quote:

but I don't get the right result in terms of the discard file

Maybe if you post us, CREATE TABLE statements, actual control file, a sample of data, and FINAL desired result for these data we can help.

Regards
Michel

[Updated on: Tue, 25 September 2012 10:14]

Report message to a moderator

Re: SQL Loader with WHEN and DISCARD [message #567332 is a reply to message #567247]

Wed, 26 September 2012 13:53

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

Senior Member

In the following demonstration, I used (10:11) and (17:18) instead of your positions to limit the length of test data for easier reading. It appears that the discard file is as expected. Please indicate what you expect instead.

-- data_file.dat:

Code: [Select all] [Show/ hide]

abcdefghi04lmnopqrstuvwxyz
abcdefghi04lmnopO stuvwxyz
abcdefghi01lmnopqrstuvwxyz
abcdefghi02lmnopO stuvwxyz

-- test.ctl:

Code: [Select all] [Show/ hide]

load data
infile data_file.dat
into table a_table
when (10:11) = '04' and (17:18) != 'O '
fields
(record_type position (10:11),
not_o position (17:18))
into table a_table
when (10:11) = '04' and (17:18) = 'O '
fields
(record_type position (10:11),
is_o position (17:18))

-- table, load, and resulting data in table:

Code: [Select all] [Show/ hide]

SCOTT@orcl_11gR2> create table a_table
  2    (record_type  number,
  3  	not_o	     varchar2(2),
  4  	is_o	     varchar2(2))
  5  /

Table created.

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log discard=test.dsc

SCOTT@orcl_11gR2> select * from a_table
  2  /

RECORD_TYPE NO IS
----------- -- --
          4 qr
          4    O

2 rows selected.

-- test.dsc:

Code: [Select all] [Show/ hide]

abcdefghi01lmnopqrstuvwxyz
abcdefghi02lmnopO stuvwxyz

Report message to a moderator

Previous Topic:	TOP N SQL in dbms_workload_repository,
Next Topic:	Slow expdp after upgrade

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 05 00:21:57 CDT 2025