SQL Loader and the bad file 1

[petersJazz]

I use SQL Loader in a system where I load from file to table. In my tests I often gets some rows in the bad file, rows that have been rejected. But I dont get an error message of any kind.

Is it some parameter or any other way to find out why SQL Loader decides to reject a row?

 

[alexhu]

You should be configuring a log file as well as a bad file - are the errors not in there ?

Alex

 

[petersJazz]

hi,

I dont get a log file. Do you have to specify some parameter to get a log file? Does it log everything?

 

[ddrillich]

Here is a way to specify the log file:

log_file=logs/xxx.log

$ORACLE_HOME/bin/sqlldr control=$ctl_file userid=user/pssword@DB data=$data_file bad=$bad_file log=$log_file errors=10000

Regards,
Dan

 

[alexhu]

Example of log file with errors

SQL*Loader: Release 8.0.5.1.0 - Production on Fri Jan 23 16:22:38 2004

(c) Copyright 1998 Oracle Corporation. All rights reserved.

Control File: sku1.ctl
Data File: sku1.ctl
Bad File: sku1.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytes
Continuation: none specified
Path used: Conventional

Table STSTYL00, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ST00_PRODUCT_GROUP FIRST * , O(") CHARACTER
ST00_SKU NEXT * , O(") CHARACTER
ST00_STYLE_DESC NEXT * , O(") CHARACTER
ST00_SKU_TYPE NEXT * , O(") CHARACTER
ST00_UPC_BAR_CODE NEXT * , O(") CHARACTER
ST00_PRODUCT_SUBGROUP NEXT * , O(") CHARACTER
ST00_COUNTRY_OF_ORIGIN NEXT * , O(") CHARACTER
ST00_VENDOR_NBR NEXT * , O(") CHARACTER
ST00_PACK_QTY NEXT * , O(") CHARACTER
ST00_CRITICAL_DIMENSION_01 NEXT * , O(") CHARACTER
ST00_CRITICAL_DIMENSION_02 NEXT * , O(") CHARACTER
ST00_CRITICAL_DIMENSION_03 NEXT * , O(") CHARACTER
ST00_UNIT_WEIGHT NEXT * , O(") CHARACTER
ST00_UNIT_VOLUME NEXT * , O(") CHARACTER
ST00_PUTAWAY_TYPE NEXT * , O(") CHARACTER
ST00_CONVEYABLE NEXT * , O(") CHARACTER
ST00_SERIAL_NBR_REQD NEXT * , O(") CHARACTER

Record 1: Rejected - Error on table STSTYL00, column ST00_SKU_TYPE.
ORA-01401: inserted value too large for column


Table STSTYL00:
0 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Space allocated for bind array: 61404 bytes(14 rows)
Space allocated for memory besides bind array: 0 bytes

Total logical records skipped: 0
Total logical records read: 1
Total logical records rejected: 1
Total logical records discarded: 0

Run began on Fri Jan 23 16:22:38 2004
Run ended on Fri Jan 23 16:22:39 2004

Elapsed time was: 00:00:00.61
CPU time was: 00:00:00.04


Alex