Image Upload -Employee

Image Upload:

Step 1:

Create a Directory To Download the Files SYS User:

CREATE OR REPLACE DIRECTORY FND_BLOBS AS '/usr/tmp/FND_LOBS[GMM1] '

Grant Access to Apps User

GRANT READ, WRITE ON DIRECTORY FND_BLOBS TO APPS;

Check the Privilege with below command,

SELECT * FROM dba_tab_privs WHERE table_name= ‘FND_BLOBS’;

Run the below Query in Apps Schema[GMM2] ,

CREATE OR REPLACE PROCEDURE XX_IMAGE_UPLOAD

AS

   CURSOR CUR_PER

   IS

      SELECT * FROM PER_ALL_PEOPLE_F  where  employee_number = 200290

      and trunc(sysdate) between effective_start_date and effective_end_date

      and business_group_id =1274;

   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 := '200290'||’.jpg’;--Image Name

         V_SRC_FILE := BFILENAME (‘XXHR_FND_BLOBS’, V_IMAGE_NAME);--File Loaction and 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);

                          DBMS_OUTPUT.put_line (

               'Uploaded the  image for the employee code : '

               || I.employee_number);

         END IF;

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line (

               'Unable to upload image for the employee code : '

               || I.employee_number||sqlerrm);

      END;

   END LOOP;

   COMMIT;

END;

/

declare

begin

XX_IMAGE_UPLOAD;

end;

SET SERVEROUTPUT ON

---

 [GMM1]Directory Path in Apps/DB Tier

 [GMM2]