Hi,
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