SQLLDR

SQLLDR - retrieving file name from profile option.

The below script will run the sqlldr utility from shell script which up loads the data into a custom table. The directory name will be retrieved from a profile option.

1. name the below shell script as xxabc.prog
#/bin/ksh
L_FILE_DIR_NAME=`sqlplus -s $1 <<EOF
      set echo off
   set heading off
   set feedback off
   clear breaks
   clear columns
   select  fnd_profile.value('XX_PROFILE_NAME') from dual;
EXIT;
EOF`
L_FILE_DIR_NAME=`echo $L_FILE_DIR_NAME |xargs`
echo "FILE PATH IS ==>  $L_FILE_DIR_NAME"
if [[ -a $L_FILE_DIR_NAME ]]
then
 echo "File $L_FILE_DIR_NAME EXIST"
else
 echo "File $L_FILE_DIR_NAME NOT EXIST"
fi
if [ $? -eq 0 ]
then
    sqlldr userid=$1 control=$XBOL_TOP/bin/xx_abc.ctl data=$L_FILE_DIR_NAME skip=1
    echo "Please check $XBOL_TOP/bin/xx_abc.log for more details"
fi

create a soft link

    ln -s $FND_TOP/bin/fndcpesr xxabc

Option to import > 255 char   in a field

    Use the CHAR(20000) to import more than 255 characters into a filed using SQL*LOADER as below.

    column_name     CHAR(20000)