Uploading Images using sql * loader

 Uploading Images using sql loader

Dears,


Here i am going to explain about how to import images using sqlloader. 
Let me take the HRMS employee images load into per_images.


SQL> desc per_images

Name                            Null?    Type
——————————- ——– —-
IMAGE_ID                        NOT NULL NUMBER(15)
IMAGE                           NOT NULL BLOB
PARENT_ID                       NOT NULL NUMBER(15)
TABLE_NAME                      NOT NULL VARCHAR2(30)
--===============================================

Assume, we are importing image for the employee number 20063. image name is 20063.jpg

IMAGE_ID   – Next value in sequence(per_images_s.nextval)
IMAGE      – Binary Image(20063.jpg)
PARENT_ID  – PERSON_ID (select person_id from PER_ALL_PEOPLE_F where employee_number = 20063 and sysdate between effective_start_date and effective_end_date)
TABLE_NAME – PER_PEOPLE_F (default)

First let us make the data file for this employee 20063. 
-------------------------------------------

SELECT  PER_IMAGES_S.nextval
       || ','
       || person_id
       || ','

       || 'd:\images\'

       || employee_number
       || '.jpg'
  FROM per_all_people_f
 WHERE  EFFECTIVE_END_DATE >= sysdate-1
 AND employee_number IN ('20063')


Now the data file look likes:-
6482,101,PER_ALL_PEOPLE_F,d:\images\20063.jpg


Creating Control file for our data file:-
load data

 infile *
 replace
 into table per_images
 fields terminated by ","
 ( IMAGE_ID,PARENT_ID,TABLE_NAME,IMAGE LOBFILE (TABLE_NAME) TERMINATED BY EOF  )
begindata
3061,101,d:\images\20063.jpg






Here, I have used the TABLE_NAME column for storing my image file path. After successful completion of the sql loader execution update the column value into PER_PEOPLE_F.


client machine they have already installed sqlloader. So i refer the local machine path. Suppose if you are accessing the server through putty or some other tool to access server. Use server folder location instead.