API to Download Attachments from Database to Server

DECLARE

   CURSOR cur_new_attmt

   IS

      SELECT DBMS_LOB.getlength (fl.file_data) file_length,

             fl.file_data,

             fl.file_name

        FROM hr_document_extra_info dei,

             per_all_people_f per,

             hr_document_types hdt,

             fnd_attached_documents fat,

             fnd_documents fd,

             fnd_lobs fl

       WHERE     per.person_id = dei.person_id

             AND per.business_group_id = 81

             AND per.employee_number = '30836'

             AND per.current_employee_flag = 'Y'

             AND TRUNC (SYSDATE) BETWEEN per.effective_start_date

                                     AND per.effective_end_date

             AND hdt.document_type_id = dei.document_type_id

             AND fat.entity_name = 'R_DOCUMENT_EXTRA_INFO'

             AND pk1_value = dei.document_extra_info_id

             AND fd.document_id = fat.document_id

             AND fl.file_id = fd.media_id

             AND hdt.category_code = 'VISA_INFO';

   l_start           NUMBER DEFAULT 1;

   l_bytelen         NUMBER DEFAULT 32000;

   l_len_copy        NUMBER;

   l_raw_var         RAW (32000);

   l_output          UTL_FILE.file_type;

   l_inv_file_name   VARCHAR2 (100);

   l_position        NUMBER;

   l_first_rec       BOOLEAN DEFAULT TRUE;

BEGIN

   l_position := 10;

   FOR get_details IN cur_new_attmt

   LOOP

      l_inv_file_name := NULL;

      l_inv_file_name := get_details.file_name;

      l_position := 20;

      -- create or replace directory MY_PDF2 as '/var/tmp' -- Folder should have Read/Write Privs

      --select * from dba_directories --> Check if the Directory has been created Successfully

      DBMS_OUTPUT.put_line (l_inv_file_name);

      l_output :=

         UTL_FILE.fopen ('MY_PDF2',

                         l_inv_file_name,

                         'wb',

                         32767);

      DBMS_OUTPUT.put_line ('Open');

      l_position := 30;

      -- maximum size OF buffer parameter IS 32767 BEFORE

      -- which you have TO flush  your buffer

      IF get_details.file_length < 32760

      THEN

         UTL_FILE.put_raw (l_output, get_details.file_data);

         UTL_FILE.fflush (l_output);

      ELSE

         l_position := 40;

         l_start := 1;

         l_bytelen := 32000;

         l_len_copy := get_details.file_length;

         DBMS_OUTPUT.put_line (l_len_copy);

         WHILE l_start < get_details.file_length AND l_bytelen > 0

         LOOP

            l_position := 50;

            DBMS_LOB.read (get_details.file_data,

                           l_bytelen,

                           l_start,

                           l_raw_var);

            l_position := 60;

            UTL_FILE.put_raw (l_output, l_raw_var);

            l_position := 70;

            UTL_FILE.fflush (l_output);

            l_start := l_start + l_bytelen;

            l_len_copy := l_len_copy - l_bytelen;

            IF l_len_copy < 32000

            THEN

               l_bytelen := l_len_copy;

            END IF;

         END LOOP;

         l_position := 80;

         UTL_FILE.fclose (l_output);

      END IF;

      l_position := 90;

      DBMS_OUTPUT.put_line (

         'File has been Copied Successfully: ' || get_details.file_name);

   END LOOP;

END;