Oracle Apex - should I use Empty_BLOB() or null?

Question

I am currently using Oracle Apex 5.1.2.

I created a table in the database that contains a column that can hold a BLOB data type, a column for filename, and a column for MIME type.

In my APEX application, I have a File Browse page item. The intent for this page item is to allow the end-user to attach a file to the page. This part works perfectly - the user is able to browse for a file using that item and attach it to a page and later download the file in order to view it.

The problem: I want the end-user to be able to delete the attachment if they need to and, if they'd like, attach another file in its place. My idea is to create a button and in the settings for the button set the Action to "Submit Page" and the Database Action to SQL Update Action. Then I will create a page process of type PL/SQL code and set the Server-side Condition in the settings for the process to occur when the button I created is pressed. In the PL/SQL code section of the process, it appears from the research I have done that I have one of two options:

UPDATE table_name
SET blob_column = EMPTY_BLOB()
WHERE my_id = id

OR:

UPDATE table_name
SET blob_column = null
WHERE my_id = id

I have already tried both of these options and they both work, but I don't know which option is better for what I am trying to achieve. I have tried to research this problem, but unfortunately I can't find the exact answer I am looking for. Does anyone have any recommendations?

Thank you in advance.

Answer 1

The choice is a matter of opinion, since both options delete the previous attachment.

I think most people would use blob_column = null to mean "no attachment", while blob_column = empty_blob() would mean "blank file attached". So I would recommend using null, but as you said, it works fine either way.

Answer 2

I think it depends on how you would like to test for nulls

http://nuijten.blogspot.com.au/2009/11/empty-clob-is-not-null-its-not-null.html

Answer 3

If you update your blob column with empty_blob() then you have to take care while selecting records like below:

suppose you want all records where blob column has some file (useful) then you have to select like below

select * from your_table where SYS.DBMS_LOB.GETLENGTH(blob_column) > 0;

but if you update your blob column with null then you can simply write like:

select * from your_table where blob_column is not null;