Mass Employee Image Upload in Oracle HRMS

There is no Standard API to do mass upload of images into the system. Following is the work around to achieve the same.

Below steps are followed from:
http://vjcoracleapps.blogspot.ae/2013/07/oracle-hrms-api-photo-upload-api.html


Step 1: 
Connect to Database Using SYS / SYSTEM.
SELECT value FROM v$parameter WHERE UPPER(name) = 'UTL_FILE_DIR';

Step 2:
Connect to Database Server & Create Directory "xxempimages" in one of the 'UTL_FILE_DIR'
in this case "/usr/tmp/".

Grant READ, WRITE & EXECUTE permissions to the Directory "xxempimages".

Move all the Employee Images into this Directory.
(Image names is assumed as <employee_number>.jpg

Step 3:
Connect to Database Using SYS / SYSTEM and Execute the below SQL Statements to Create Directory inside Database also to Grant permissions to APPS Schema.

CREATE DIRECTORY XXCUST_EMP_IMAGES AS '/usr/tmp/xxempimages';
GRANT READ ON DIRECTORY XXCUST_EMP_IMAGES TO APPS;
GRANT WRITE ON DIRECTORY XXCUST_EMP_IMAGES TO APPS;

Step 4:
Connect to Database Using APPS and Execute the below PLSQL to Upload the Employee Images into PER_IMAGESTable.

CREATE OR REPLACE PROCEDURE XXMUD_IMAGE_UPLOAD

AS

   CURSOR cur_per

   IS

   SELECT *

     FROM per_all_people_f

    WHERE TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date

      AND current_employee_flag = 'Y';

   V_IMAGE_NAME    VARCHAR2 (240);

   V_DSTN_FILE     BLOB;

   V_SRC_FILE      BFILE;

   V_FILE_EXISTS   INTEGER := 0;

   V_AMT           INTEGER ;

  

BEGIN

   FOR i IN cur_per

   LOOP

      BEGIN

         V_IMAGE_NAME   := TO_CHAR (i.employee_number) || '.jpg';

         V_SRC_FILE     := BFILENAME ('XXCUST_EMP_IMAGES', V_IMAGE_NAME);

         V_FILE_EXISTS  := DBMS_LOB.FILEEXISTS (V_SRC_FILE);        

         V_AMT          := DBMS_LOB.GETLENGTH(V_SRC_FILE) ;

         IF V_FILE_EXISTS = 1

         THEN

            DBMS_LOB.CREATETEMPORARY (V_DSTN_FILE, TRUE, DBMS_LOB.SESSION);

            DBMS_LOB.FILEOPEN (V_SRC_FILE, DBMS_LOB.FILE_READONLY);

            DBMS_LOB.LOADFROMFILE (V_DSTN_FILE,V_SRC_FILE,V_AMT ,1,1);

           

            COMMIT;

            DBMS_LOB.FILECLOSE (V_SRC_FILE);

            INSERT INTO APPS.PER_IMAGES (IMAGE_ID,

                                         PARENT_ID,

                                         TABLE_NAME,

                                         IMAGE)

                                 VALUES (PER_IMAGES_S.NEXTVAL,

                                         I.PERSON_ID,

                                         'PER_PEOPLE_F',

                                         V_DSTN_FILE);

         END IF;

        

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line ('Failed for Employee : '|| i.employee_number);

      END;

   END LOOP;

   COMMIT;

END;

/