Invoking SQL loader from UNIX for multiple files

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[@]}

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}

########################################################################################################

# 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 “Test file is loaded sucessfully ” >> $EMAILMESSAGE

echo ” ” >> $EMAILMESSAGE

echo “Some message you want to track ” >> $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.

Sureshkumar Kandiban