create or replace procedure store_image (
p_file_name in varchar2
, p_description in varchar2
)
is
l_image_id DMS_MASTER.DOC_ID%type;
l_file blob;
l_mime_type apex_application_files.mime_type%type;
l_name apex_application_files.name%type;
l_file_ext varchar2(255) := regexp_substr(p_file_name, '\..*$');
begin
-- get file from apex files
select name
, mime_type
, blob_content
into l_name
, l_mime_type
, l_file
from apex_application_files
where name = p_file_name;
-- insert record into images table
insert into DMS_MASTER ( DOC_FILE_NAME
, DOC_MIME_TYPE
, DOC_DESCRIPTION)
values ( l_name
, l_mime_type
, p_description)
returning DOC_ID into l_image_id;
-- insert file to os, use table pk as file name
write_to_file(l_image_id||l_file_ext, 'MY_DIR', l_file);
-- resize image, could check for mime-type here
-- delete file from apex files when done
delete from apex_application_files
where name = p_file_name;
end store_image;