Sunday, June 15, 2014

Code for Generic SQL Loader program in Oracle Apps

Save the below code as .prog
Make sure to create a symbolic link from your script to $FND_TOP/bin/fndcpesr
Ex: ln -s $FND_TOP/bin/fndcpesr <<File_Name>>


#--------------------------------------
# GETTING SYSTEM PARAMETERS
#--------------------------------------
ORA_LOGON=`echo $1`
USER_ID=`echo $2`
AOL_USER=`echo $3`
FCP_REQID=`echo $4`
CTRL_FILE_NAME=`echo $5`
DATA_FILE_PATH=`echo $6`
ARCHIVE_FILE_PATH=`echo $7`
DATA_FILE_NAME=`echo $8`

#--------------------------------------
# Echoing the log information
#--------------------------------------
echo "-----------------------------------------------------------"
echo '$ORA_LOGON         : '$ORA_LOGON
echo 'User ID              : '$USER_ID
echo 'User Name is     : '$AOL_USER
echo 'Conc Request ID      : '$FCP_REQID
echo 'Control File Name  : '$CTRL_FILE_NAME
echo 'Data File Path     : '$DATA_FILE_PATH
echo 'Archive File Path  : '$ARCHIVE_FILE_PATH
echo 'Data File Name     : '$DATA_FILE_NAME
echo "-----------------------------------------------------------"
echo
echo
echo

cd $CUSTOM_TOP/bin

echo "-----------------------------------------------------------"
echo Validating Input Parameters
echo "-----------------------------------------------------------"
echo
if [ ! -d "$DATA_FILE_PATH" ]; then
  echo  'Data Path Does not exist'
  exit 1
else
  echo  'Data Path Validated'
fi

if [ ! -d "$ARCHIVE_FILE_PATH" ]; then
  echo  'Archive Path Does not exist'
  exit 1
else
  echo  'Archive Directory Validated'
fi

if [ ! -f "$CTRL_FILE_NAME" ];
then
   echo "Control File Missing"
   exit 1
else
  echo  'Control File Validated'
fi

if [ ! -f "$DATA_FILE_PATH/$DATA_FILE_NAME" ];
then
   echo "Data File Missing"
   exit 1
else
  echo  'Data File Validated'
fi

echo "Executing the SQL Loader Process..."

sqlldr userid=$ORA_LOGON control=${CTRL_FILE_NAME} data=${DATA_FILE_PATH}/${DATA_FILE_NAME} log=${DATA_FILE_PATH}/${DATA_FILE_NAME}.log bad=${DATA_FILE_PATH}/${DATA_FILE_NAME}.bad errors=100000
retcode=`echo $?`
case "$retcode" in
    0) echo "SQL*Loader execution successful" ;;
    1) echo "SQL*Loader execution exited with EX_FAIL, see logfile" ;;
    2) echo "SQL*Loader execution exited with EX_WARN, see logfile" ;;
    3) echo "SQL*Loader execution encountered a fatal error" ;;
    *) echo "unknown return code";;
esac


echo "****************************************************************************"
if [ "$retcode" -eq 1  ] || [ "$retcode" -eq 3 ]; then   
    exit 1
fi

exit 0


Reactions:

0 comments:

Post a Comment