1. connect to scott user and create below table
CREATE TABLE SFTP_FILE_NAMES (FILE_NAME VARCHAR2(100),PROCESSED_ON DATE);
2. assume /u04/ftp/abcdev/abc/scripts/file_list.20201102 contains below files
sftp> cd /test
sftp> ls -l 20201102*
-rwx------ 0 0 0 112255 Nov 2 01:04 20201102_orcl_result1.xml
-rwx------ 0 0 0 112255 Nov 2 05:09 20201102_orcl_result2.xml
-rwx------ 0 0 0 181669 Nov 2 09:03 20201102_orcl_result3.xml
-rwx------ 0 0 0 166850 Nov 2 13:05 20201102_orcl_result4.xml
-rwx------ 0 0 0 117372 Nov 2 17:01 20201102_orcl_result5.xml
-rwx------ 0 0 0 35570 Nov 2 22:01 20201102_orcl_result50.xml
-rwx------ 0 0 0 77009 Nov 2 21:03 20201102_orcl_result6.xml
sftp>
3. create load_files2db.sh to copy the files from unix to oracle database
#!/bin/bash
export ORACLE_HOME=/u02/test/abc/instantclient/instantclient_11_2
export TNS_ADMIN=/u02/test/abc/instantclient/instantclient_11_2/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME
export PATH=$PATH:$ORACLE_HOME/bin
DATETIME=`date +%d-%^h-%y`
CTLDIR=/u04/ftp/abcdev/ac/inactive_ctls
LOGDIR=/u04/ftp/abcdev/abc/inactive_logs
BADDIR=/u04/ftp/abcdev/abc/inactive_bads
ctlfile=$CTLDIR/$DATETIME".ctl"
logfile=$LOGDIR/$DATETIME".log"
badfile=$BADDIR/$DATETIME".bad"
FILENAMES=/u04/ftp/abcdev/abc/scripts/file_list.`date -d "1 days ago" -u +%Y%m%d`
echo "LOAD DATA" > $ctlfile
echo "INFILE *" >> $ctlfile
echo "BADFILE '$badfile'" >> $ctlfile
echo "APPEND" >> $ctlfile
echo "INTO TABLE SCOTT.SFTP_FILE_NAMES" >> $ctlfile
echo "FIELDS TERMINATED BY ','" >> $ctlfile
echo "(" >> $ctlfile
echo "FILE_NAME CHAR(100)," >> $ctlfile
echo "PROCESSED_ON DATE" >> $ctlfile
echo ")" >> $ctlfile
echo "BEGINDATA" >> $ctlfile
if [ $(grep -m 1 '\.xml' $FILENAMES|awk '{print $NF}') ];
then
for line in $(grep '\.xml' $FILENAMES|awk '{print $NF}')
do
echo `echo $line|tr -d '\r'`","$DATETIME >> $ctlfile
done
else
echo ""","$DATETIME >> $ctlfile
fi
$ORACLE_HOME/bin/sqlldr control=$ctlfile userid=SCOTT/tiger@ORCL log=$logfile
exit
4. run the script using the command - ./ load_files2db.sh or sh load_files2db.sh
#!/bin/bash
export ORACLE_HOME=/u02/test/abc/instantclient/instantclient_11_2
export TNS_ADMIN=/u02/test/abc/instantclient/instantclient_11_2/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME
export PATH=$PATH:$ORACLE_HOME/bin
DATETIME=`date +%d-%^h-%y`
CTLDIR=/u04/ftp/abcdev/ac/inactive_ctls
LOGDIR=/u04/ftp/abcdev/abc/inactive_logs
BADDIR=/u04/ftp/abcdev/abc/inactive_bads
ctlfile=$CTLDIR/$DATETIME".ctl"
logfile=$LOGDIR/$DATETIME".log"
badfile=$BADDIR/$DATETIME".bad"
FILENAMES=/u04/ftp/abcdev/abc/scripts/file_list.`date -d "1 days ago" -u +%Y%m%d`
echo "LOAD DATA" > $ctlfile
echo "INFILE *" >> $ctlfile
echo "BADFILE '$badfile'" >> $ctlfile
echo "APPEND" >> $ctlfile
echo "INTO TABLE SCOTT.SFTP_FILE_NAMES" >> $ctlfile
echo "FIELDS TERMINATED BY ','" >> $ctlfile
echo "(" >> $ctlfile
echo "FILE_NAME CHAR(100)," >> $ctlfile
echo "PROCESSED_ON DATE" >> $ctlfile
echo ")" >> $ctlfile
echo "BEGINDATA" >> $ctlfile
if [ $(grep -m 1 '\.xml' $FILENAMES|awk '{print $NF}') ];
then
for line in $(grep '\.xml' $FILENAMES|awk '{print $NF}')
do
echo `echo $line|tr -d '\r'`","$DATETIME >> $ctlfile
done
else
echo ""","$DATETIME >> $ctlfile
fi
$ORACLE_HOME/bin/sqlldr control=$ctlfile userid=SCOTT/tiger@ORCL log=$logfile
exit
4. run the script using the command - ./ load_files2db.sh or sh load_files2db.sh
5. connect to scott user and verify the results
SELECT * FROM SCOTT.SFTP_FILE_NAMES;
FILE_NAME PROCESSED_ON
----------------------------------------------------
20201102_orcl_result1.xml 03-NOV-20
20201102_orcl_result2.xml 03-NOV-20
20201102_orcl_result3.xml 03-NOV-20
20201102_orcl_result4.xml 03-NOV-20
20201102_orcl_result5.xml 03-NOV-20
20201102_orcl_result50.xml 03-NOV-20
20201102_orcl_result6.xml 03-NOV-20
No comments:
Post a Comment