Image Upload:
Step 1:
Create a Directory To Download the Files SYS User:
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’;
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
