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

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

Answers (1)

Related Questions