Friday, June 14, 2013

How to download a BLOB file (AP Invoice Attachment) from the Database? (UTL_FILE.PUT_RAW)















We had a requirement to download an AP Invoice attachment stored in BLOB column from the database. We wrote a standalone procedure to accomplish the same. 

Script:


CREATE OR REPLACE PROCEDURE xx_unload_ecx_attachment
                     (p_msg_id IN VARCHAR2,
                      p_inv_image_dir IN VARCHAR2
                      ) IS

  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,
           ecx_attachment_maps eam
     WHERE eam.fid = fl.file_id
       AND eam.msgid = p_msg_id;

  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
    BEGIN
      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(p_inv_image_dir,
                                 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);
   
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line(rpad(nvl(v_inv_file_name,'NA'),31)
                            || rpad('ERROR', 21)
                            || 'POSITION: '
                            || v_position
                            || 'Error :'
                            || substr(SQLERRM,1,100));
    END;
  END LOOP;
END xx_unload_ecx_attachment;



Friday, June 14, 2013 by Team search · 0

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.