Overview
This document says about how to load multiple data files with csv format using sql loader from UNIX and also explains about control file and database connection through Linux.
Technologies and Tools Used
The following technologies has been used to achieve the expected output.
- Oracle PLSQL
- Unix
Use Case
The files will be placed in a particular directory, from the using shell script will read the data and manipulate into the oracle table. For this it requires control file to a text file that contains data definition language (DDL) instructions and error file to log the error details.
Inside the shell script will connect the database and will include the logics and checks to validate the data and insert the data into the tables. The logic which includes invoking the multiple data files and store in the single csv file and load it into the tables.
Architecture
We can see examples of multiple files which it will invoking through sql loader using Unix.
Below are some sample files
# test_file1.csv COL1, COL2 1,"TEST1" 2,"TEST1"
# test_file2.csv COL1, COL2 1,"TEST2" 2,"TEST2"
# test_file3.csv
COL1, COL2
1,”TEST3″
2,”TEST3″
Table Structure:
CREATE TABLE TEST_STG
(
COL1 NUMBER (10),
COL2 VARCHAR2 (30)
);
Control File:
OPTIONS (skip=1)
LOAD DATA
TRUNCATE
INTO TABLE TEST_STG
Fields terminated by “,”
Optionally enclosed by ‘”‘
TRAILING NULLCOLS
(
COL1,
COL2
)
Below example shows that how to connect database and invoking the multiple files and load the data into the oracle table.
Shell script:
#!/bin/sh
#########################################################################
## Script Name : Sample_script.sh
## Comments : Invoking SQL loader from Unix for multiple files
##########################################################################
###########################################################################################
# Export Oracle paths and take a variable and pass the username and password
# of Oracle database
###########################################################################################
export ORACLE_BASE=/opt/app/oracle
export ORACLE_SID=*****.db.*****.com
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_PATH=$ORACLE_HOME/bin
export PATH=$ORACLE_HOME/bin:$PATH
ORACLE_ACCESS=sloba@$ORACLE_SID/*********
###########################################################################################
# Get all the files which are not like ‘%_loaded’
# For example: if the file name is test_file_loaded.txt then this will not be taken
# as this file is already loaded.
###########################################################################################
FILES=`ls /opt/app/test_*.txt|grep -v “_loaded.txt”`;
###########################################################################################
# Get all the files which are not like ‘%_loaded’ and start the Oracle SQL Loader to
# To load the data
###########################################################################################
for file in ${FILES[@]}
do
filename=`expr substr “$file” 47 31`
echo $filename
###########################################################################################
# Get the date from the file name, so that we can update it on the table
###########################################################################################
filedate=`expr substr “$file” 66 8`
echo $filedate
###########################################################################################
# SQL LOADER command to load the file from
###########################################################################################
sqlldr $ORACLE_ACCESS control=/opt/app/test_c.ctl log=/opt/app/test_loaded.log data=$filename skip=1
export ORACLE_BASE=/opt/app/oracle
export ORACLE_SID=*****.db.*****.com
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_PATH=$ORACLE_HOME/bin
export PATH=$ORACLE_HOME/bin:$PATH
ORACLE_ACCESS=sloba@$ORACLE_SID/*********
LOG_FILE_PATH=/opt/app
LOG_FILE=/update.log
###########################################################################################
# Prcedure to insert from stage table to main with
# some validation which I have used it but you can ignore this too
###########################################################################################
BEGIN
INSERT_MAIN();
END;
/
exit 0
RUN_SQL`
echo $SQL
if [[ $? = 1 ]]; then
print `date “+%Y-%m-%d-%H.%M.%S”` “**Error running update sql. review above messages.” >> ${LOG_FILE_PATH}${LOG_FILE}
fi
########################################################################################################
# Now rename the file with sufix “_loaded.txt”
# So that when we schedule this programe then only the files will be taken which are not loaded earlier
#########################################################################################################
NAME=`expr substr “$file” 47 27`
EXT=${filename#*.}
LOAD=”loaded”
NEWFILE=${NAME}_${LOAD}.${EXT}
mv $file $NEWFILE
echo $NEWFILE
done
If you want to send an email notification then you can also add the below piece of code to send email to the required persons for intimating the file are been loaded:
########################################################################################################
# Email notification
#########################################################################################################
SUBJECT=”TEST file loaded”
FROM=”Your Team”
DESCRIPTION=”THIS IS AN AUTO-GENERATED MESSAGE”
EMAIL=”you@doyen.com,other1@doyen.com,other2@doyen.com,…..othern@doyen.com”
EMAILMESSAGE=/tmp/emailmessage
echo “From: $FROM” > $EMAILMESSAGE
echo “To: $EMAIL” >> $EMAILMESSAGE
echo “Reply-To: you@xyz.com” >> $EMAILMESSAGE
echo “Subject: $SUBJECT ” >> $EMAILMESSAGE
echo “************************************************************************” >> $EMAILMESSAGE
echo “$DESCRIPTION : DO NOT REPLY TO THIS EMAIL MESSAGE. ” >> $EMAILMESSAGE
echo “************************************************************************” >> $EMAILMESSAGE
echo ” ” >> $EMAILMESSAGE
echo ” ” >> $EMAILMESSAGE
echo “Test file is loaded sucessfully ” >> $EMAILMESSAGE
echo ” ” >> $EMAILMESSAGE
echo “Some message you want to track ” >> $EMAILMESSAGE
echo ” ” >> $EMAILMESSAGE
echo ” ” >> $EMAILMESSAGE
echo “Thanks, ” >> $EMAILMESSAGE
echo “XXXX)” >> $EMAILMESSAGE
cat $EMAILMESSAGE | /usr/sbin/sendmail -t
Conclusion
The above examples shows how the data can be populated into table by Invoking SQL loader from UNIX for multiple files. You can modify the codes as per your requirements.