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:
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”)
#mainscript.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”;
#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
What about when you doing the opposite bulk download of images.???
LikeLike