Conditional import - how to discard records?

0
votes

I want to import a csv file using SQLLDR, but I only want specific records. I have solved this with "WHEN record_type = 1" in my control file.

This works but the log file is getting flooded by "Record xxx: Discarded - failed all WHEN clauses." The input files contain millions of records but only a few percent satisfy the condition, so I end up with a log file with the same size as the input file :)

Am I doing this incorrectly? Is there another way to discard/filter records when using SQLLDR?

Example Data:

record_type;a;b;c
24;a1;b1;c1
17;a2;b2;c2
22;an;bn;cn
1;a1;b1;c1
1;a2;b2;c2
1;an;bn;cn

Control file

load data
truncate
into table my_table_t
WHEN record_type = 1
(...
)
3
oraclesql-loader
You could try using EXTERNAL TABLES rather than SQL*Loader. Or alternatively, if the target rows are only a small fraction of the file, you could use a UNIX pattern matching command to build a small file containing just your target records. - Christian Palmer
@ChristianPalmer, the target rows are only a fraction of the file so I investigate the unix filtering as well. Thanks for the idea. - Ronnis
something like this UNIX> grep -e "^1;" test_file.dat > output_file.dat - Christian Palmer

3 Answers

1
votes

What you do is right IMO.

SQL*Loader is logging to the finest level of the loading details for you. You can opt out from few of the things.

Yo can disable the DISCARD records logging by adding SILENT=(DISCARDS) to your SQL*Loader

You can refer the DOC for further details.

0
votes

If you just want to get rid of the log you can send these log to /dev/null if you using Linux/Unix and NUL on Windows.

Example

Data File.

[oracle@ora12c Desktop]$ cat sample.txt 
record_type;a;b;c
24;a1;b1;c1
17;a2;b2;c2
22;an;bn;cn
1;a1;b1;c1
1;a2;b2;c2
1;an;bn;cn

Control file.

[oracle@ora12c Desktop]$ cat control.ctl 
load data
 infile 'sample.txt'
 insert
 into table table_1  when record_type = '1'
 fields terminated by ";"
 (record_type, a, b, c)

Lets try to load records.

[oracle@ora12c Desktop]$ sqlldr jay/password@orapdb1 control=control.ctl data=sample.txt log=/dev/null

SQL*Loader: Release 12.1.0.2.0 - Production on Fri Feb 10 16:05:10 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 7

Table TABLE_1:
  3 Rows successfully loaded.

Check the log file:
  /dev/null
for more information about the load.

There was no log file.

Now we got the only selected records.

SQL> select * from table_1;

RECORD_TYPE A            B            C
----------- -------------------- -------------------- --------------------
      1 a1           b1           c1
      1 a2           b2           c2
      1 an           bn           cn
0
votes

Using the external table, you can then use simple SQL to load your table...

insert into my_table_t( record_type, a, b, c )
select record_type, a, b, c 
from my_external_table
where record_type != 1