SQL Loader error -- Row not loaded due to data errors

Question

I am trying to load data using SQL Loader but getting below error.

Table to be loaded -

CREATE TABLE TEST_PIPE_SEP (FILE_NAME VARCHAR2(3000), KEY_COL VARCHAR2(4000), DESCR VARCHAR2(100), RUN_DATE DATE );

I am creating dynamic CTL file using Shell and SED command so that I can get the current processing file and pass it as default value for the FILE_NAME column.

#!/bin/ksh  

echo "starting script"   
#cd data  

for i in data/Key_Mismatch_Output_UAT*.csv  
do   

  #echo "$i"  

  filename=`basename "${i}"`   
  echo "$filename"  

  #sed '1d' "$i" >> test.csv   
  sed -e "s/#file_name#/file_name \"${filename}\",/g" test.ctl > new_test_3.ctl  

 sqlldr ERRORS=100000 userid=$USER_CRED control=new_test_3.ctl data=data/$filename silent=all log=data/$filename".log" bad=data/$filename.bad skip=1  

wait   
done   
echo "ending script"

The format of test.ctl is as below which I am editing on run time using SED to pass the current processing file name --

LOAD DATA        
CHARACTERSET WE8ISO8859P1        
APPEND        
INTO TABLE TEST_PIPE_SEP        
FIELDS TERMINATED BY ','        
(      
#file_name#     
key_col "trim(:key_col)",  
descr   "trim(:descr)",  
run_date "SYSDATE"  
)

The new file which will get created after the SED commend is as below --

LOAD DATA  
CHARACTERSET WE8ISO8859P1  
APPEND  
INTO TABLE TEST_PIPE_SEP  
FIELDS TERMINATED BY ','  
(  
file_name  "Key_Mismatch_Output_UAT.csv.20170804070448.1_LIVE.csv.20170804070448.2_20170804070448.csv",  
key_col "trim(:key_col)",  
descr   "trim(:descr)",  
run_date "SYSDATE"  
)

The CSV file and its contains --

Key Columns,Description  

"C"|"G000053929"|"ABCD"|"G000053929"|""|""|"TTA"|""|""|""|""|""|"DRR"|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|"",Not Present in file1   
"C"|"G000053621"|"HGHQ"|"G000053621"|""|""|"CBI"|""|""|""|""|""|"DRR"|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|"",Not Present in file2  
"C"|"G000053929"|"HGHQ"|"G000053929"|""|""|"TTA"|""|""|""|""|""|"DRR"|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|"",Not Present in file2

and I am getting below error and not able to find the route cause tried changing the CTL file as much as possible but not able to load data

Table TEST_PIPE_SEP, loaded from every logical record.  
Insert option in effect for this table: APPEND  
　  
   Column Name                  Position   Len  Term Encl Datatype        
------------------------------ ---------- ----- ---- ---- ---------------------  
FILE_NAME                           FIRST     *   ,       CHARACTER              
    SQL string for column : "TRIM('Key_20170804070448.csv')"  
KEY_COL                              NEXT     *   ,       CHARACTER              
DESCR                                NEXT     *   ,       CHARACTER              
RUN_DATE                             NEXT     *   ,       CHARACTER              
    SQL string for column : "SYSDATE"  
　  

Table TEST_PIPE_SEP:  

  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.

Please ask me if more information is required.

I am using

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

etsa · Accepted Answer · 2017-09-05 09:11:24Z

2

As far as I know to load a constant value with sqlldr you should use the word CONSTANT. So in your case you should substitute

...
FIELDS TERMINATED BY ','  
(file_name "Key_Mismatch_Output_UAT.csv.20170804070448.1_LIVE.csv.20170804070448.2_20170804070448.csv",  
...

with

...
FIELDS TERMINATED BY ','  
(file_name CONSTANT "Key_Mismatch_Output_UAT.csv.20170804070448.1_LIVE.csv.20170804070448.2_20170804070448.csv",  
...

Pls let me know it works for you (in the meantime I try to reproduce your table and your script, to do more test if necessary)

Addendum

I made a test using your data. I confirm you should use CONSTANT keyword as suggested above.

Moreover, I think you should add TRAILING NULLCOLS option as you want to load run_date with SYSDATE. So you should change

...
INTO TABLE TEST_PIPE_SEP  
FIELDS TERMINATED BY ','  
(  
...

with

...
INTO TABLE TEST_PIPE_SEP  
FIELDS TERMINATED BY ','
TRAILING NULLCOLS   
(  
...

CTL used to make my tests:

LOAD DATA  
CHARACTERSET WE8ISO8859P1  
APPEND  
INTO TABLE TEST_PIPE_SEP  
FIELDS TERMINATED BY ','
TRAILING NULLCOLS  
(  
file_name  CONSTANT "Key_Mismatch_Output_UAT.csv.20170804070448.1_LIVE.csv.20170804070448.2_20170804070448.csv",  
key_col "trim(:key_col)",  
descr   "trim(:descr)",  
run_date "SYSDATE"  
)

Log file SQLLDR: (I loaded 4 rows because I added another sample rows at your file (simply A,B).

SQL*Loader: Release 11.2.0.2.0 - Production on Mon Sep 4 15:01:12 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Control File:   TEST2.CTL
Character Set WE8ISO8859P1 specified for all input.
Data File:      test2.csv
  Bad File:     test2.bad
  Discard File:  none specified
 (Allow all discards)
Number to load: ALL
Number to skip: 1
Errors allowed: 100000
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional
Table TEST_PIPE_SEP, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
FILE_NAME                                                 CONSTANT
    Value is 'Key_Mismatch_Output_UAT.csv.20170804070448.1_LIVE.csv.20170804070448.2_20170804070448.csv'
KEY_COL                             FIRST     *   ,       CHARACTER            
    SQL string for column : "trim(:key_col)"
DESCR                                NEXT     *   ,       CHARACTER            
    SQL string for column : "trim(:descr)"
RUN_DATE                             NEXT     *   ,       CHARACTER            
    SQL string for column : "SYSDATE"

Table TEST_PIPE_SEP:
  4 Rows successfully loaded.
  0 Rows 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:                  55296 bytes(64 rows)
Read   buffer bytes: 1048576

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

Run began on Mon Sep 04 15:01:12 2017
Run ended on Mon Sep 04 15:01:12 2017

Elapsed time was:     00:00:00.09
CPU time was:         00:00:00.00

Sample data:

Key Columns,Description  
A,B
"C"|"G000053929"|"ABCD"|"G000053929"|""|""|"TTA"|""|""|""|""|""|"DRR"|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|"",Not Present in file1  
"C"|"G000053621"|"HGHQ"|"G000053621"|""|""|"CBI"|""|""|""|""|""|"DRR"|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|"",Not Present in file2  
"C"|"G000053929"|"HGHQ"|"G000053929"|""|""|"TTA"|""|""|""|""|""|"DRR"|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|"",Not Present in file2

edited Sep 5, 2017 at 9:11

answered Sep 4, 2017 at 12:41

etsa

5,0501 gold badge9 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

mradul Over a year ago

Thanks for reply etsa. I tried using CONSTANT keyword but it gives me same error. Did you execute the scenario successfully if yes could you please give the CTL file example which you use and yes I amend the CTL structure to add TRAILING NULLCOLS.

etsa Over a year ago

@mradul Yes I executed succesfully. I added CTL to my answer

etsa Over a year ago

@mradul added log ctl and sample data (your plus another row)

etsa Over a year ago

@mradul Tip: start using a fixed CTL, until it works. Then do your tries to change CTL with your script.

mradul Over a year ago

Thanks etsa for your help. I am now editing file itself and adding the file_name column as field value in CSV file using SED and it is working fine now. I will give a try with your CTL and let you know if its working on my machine !

|

Collectives™ on Stack Overflow

SQL Loader error -- Row not loaded due to data errors

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related