Using Images in Oracle HRMS

Hello Friends, Lets learn something in Oracle HRMS. Oracle Human resources has a table called PER_IMAGES. This table stores employee pictures in RAW format. If you want to mass upload the pictures of all employees in this table, you can have a hard time. we were facing a similar issue, and here is the approach i used to bulk upload (or mass upload) employee pictures to PER_IMAGES table in Oracle Human resources system using SQL Loader.

First, let’s see how the table looks:

SQL> desc per_images

Name                            Null?    Type
——————————- ——– —-
IMAGE_ID                        NOT NULL NUMBER(15)
IMAGE                           NOT NULL LONG RAW
PARENT_ID                       NOT NULL NUMBER(15)
TABLE_NAME                      NOT NULL VARCHAR2(30)
IMAGE_ID   – Next value in sequence
IMAGE      – Binary Image
PARENT_ID  – PERSON_ID from PER_PEOPLE_F
TABLE_NAME – PER_PEOPLE_F

To load a binary image in this table, we have to first create a control file, which will look something like this:

#mainscript.sh

#newshscript.sh

filesize=`ls -l $3 | awk ‘{print $5}’`
fixsize=`expr $filesize`
concno=1

if [ $filesize -gt 65536 ]; then
   echo “Filesize more than 6 k. breaking up…”
   concno=2
    if [ $filesize -gt 131072 ]; then
      concno=3      
    fi
    if [ $filesize -gt 196608 ]; then
      conco=4
    fi
    if [ $filesize -gt 262144 ]; then
      conco=5
    fi
   echo “Breaking into parts :”$concno
   fixsize=`expr $filesize / $concno`  
fi

echo $1 $2 $3 $4 “fixsize” $fixsize “filesize” $filesize “conc” $concno >> fileload.log

echo “Creating ctl file for dataload now”
echo “options (bindsize=400000)” >> loadpics.ctl
echo “load data” >> loadpics.ctl
echo “infile $3″ \”fix $fixsize\”>> loadpics.ctl
echo “append” >> loadpics.ctl
echo “concatenate “$concno >> loadpics.ctl
echo “into table PER_IMAGES” >> loadpics.ctl
echo “(image_id constant $1,” >> loadpics.ctl
echo “image raw($filesize),” >> loadpics.ctl
echo “parent_id constant $2,” >> loadpics.ctl
echo “table_name constant \”PER_PEOPLE_F\”)” >> loadpics.ctl
echo “” >> loadpics.ctl

sqlldr apps/<appspwd> control=loadpics.ctl log=picsload.log

mv loadpics.ctl loadpicslast.ctl

filesize=0
fixsize=0
concno=1

Above script is pretty simple, isn’t it? Well what we are doing is reading the file size, and creating
a ctl file with the information gathered. Then we are calling SQLLDR to upload the picture using this CTL file.

Hope this helps you! Thanks you …. Shivmohan Purohit

Article Courtesy: logicwala.com