Making the File Upload a required field is difficult with APEX 24.1 or earlier. In this post, I will show you the solution I use until Oracle APEX's native behavior improves.

The definition of my table:

create table xfile 
(    id           number generated by default on null as identity
                 constraint xfile_pk primary key,
    app_id       number not null, 
    name         varchar2(400) not null, 
    filename     varchar2(400) not null,  
    mime_type    varchar2(255) not null,  
    created_on   date default sysdate not null,  
    blob_content blob not null
);

Create an Interactive Report with Form on this table:

When you run the page and create a new record but forget to add a file, you will receive this:

This is perfect, just like we want. The blob content item has the required value flag set automatically, because APEX picked up the "not null" constraint from the table. Because of this flag, APEX raises the error when no file is selected.

Now, what happens if we edit the record?

Although there is a file, APEX still complains that there is no file. This is not the behavior we want! Whenever there is a file, it should not give an error. The issue is that APEX doesn't detect that a file has already been selected before.

To work around this issue, I set the "Value Required" on the item to no and wrote my own validation.

My custom validation "Check for required blob":

The code looks like this, I added debug messages to explain what it does:

declare
  l_exists varchar2(1) := 'N';
begin
  apex_debug.message('## The id is: ' || :P3_ID);
  apex_debug.message('## The value of blob content is: ' || :P3_BLOB_CONTENT);

  if :P3_ID is null and :P3_BLOB_CONTENT is not null
  then
    apex_debug.message('## New record and blob filled in.');
    l_exists := 'Y';

  elsif :P3_ID is not null
  then
    apex_debug.message('## We need to find a blob for id: ' || :P3_ID);
    begin
      select 'Y'
        into l_exists
        from xfile
       where id = to_number(:P3_ID)
         and blob_content is not null; 

      apex_debug.message('## We are good.');
    exception
    when no_data_found
    then
      apex_debug.message('## We did not find a blob.');
    end;
  end if;

  apex_debug.message('## Exists: ' || l_exists);

  if l_exists = 'Y'
  then 
    return true;
  else  
    return false;
  end if;  
end;

This code checks whether a blob is present when creating a new record or editing an existing record.

The page allows you to remove a file by clicking the X:

But when you do, Oracle APEX doesn't remove the file until you select another file. This is why the above code for the validation works, but it would have been cool if we could detect if the file was effectively removed.

So, an alternative solution to the above is detecting if a blob exists. This solution is a bit more complex since it has more moving pieces.

Create a hidden item on the page called P3_BLOB_EXISTS and make sure the item is not "Value Protected".

We will fill this item with the value Y when there's a download link or remove button, otherwise the value will be N. Note: checking only for the download link is not enough, since there is a setting to not show the download link.

Add a Dynamic Action called "Blob exists":

• When - Event: Before Page Submit

• Action - True: Execute JavaScript Code

    if ($(".a-FileDrop-download").length > 0 
        || $(".a-FileDrop-remove").attr('tabindex') == 0) {
      apex.item("P3_BLOB_EXISTS").setValue("Y");
    } else {
      apex.item("P3_BLOB_EXISTS").setValue("N");  
    }
  

Here's a screenshot of what that looks like:

We will change our Validation "Check for required blob" to the following:

declare
  l_blob_content varchar2(255) := :P3_BLOB_CONTENT;
  l_blob_exists  varchar2(1)   := :P3_BLOB_EXISTS;
begin
  apex_debug.message('## The value of blob content is: ' || l_blob_content);
  apex_debug.message('## Did a blob exist before: ' || l_blob_exists);

  if l_blob_content is not null
  then
    apex_debug.message('## A new blob is added.');
    return true;
  else
    apex_debug.message('## The blob item is empty.');
    if l_blob_exists = 'Y'
    then
      apex_debug.message('## A blob existed before.');
      return true;
    else 
      apex_debug.message('## A blob is missing.');
      return false;
    end if; 
  end if;
end;

Here's a screenshot of the validation:

So now, when you create a record and don't upload a file or when you edit the record and remove the file, APEX will say the field is required. And more importantly, APEX won't complain anymore when you edit the record and don't touch the file (blob).