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:

options (bindsize 400000)
load data
infile photo.bmp “fix 60999”
replace
concatenate 3
into table PER_IMAGES
(image_id constant 1, image raw(33532), parent_id constant 1253,
table_name constant “PER_PEOPLE_F”)

Some important facts now: Size of picture in this example is 33532 bytes. Bindsize must always be larger than the filesize. And, most importantly, only 64k can be loaded at one time, so we have divided the file size into equal portions < 64k. Here are the shell scripts, and ctl files i am using:

#mainscript.sh

#!/bin/sh
   echo enter file name
   read fname
     exec<$fname
      x=1
      value=“
while read line
     do
  value=`head -n $x $fname | tail -n 1`
         echo $x $value;
                sh newshscript.sh $x $value
                x=`expr $x + 1`
            done
   echo “****$value”;

What the above script called mainscript does is that it read the file that contain employee id and picture file name.For each employee, it than calls another shell script called newshscript.sh, and increments the id. This id we will be using in the ctl file.

#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