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('
if :P3_ID is null and :P3_BLOB_CONTENT is not null
then
apex_debug.message('
l_exists := 'Y';
elsif :P3_ID is not null
then
apex_debug.message('
begin
select 'Y'
into l_exists
from xfile
where id = to_number(:P3_ID)
and blob_content is not null;
apex_debug.message('
exception
when no_data_found
then
apex_debug.message('
end;
end if;
apex_debug.message('
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('
if l_blob_content is not null
then
apex_debug.message('
return true;
else
apex_debug.message('
if l_blob_exists = 'Y'
then
apex_debug.message('
return true;
else
apex_debug.message('
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).
