APEX

I am developing a SIM Services Application in APEX where users can apply for international roaming. The process involves filling out an application form, which is then sent to the HO Admin. The admin can upload a document on the "Send Details" page, and the user can download this document from the "Task Details" page of their request.

However, I am encountering an issue with the file upload functionality. When I upload a file and click on submit, I receive the following error: ORA-01465: invalid hex number.

Here are the details of my implementation:

Script to create tables:

  CREATE TABLE "EMP_SIM_ROAMING" 
  (    "REQ_ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 8000 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
    "EMP_NO" VARCHAR2(10), 
    "ISSUANCE_REQUIREMENTS" VARCHAR2(50), 
    "REQUEST_DATE" DATE DEFAULT SYSDATE, 
    "LOCATION" VARCHAR2(100), 
    "COUNTRY_NAME" VARCHAR2(20), 
    "START_DATE" DATE DEFAULT SYSDATE, 
    "END_DATE" DATE DEFAULT SYSDATE, 
    "OFFICIAL_NO" VARCHAR2(20), 
    "USER_REMARK" VARCHAR2(100), 
    "UPDATED_BY" VARCHAR2(20), 
    "STATUS" VARCHAR2(20), 
    "PACK_DETAILS" BLOB, 
    "ACKNOWLEDGED" VARCHAR2(1) DEFAULT 'N', 
     CONSTRAINT "EMP_SIM_ROAMING_PK" PRIMARY KEY ("REQ_ID")
 USING INDEX  ENABLE
  ) ;
 

Page Item on the Send Details Page: I have an upload field where the admin can upload a document.

Page Item on the Task Details Page: This page item allows the user to download the uploaded document.

SQL Code for Submitting the Details after uploading:

declare
   l_email_recipient varchar2(50);
   l_email_subject varchar2(100);
   l_email_body varchar2(4000);
begin
   -- Ensure the BLOB data is correctly handled
   if :P13_PACK_DETAILS is not null then
       update emp_sim_roaming
       set pack_details = :P13_PACK_DETAILS,
           status = 'COMPLETED'
       where req_id = :P13_REQ_ID
       and emp_no = :P13_EMP_NO;
   end if;
   -- Send an email to the original applicant to notify about the update
   select email_id into l_email_recipient from emp_4 where empno = :P13_EMP_NO;
   l_email_subject := 'SIM Request Update';
   l_email_body := 'Dear Employee,' || chr(10) ||
                   'Your SIM request has been processed.' || chr(10) ||
                   'Please log in to your account to acknowledge receipt.' || chr(10) ||
                   'Thank you.';
   apex_mail.send(
       p_to => l_email_recipient,
       p_from => 'no-reply@example.com',
       p_subj => l_email_subject,
       p_body => l_email_body
   );
   apex_mail.push_queue();
end;

Current Task Details Page Display: The task details page currently displays an error message or is blank, which I believe is due to the absence of a file in the pack_details.

I would greatly appreciate any guidance on how to resolve the file upload issue on the "Send Details" page. Specifically, I am looking for assistance with the ORA-01465 error and ensuring the document is correctly uploaded and retrievable. Thanks!

---------UPDATE-----------

Okay so I started looking around the sample upload and download app present in the oracle apex itself , and I am able to download the files BUT the request ID is not mapping correctly to the request. When I upload a file it creates a new Request ID and not on the original request ID of the task. So any idea how can this be resolved?