How to upload and embed a pdf document within a page in an Oracle APEX Application

Some Oracle APEX customers prefer to view embedded PDF documents directly within a page in their application rather than having to download.

My current case study allows users to upload a .pdf document, save directly to a file location (rather than a DB Column) and view same embedded on the current page as shown above.

Steps taken:

1. Identify the physical location of the #IMAGE_PREFIX# (/i/), in my case it is/u01/userhome/oracle/apex/images

2. Create a folder called 'docs' in the directory logged in as user SYS

$ cd /u01/userhome/oracle/apex/images
$ mkdir docs

3. Create a database directory called MY_DOCS

create or replace directory MY_DOCS as '/u01/userhome/oracle/apex/images/docs';
Directory MY_DOCS created.

grant read,write on directory MY_DOCS to {WORKSPACE_SCHEMA};
Grant succeeded.

4. Create a page within your application with following create page items PXX_FIRST_NAME (Textfield), PXX_LAST_NAME (Textfield), PXX_UPLOAD_DOC (File Browse), PXX_FILENAME (Hidden) and a 'Save' Button for submission

5. Create a PL/SQL Page Process to be fired after user clicks on the save button

declare
l_file          utl_file.file_type;
l_buffer        raw(32000);
l_amount        binary_integer := 32000;
l_pos           integer := 1;
l_blob          blob;
l_blob_left     number;
l_blob_length   number;
l_file_name     varchar2(50);
l_directory     varchar2(50):='MY_DOCS';
l_seq           number;
begin

APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
    p_collection_name => 'INFO');
    
    
   select blob_content,filename,filename
    into l_blob,l_file_name,:P100_FILENAME
    from apex_application_temp_files;

    
    l_seq := APEX_COLLECTION.ADD_MEMBER(
                 p_collection_name => 'INFO',
                 p_c001            => :P100_FIRST_NAME,
                 p_c002            => :P100_LAST_NAME,
     p_c003            => l_file_name,
                 p_blob001         => l_blob );

l_blob_length := dbms_lob.getlength(l_blob);
l_blob_left := l_blob_length;

l_file := utl_file.fopen(l_directory,l_file_name,'WB', 32760);

if l_blob_length < 32760 then
utl_file.put_raw(l_file,l_blob);
utl_file.fflush(l_file);
else 
l_pos := 1;
while l_pos < l_blob_length
loop
  dbms_lob.read(l_blob,l_amount,l_pos,l_buffer);
  utl_file.put_raw(l_file,l_buffer);
  utl_file.fflush(l_file);
  -- set the start position for the next cut
  l_pos := l_pos + l_amount;
  -- set the end position if less than 32000 bytes
  l_blob_left := l_blob_left - l_amount;
  if l_blob_left < 32000 then
     l_amount := l_blob_left;
  end if;
end loop;
end if;
utl_file.fclose(l_file);
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;

6. On the 'Uploaded Document' static content region add below to the source

< embed height="800%" src="#IMAGE_PREFIX#docs/&P100_FILENAME." type="application/pdf" width="100%" > < /embed >< /span >

Header Text - < div style="overflow: auto; height: 700px;" >
Footer Text - < /div >

7. Ensure 'After Processing' redirects to the current page

I hope someone out there finds this helpful!