Downloading files from the database server via APEX

Question

I am trying to find a way to get files from the database server via APEX. I can't find any documentation about this issue. Can I avoid using the plsql code?

PS: I'm launching APEX on tomcat on different server that db is.

Answer 1

As we talked on the comments, let me show you an example. I write you a lot of thing so take your time...

Upload file

You can have an object in Apex that allows the user to browse for a file to be uploaded. At the end, the user press a button that triggers the action . The button upload submits the page, and the action is after submit for the button itself. Any file upload is stored automatically in apex_application_temp_files ( keep in mind I removed a lot of controls I have regarding format of the file, size, etc ).

First create the directory

create or replace directory yourdirectory as '/your_path' ;
grant read, write on directory yourdirectory to your_user ;

The code in the button:

declare 
v_error       VARCHAR2(400);
v_filename    VARCHAR2(400);
v_name        VARCHAR2(400);
v_blob        blob; 
vodate        number(8);
begin
   
    SELECT filename,blob_content,name, to_number(regexp_replace(filename,'[0-9]{4}[0-9]{2}[0-9]{2}')) 
    INTO v_filename,v_blob,v_name,vodate
    FROM apex_application_temp_files
    WHERE name = :P2_FILE;
    
    apex_debug.enable ( p_level => 5 );
    apex_debug.message(p_message => 'v_filename is '||v_filename||' ', p_level => 5) ;
    apex_debug.message(p_message => 'v_name is '||v_name||' ', p_level => 5) ;
    apex_debug.message(p_message => 'vodate is '||to_number(substr(v_filename,14,8)) ||' ', p_level => 5) ;  

    -- insert into filesystem
    p_write_blob_to_file(p_name=>v_name);


   EXCEPTION
    WHEN OTHERS THEN
              raise;
end;

The important part here is the code p_write_blob_to_file. This is the code of that procedure, keeping in consideration that in my case p_dir takes a default value.

CREATE OR REPLACE procedure p_write_blob_to_file (p_name IN VARCHAR2, p_dir IN VARCHAR2 default 'your_directory' )
   IS
      l_blob            BLOB;
      l_blob_length     INTEGER;
      l_out_file        UTL_FILE.file_type;
      l_buffer          RAW (32767);
      l_chunk_size      BINARY_INTEGER := 32767;
      l_blob_position   INTEGER := 1;
      l_file_name       varchar2(2000);
      v_mime_type       varchar2(2000);     
   BEGIN
     
    -- Retrieve the BLOB for reading
    SELECT blob_content, filename, mime_type 
      INTO l_blob, l_file_name, v_mime_type
      FROM apex_application_temp_files
    WHERE name = p_name;
    
      -- Retrieve the SIZE of the BLOB
      l_blob_length := DBMS_LOB.getlength (l_blob);

      -- Open a handle to the location where you are going to write the BLOB
      -- to file.

      l_out_file :=
         UTL_FILE.fopen (p_dir,
                         l_file_name,
                         'wb',
                         l_chunk_size);

      -- Write the BLOB to file in chunks
      WHILE l_blob_position <= l_blob_length
      LOOP
         IF l_blob_position + l_chunk_size - 1 > l_blob_length
         THEN
            l_chunk_size := l_blob_length - l_blob_position + 1;
         END IF;

         DBMS_LOB.read (l_blob,
                        l_chunk_size,
                        l_blob_position,
                        l_buffer);
         UTL_FILE.put_raw (l_out_file, l_buffer, TRUE);
         l_blob_position := l_blob_position + l_chunk_size;
      END LOOP;

      -- Close the file handle
      UTL_FILE.fclose (l_out_file);
END p_write_blob_to_file;
/

Download File

In order to download the file, you need the opposite path.

• The button or link download must be associated to a component PL/SQL
• The button loads the file first from the directory in the server into a column
• The button then download the file

I was going to write all the commands here , but you have a find very good example here of both actions:

• Load file from directory into column

https://renaps.com/en/blog/how-to/how-to-load-file-content-to-a-blob-field-and-unload-blob-content-to-a-file-on-the-os

• Download file

https://oracle-base.com/articles/misc/apex-tips-file-download-from-a-button-or-link#apex-button

Try to experiment with this and let me know any issues you might find. The only tricky thing here is that in Apex you need to pass the name of the file you want to download. So the user must know the name, exactly as it is in the server. What you can't do is provide a graphical interface to the server in order to select the file.