How to download attachment stored in fnd_lobs table
- -
Open employee’s record and click on attachment
icon.
- Attach any new document under category as show below.
Now you can see the attachment symbol.
Attachment will be stored in fnd_lobs table.
below are queries to identify the attachment record.
select * from fnd_attached_documents
where entity_name='PER_PEOPLE_F'
and pk1_value=33579
order by creation_date desc;
select * from fnd_documents
where document_id=2349427;
select * from fnd_lobs
where file_id=2990785;
Now if you download the attachment through pl/sql procedure.
prerequisite
- server directory - where you want to download the file - generally database tier
- read/write access on the that directory.
- proper privileges on UTL_file packages
you can below procedure to download above attachment.
CREATE OR REPLACE PROCEDURE APPS.xx_download_attachment
AS
CURSOR cur_new_attmt
IS
SELECT fl.file_id, fl.file_name, fl.file_data,
DBMS_LOB.getlength (fl.file_data) file_length
FROM fnd_lobs fl
WHERE fl.file_id =2990785;
v_start NUMBER DEFAULT 1;
v_bytelen NUMBER DEFAULT 32000;
v_len_copy NUMBER;
v_raw_var RAW (32000);
v_output UTL_FILE.file_type;
v_inv_file_name VARCHAR2 (100);
v_position NUMBER;
v_first_rec BOOLEAN DEFAULT TRUE;
BEGIN
v_position := 10;
FOR rec_inv IN cur_new_attmt
LOOP
v_inv_file_name := NULL;
v_inv_file_name := rec_inv.file_name;
v_position := 20;
-- define output directory AND OPEN THE file IN WRITE BYTE MODE
v_output := UTL_FILE.fopen ('ODPDIR', v_inv_file_name, 'wb', 32760);
v_position := 30;
-- maximum size OF buffer parameter IS 32767 BEFORE
-- which you have TO flush your buffer
IF rec_inv.file_length < 32760
THEN
UTL_FILE.put_raw (v_output, rec_inv.file_data);
UTL_FILE.fflush (v_output);
ELSE
v_position := 40;
v_start := 1;
v_bytelen := 32000;
v_len_copy := rec_inv.file_length;
WHILE v_start < rec_inv.file_length AND v_bytelen > 0
LOOP
v_position := 50;
DBMS_LOB.READ (rec_inv.file_data, v_bytelen, v_start, v_raw_var);
v_position := 60;
UTL_FILE.put_raw (v_output, v_raw_var);
v_position := 70;
UTL_FILE.fflush (v_output);
v_start := v_start + v_bytelen;
v_len_copy := v_len_copy - v_bytelen;
IF v_len_copy < 32000
THEN
v_bytelen := v_len_copy;
END IF;
END LOOP;
v_position := 80;
UTL_FILE.fclose (v_output);
END IF;
v_position := 90;
DBMS_OUTPUT.put_line (' File_name :' || rec_inv.file_name);
END LOOP;
END xx_download_attachment;
/
=======================================================================
execute the procedure to download the attachment.
execute xx_download_attachment;
Note :
- 'ODPDIR' is the directory name.
refer dba_directories for more detail.
select * from dba_directories;
