Attachments in APEX Application

Uploading Attachments

So often it is not enough to be able to display data and to provide a good UI for data handling.

Every now and then you need to be able to store complete files in your DB.

That can be technical drawings or a paper that has a signature on it.

Whatever reason you have for it, I will show you two options how you can include the attachment functionality in your APEX apps.

Following examples are all built with APEX 4.2.x.
Things change in APEX 5. Roel Hartman blogged about it a couple of months ago.

Include the file in your main table

E.g. you want to support the possibility to attach a file to a screen.  One single file only.

I once had to do this to support a sort of workflow driven process.

In the process of data gathering, at some point to be able to move to the next status, it was required that a specific document was attached.

The page was built using an Automated Row Fetch and Automated Row Processing.

So there I opted to include the file in the dedicated table for that page.

Your table definition

You need to have some columns in your table, of course you can add them via an alter table statement if this requirements comes up during your development process.

Those dedicated columns are:

Column name	Data type
DOC_BLOB	BLOB
DOC_MIME_TYPE	VARCHAR2(100)
DOC_FILE_NAME	VARCHAR2(250)
DOC_UPDATE_DATE	DATE

In most cases those columns will be NULLS ALLOWED. But that depends on your functional specifications.

Example in the APEX page before the upload

The APEX solution

You will have on your page following page items:

Filebrowser P73_DOC_BLOB

Storage Type: I opted here for the Storage Type "BLOB", because this is the case where the file is stored in the table specified by Automated Row Processing.

MIME Type column: It is in fact not required that this column exists as page item on your page. I did it anyway, but made it hidden.

In this column the mime type of the uploaded column will be stored.

Filename Column: this is where the filename is stored. This can be easily retrieved via SQL to display elsewhere that a file has been attached. Possibly with a download link.

BLOB Last Updated Column: will contain the timestamp as used by browser caching to identify if the file has changed.

Download Link text: this text will be shown once the file is uploaded (see picture below)

Content disposition: (as copied from the APEX help info:) Content Disposition is used by the browser to decide if the downloaded content should be displayed inline within the browser window or if the open dialog should be displayed. Not all content will be displayed inline, it depends on the supported mime types.

This identifies that the file will be uploaded to the column DOC_BLOB of the table specified by the Automated Row Processing.

I added a Read Only condition. Once a file is uploaded, it will be shown in R/O modus. The page has a specific button to delete the file.

Example in the APEX page after the upload

Use a dedicated attachments table

A second method to store attachments is to make use of a dedicated table that stores all files.

you link the file to its specific function with a foreign key link.

This method will be often used when you must be able to add multiple files to a functional area.

You can think of a person-record in an HCM database for which the scanned ID-card must be attached, but also the (paper scanned) CV of that person. 

Example in APEX

As often, there are multiple ways to implement. You can make a separate page (perhaps a modal window) where you can upload as many files as you want.

But in a specific case I was asked that the number of attachments had to be limited to a maximum of 10 files. I opted to stay on the same page and implemented the following:

This comes down to the following region on my page. 

I admit that I would not choose for this implementation for more than 10 attachments. To be honest, I think now that ten is already too much. But the requirement started with 3 files max and I had already built the solution when the customer explained they needed "a small change on the attachment functionality" and they needed up to 10 files ...

The REMARK field is a simple text field on your page. Nothing special.

The ATTACHMENT field needs some explanation.

Filebrowser P27_ATTACHMENT1

This filebrowser needs less specification compared to the one explained above. But following are important:

The storage type now points to the table WWV_FLOW_FILES. This is a table used by APEX to store the files. It is intended as a temporary table and you should after upload move your files to your own table. Moving in our case comes down to copy to the new table and then delete it from the WWV_FLOW_FILES table.

To do so, you will need a separate page process.

Page process to upload the attachment

I had created a page process that fires upon page submit. But of course you can also add a button that triggers a dynamic action and performs the same logic.

How to deal wit WWV_FLOW_FILES

You can access this table by using your page-item (e.g. P27_ATTACHMENT1) that holds your file.

And that you use to copy (insert) the file to your own table:

insert into my_attachments_table

   ( doc_blob

   , doc_mime_type

   , doc_file_name

   )

   select blob_content

        , mime_type

        , :P27_ATTACHMENT1

     from wwv_flow_files

    where name = :P27_ATTACHMENT1;

So that will be the first step in your process.

This must be immediately followed - as per good practise and advised by Oracle - by deleting the file from the WWV_FLOW_FILES table:

delete from wwv_flow_files where name = :P27_ATTACHMENT1;

A smart thing of course to do is to only execute this code if an attachment was added. Do this via a condition (:P27_ATTACHMENT1 must not be null).

Downloading Attachments

I've provided you with 2 options to upload attachments. But of course you must also be able to download them.

As shown in the first option of uploading attachments, when you use the BLOB mechanism of APEX, the download link is automatically created for you. Nice and easy! 

But you can also add a download link in a report. When you have some nice icons uploaded or you have integrated something like Font Awesome, you can even use a nice icon in stead of the "download" text to do this.

But here's a quick example:

The SQL behind it:

select att.attachment_id

     , att.proposal_id

     , att.remark

     , att.doc_file_name

     , substr( att.doc_file_name, instr( att.doc_file_name, '/') + 1) as short_file_name

     , dbms_lob.getlength(att.doc_blob) as doc_blob

     , null as delete_me

  from my_attachments_table att

 where att.proposal_id = :P14_PROPOSAL_ID

Two important columns for us now:

• delete_me
• doc_blob

Delete Me

Let me start with the delete option.

I hadn't spoken about it, but you will probably want to allow the end user to remove a file again from the database.

To do this, I've created a (hidden) page item to capture the attachment-id.

This item triggers a dynamic action (on change) that will remove the specified attachment.

And to change that hidden item (and by consequence trigger the dynamic action), I use a link on the column name with in the URL following javascript code:

javascript:$s('P14_DELETE_ATT_ID','#ATTACHMENT_ID#')

This is what the column link looks like:

Do not forget to Display the delete (and download) column as "Display as text":

When you click the link, the javascript will fill the page item :P14_DELETE_ATT_ID with the value captured in #ATTACHMENT_ID#, which is the value of the specified line in your report.

Following mechanism will be triggered:

The PL/SQL to be executed is fairly simple:

(do not forget to submit the page-item)

The refresh will make sure your page is immediately refreshed with the new content (removed file).

Doc Blob

The DOC_BLOB contains your download settings.

This is the somewhat tricky bit. Specify following syntax in the Column definition's Number / Date format of your report query:

DOWNLOAD:MY_ATTACHMENTS_TABLE:DOC_BLOB:ATTACHMENT_ID::::::Attachment:download

The Blob Column Attributes point to your (custom) attachments table:

Happy to share ...