Upload a file in APEX and write it to the UNIX filesystem

A csv file is uploaded from Windows into a blob via APEX. Subsequently this blob is written to a file on Unix. There is a lot of code on the internet that do the job, but at the end you will see ctrl-M at the end of each line when opening the file in the vi editor. This is due to the difference between Windows and and Unix. Windows puts a carriage return and linefeed (CR/LF) at the end of each line whereas Unix is only using a linefeed. The extra symbol show up as ^M in vi. To get rid of this character we have to cast the raw (chunck of) data to char and then replace CHR(13) (the CR) by nothing before writing it to the filesystem. The rest of te code in this procedure write_dos_blob_to_unix_file is pretty much the same as other code you will find on the internet.

The page in Application Express (APEX) is similar to upload page I created earlier (see post Upload a file in APEX and submit concurrent request in EBS R12). Instead of submitting a request in the Oracle E-Business Suite R12 (EBS R12), the additional custom process ('Write BLOB to filesystem') calls a routine that writes the blob content to a unix file. This routine is added to the apex_global package, where it will run as APPS user (because of the authid clause). This was necessary because APPS already has the rights to write to the unix filesystem via directory 'XXCUST_TEMP_FILES'. (Here directory is an Oracle database object that contains the actual path on the filesystem and where rights can be assigned to).

Source in the process definition of the additional process 'Write BLOB to filesystem':

BEGIN
   apps.apex_global.write_dos_blob_to_unix_file('XXCUST_TEMP_FILES',TO_NUMBER(V('P12_FILE_ID')));
END;

The code that has been added to the apex_global package:

CREATE OR REPLACE PACKAGE apex_global AUTHID DEFINER AS
   /****************************************************************
   *
   * PROGRAM NAME
   *    APEX_GLOBAL.pks
   *
   * DESCRIPTION
   *    Routines for collaboration of APEX with EBS
   *
   * CHANGE HISTORY
   * Who          When         What
   * ---------------------------------------------------------------
   * M. Weeren    01-07-2014   Initial Version
   *****************************************************************/

   ...

   PROCEDURE write_dos_blob_to_unix_file (
      p_location  IN VARCHAR2,
      p_file_id   IN NUMBER);
END;
/

CREATE OR REPLACE PACKAGE BODY apex_global AS
   /****************************************************************
   *
   * PROGRAM NAME
   *    APEX_GLOBAL.pkb
   *
   * DESCRIPTION
   *    Routines for collaboration of APEX with EBS
   *
   * CHANGE HISTORY
   * Who          When         What
   * ---------------------------------------------------------------
   * M. Weeren    01-07-2014   Initial Version
   *****************************************************************/

   ...

   PROCEDURE write_dos_blob_to_unix_file (
      p_location  IN VARCHAR2,
      p_file_id   IN NUMBER)
   IS
      c_chunck_size  CONSTANT   NUMBER := 32760;

      l_file_id                 NUMBER; 
      l_filename                VARCHAR2(100);

      l_blob                    BLOB;
      l_blob_length             NUMBER;
      l_bytes_written           NUMBER := 0;
      l_chunck                  RAW(32760);
      l_chunck_size             NUMBER;

      l_output                  utl_file.file_type;

   BEGIN
      -- select filename, blob incl length into variables
      SELECT file_name
      ,      dbms_lob.getlength(file_data)
      ,      file_data
      INTO   l_filename
      ,      l_blob_length
      ,      l_blob
      FROM   xxoic_files_pons
      WHERE  file_id = p_file_id;

      -- define output directory
      l_output := utl_file.fopen(p_location, l_filename, 'W', c_chunck_size);

      -- if small enough for a single write
      IF l_blob_length <= c_chunck_size THEN
         utl_file.put(l_output, replace(utl_raw.cast_to_varchar2(l_blob),chr(13)));
         utl_file.fflush(l_output);
      ELSE -- write in pieces
         l_chunck_size := c_chunck_size;
         WHILE l_bytes_written < l_blob_length
         LOOP
            l_chunck_size := least(c_chunck_size, l_blob_length - l_bytes_written);
            dbms_lob.read(l_blob, l_chunck_size, l_bytes_written + 1, l_chunck);

            utl_file.put(l_output, replace(utl_raw.cast_to_varchar2(l_chunck),chr(13)));
            utl_file.fflush(l_output); 

            l_bytes_written := l_bytes_written + l_chunck_size;
        END LOOP; 
      END IF;
      utl_file.fclose(l_output);
   END;
   
END;
/