Home » RDBMS Server » Server Utilities » SQL Loader with WHEN and DISCARD (11.2.0.1.0 )
SQL Loader with WHEN and DISCARD [message #567238] Tue, 25 September 2012 09:20 Go to next message
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!

Re: SQL Loader with WHEN and DISCARD [message #567239 is a reply to message #567238] Tue, 25 September 2012 09:23 Go to previous messageGo to next message
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
Re: SQL Loader with WHEN and DISCARD [message #567247 is a reply to message #567238] Tue, 25 September 2012 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous message
Barbara Boehmer
Messages: 9077
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:
abcdefghi04lmnopqrstuvwxyz
abcdefghi04lmnopO stuvwxyz
abcdefghi01lmnopqrstuvwxyz
abcdefghi02lmnopO stuvwxyz


-- test.ctl:
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:
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:
abcdefghi01lmnopqrstuvwxyz
abcdefghi02lmnopO stuvwxyz

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


Current Time: Thu Mar 28 05:09:44 CDT 2024