I want to import a .csv file into an oracle database table below, but its not working and the log file is empty without no output.
my shell script is below:
and also the logfile is always empty
below is a sample of the .csv file
SendingFri,LoggingTime,RenewalNumber,TransactionType,Status,Duration
22997186945,2023-07-25-00:05:56.301,23900032093333,Payment,SUCCESS,null
22951547347,2023-07-25-00:04:12.794,23900336289912,Payment,SUCCESS,null
#!/bin/bash
# Oracle Database Connection Details
USERNAME='abc'
PASSWORD="abc"
HOST="myip"
PORT="1521"
SERVICE_NAME="abc"
# CSV File Details
CSV_FILE="/home/hdegboevi/modified5.csv"
TABLE_NAME="myCANALINFORMATION"
# Control File
CONTROL_FILE="my_control_file.ctl"
# Create a control file
cat <<EOF > $CONTROL_FILE
LOAD DATA
INFILE '$CSV_FILE'
APPEND INTO TABLE $TABLE_NAME
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
SENDINGFRI,
LOGGINGTIME,
RENEWALNUMBER,
TRANSACTIONTYPE,
STATUS,
DURATON
)
EOF
# Run SQL*Loader
sqlldr $USERNAME/$PASSWORD@$HOST:$PORT/$SERVICE_NAME control=$CONTROL_FILE log=/home/hdegboevi/sqlldr.log /home/hdegboevi
# Check the log for errors
if grep "ORA-" sqlldr.log; then
echo "Error occurred during data load. Please check sqlldr.log for details."
else
echo "Data loaded successfully."
fi
You should have posted target table description; without it, we have to guess datatypes. My lucky guess (according to sample data):
SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
SENDINGFRI NUMBER
LOGGINGTIME TIMESTAMP(6)
RENEWALNUMBER NUMBER
TRANSACTIONTYPE VARCHAR2(20)
STATUS VARCHAR2(10)
DURATION VARCHAR2(20)
SQL>
which is probably wrong; duration column sounds as if you'd want to put e.g. a number in it (something took 2 seconds to complete), but sample data suggests that you're inserting a string into it (null, in this context, is a string).
With a control file you wrote, result is (in my database)
Record 1: Rejected - Error on table TEST, column LOGGINGTIME.
ORA-01843: not a valid month
Control file, fixed:
LOAD DATA
INFILE *
REPLACE
INTO TABLE test
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
SENDINGFRI,
LOGGINGTIME "to_timestamp(:loggingtime, 'yyyy-mm-dd-hh24:mi:ss.ff')",
RENEWALNUMBER,
TRANSACTIONTYPE,
STATUS,
DURATION
)
begindata
22997186945,2023-07-25-00:05:56.301,23900032093333,Payment,SUCCESS,null
22951547347,2023-07-25-00:04:12.794,23900336289912,Payment,SUCCESS,null
Loading session:
SQL> $sqlldr scott/tiger@orcl control=test49.ctl log=test49.log
SQL*Loader: Release 18.0.0.0.0 - Production on Čet Kol 17 14:23:09 2023
Version 18.5.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 1
Commit point reached - logical record count 2
Table TEST:
2 Rows successfully loaded.
Check the log file:
test49.log
for more information about the load.
Result:
SQL> select * from test;
SENDINGFRI LOGGINGTIME RENEWALNUMBER TRANSACTIONTYPE STATUS DURATION
------------- ------------------------- ---------------- -------------------- ---------- --------------------
22997186945 25.07.23 00:05:56,301000 23900032093333 Payment SUCCESS null
22951547347 25.07.23 00:04:12,794000 23900336289912 Payment SUCCESS null
SQL>
OK; data is here.
I'd suggest you to first make that part of job correctly (i.e. loading itself), and then create a shell script. I can't assist about that, I don't use any kind of Unix operating systems.
[EDIT, after you posted table description]
Aha; so all columns are varchar2. Even simpler.
SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
SENDINGFRI VARCHAR2(30)
LOGGINGTIME VARCHAR2(30)
RENEWALNUMBER VARCHAR2(30)
TRANSACTIONTYPE VARCHAR2(30)
STATUS VARCHAR2(30)
DURATON VARCHAR2(30)
SQL>
(I thought it was a typo, "duraton" vs. "duration"; looks like it really is "duraton", whatever that might be).
Control file is trivial:
LOAD DATA
INFILE *
REPLACE
INTO TABLE test
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
SENDINGFRI,
LOGGINGTIME,
RENEWALNUMBER,
TRANSACTIONTYPE,
STATUS,
DURATON
)
begindata
22997186945,2023-07-25-00:05:56.301,23900032093333,Payment,SUCCESS,null
22951547347,2023-07-25-00:04:12.794,23900336289912,Payment,SUCCESS,null
Loading session:
SQL> $sqlldr scott/tiger@orcl control=test49a.ctl log=test49a.log
SQL*Loader: Release 18.0.0.0.0 - Production on Čet Kol 17 14:48:15 2023
Version 18.5.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 1
Commit point reached - logical record count 2
Table TEST:
2 Rows successfully loaded.
Check the log file:
test49a.log
for more information about the load.
Result:
SQL> select * from test;
SENDINGFRI LOGGINGTIME RENEWALNUMBER TRANSACTIONTYPE STATUS DURATON
------------ ------------------------- --------------- --------------- ---------- ----------
22997186945 2023-07-25-00:05:56.301 23900032093333 Payment SUCCESS null
22951547347 2023-07-25-00:04:12.794 23900336289912 Payment SUCCESS null
SQL>