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.


If you have any clarifications please let me know.


Cool,
AntO.