Document Of Records Download IN Oralce HRMS

Document Download:

Step 1:

Create a Directory To Download the Files SYS User:

CREATE OR REPLACE DIRECTORY FND_BLOBS AS '/usr/tmp/FND_LOBS'

(Directory Path in Apps/DB Tier)

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] ,

DECLARE

l_file UTL_FILE.FILE_TYPE;

l_buffer RAW(32767);

l_amount BINARY_INTEGER := 32767;

l_pos INTEGER := 1;

l_blob BLOB;

l_blob_len INTEGER;

l_file_name varchar2(100);

BEGIN

-- Get LOB locator

FOR rec IN (SELECT L.file_data

  INTO   l_blob

  FROM fnd_attached_documents ad,  fnd_documents d, fnd_lobs l

 WHERE ad.pk1_value =41402(Unique Key Refers to the document for each Employee)

  and  d.document_id = ad.document_id

   AND ad.ENTITY_NAME LIKE 'R_DOCUMENT_EXTRA_INFO'(Entity Name Is the Document Reference Name)

 AND l.file_id = d.media_id)

 LOOP

l_blob_len := DBMS_LOB.getlength(rec.l_blob);

-- Open the destination file.

l_file := UTL_FILE.fopen('FND_BLOBS',rec.L.file_data,'w', 32767);

-- Read chunks of the BLOB and write them to the file

-- until complete.

WHILE l_pos < l_blob_len LOOP

DBMS_LOB.read(rec.l_blob, l_amount, l_pos, l_buffer);

UTL_FILE.put_raw(l_file, l_buffer, TRUE);

UTL_FILE.fflush (l_file);

l_pos := l_pos + l_amount;

END LOOP;

-- Close the file.

UTL_FILE.fclose(l_file);

END LOOP;

EXCEPTION

WHEN OTHERS THEN

-- Close the file if something goes wrong.

IF UTL_FILE.is_open(l_file) THEN

UTL_FILE.fclose(l_file);

END IF;

RAISE;

END;

Output directory path in Apps

/u01/(instance_name)/inst/apps/xyz/logs/appl/conc/out/FND_BLOBS

Commit and Check the Path File will Be downloaded in the specified Location.

DECLARE

l_file UTL_FILE.FILE_TYPE;

l_buffer RAW(32767);

l_amount BINARY_INTEGER := 32767;

l_pos INTEGER := 1;

l_blob BLOB;

l_blob_len INTEGER;

l_file_name varchar2(100);

BEGIN

-- Get LOB locator

FOR rec IN (SELECT L.file_data

  INTO   l_blob

  FROM fnd_attached_documents ad,  fnd_documents d, fnd_lobs l

 WHERE ad.pk1_value =41402

  and  d.document_id = ad.document_id

   AND ad.ENTITY_NAME LIKE 'R_DOCUMENT_EXTRA_INFO'

 AND l.file_id = d.media_id)

 LOOP

l_blob_len := DBMS_LOB.getlength(rec.l_blob);

-- Open the destination file.

l_file := UTL_FILE.fopen('/u01/instance name/inst/apps/xyz/logs/appl/conc/out/FCP_BLOBS',rec.L.file_data,'w', 32767);

-- Read chunks of the BLOB and write them to the file

-- until complete.

WHILE l_pos < l_blob_len LOOP

DBMS_LOB.read(rec.l_blob, l_amount, l_pos, l_buffer);

UTL_FILE.put_raw(l_file, l_buffer, TRUE);

UTL_FILE.fflush (l_file);

l_pos := l_pos + l_amount;

END LOOP;

-- Close the file.

UTL_FILE.fclose(l_file);

END LOOP;

EXCEPTION

WHEN OTHERS THEN

-- Close the file if something goes wrong.

IF UTL_FILE.is_open(l_file) THEN

UTL_FILE.fclose(l_file);

END IF;

RAISE;

END;

---

Commit And Check the Directory