Tuesday, November 3, 2020

copy file names from UNIX server to Oracle Database

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

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

Delete Row Button to all records of Interactive Report in Oracle Apex

 1. add 'Delete' Del column to Report Query 2. Set the Following Properties for the DEL Column Type: Link Heading: Delete Targ...