I am trying to upload an image into APEX (Version 24.2) and store it in a BLOB column (Product_Image) in the PRODUCTS table.

I also have the MIME type, filename, and last updated columns properly set up.

Current Setup

• In APEX, I have an "Upload Image" field titled P21_IMAGE.
• Storage Type: I selected BLOB Column Specified in the Source Attribute, but no table is popping up (because I am not using a form).
• Goal: I want to run a simple INSERT statement when the page is submitted to store the uploaded image in the database.

Issues Encountered

• Using a direct INSERT into the BLOB column:
  • ❌ Returns an "Invalid Hex Number" error.
• Using APEX_APPLICATION_TEMP_FILES to store and retrieve the image:
  • ❌ Returns "No file uploaded" error.
  • ✅ This worked in another APEX app but had issues displaying the image later.

Code for Using APEX Temporary Table (APEX_APPLICATION_TEMP_FILES)

DECLARE
    v_new_product_id NUMBER;
    v_blob BLOB;
    v_filename VARCHAR2(255);
    v_mime VARCHAR2(50);
    v_last_updated DATE;
BEGIN
    -- Get the next available PRODUCT_ID (max +1)
    SELECT NVL(MAX(PRODUCT_ID), 0) + 1 
    INTO v_new_product_id 
    FROM PRODUCTS;

    -- Retrieve uploaded file details from APEX temporary storage
    SELECT blob_content, filename, mime_type, created_on
    INTO v_blob, v_filename, v_mime, v_last_updated
    FROM APEX_APPLICATION_TEMP_FILES
    WHERE name = :P21_IMAGE_FILENAME1;

    -- Insert into the PRODUCTS table
    INSERT INTO PRODUCTS (
        PRODUCT_ID, PRODUCT_NAME, UNIT_PRICE, PRODUCT_IMAGE, 
        IMAGE_MIME_TYPE, IMAGE_FILENAME, IMAGE_LAST_UPDATED
    ) VALUES (
        v_new_product_id, :P21_PRODUCT_NAME1, :P21_UNIT_PRICE1, v_blob, 
        v_mime, v_filename, v_last_updated
    );

    -- Cleanup temporary file storage
    DELETE FROM APEX_APPLICATION_TEMP_FILES WHERE name = :P21_IMAGE_FILENAME1;

    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'No file uploaded.');
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20002, 'Unexpected error: ' || SQLERRM);
END;

Code for Direct BLOB Insert

DECLARE
    v_new_product_id NUMBER;
BEGIN
    -- Get the next available PRODUCT_ID (max +1)
    SELECT NVL(MAX(PRODUCT_ID), 0) + 1 
    INTO v_new_product_id 
    FROM PRODUCTS;

    -- Insert the image into the PRODUCTS table
    INSERT INTO PRODUCTS (
        PRODUCT_ID, PRODUCT_NAME, UNIT_PRICE, PRODUCT_IMAGE, 
        IMAGE_MIME_TYPE, IMAGE_FILENAME, IMAGE_LAST_UPDATED
    ) VALUES (
        v_new_product_id, :P21_PRODUCT_NAME1, :P21_UNIT_PRICE1, TO_BLOB(:P21_IMAGE), 
        :P21_IMAGE_MIME_TYPE1, :P21_IMAGE_FILENAME1, :P21_IMAGE_LAST_UPDATED
    );

    COMMIT;
END;

What I Need Help With

1. Is it possible to upload an image using an INSERT statement in APEX?
2. Why am I getting a "No file uploaded" error when using APEX_APPLICATION_TEMP_FILES?
3. How do I properly display the uploaded image after inserting it?

Additional Context

• In other apps, I have used Java to upload images to a remote database folder.
  • This has caused timing/order of events issues, so I'd prefer to keep everything in APEX and Oracle DB.
• Any cheat sheet or best practices for handling BLOB uploads in APEX would be greatly appreciated!

I am trying to upload an image into APEX (Version 24.2) and store it in a BLOB column (Product_Image) in the PRODUCTS table.

I also have the MIME type, filename, and last updated columns properly set up.

Current Setup

• In APEX, I have an "Upload Image" field titled P21_IMAGE.
• Storage Type: I selected BLOB Column Specified in the Source Attribute, but no table is popping up (because I am not using a form).
• Goal: I want to run a simple INSERT statement when the page is submitted to store the uploaded image in the database.

Issues Encountered

• Using a direct INSERT into the BLOB column:
  • ❌ Returns an "Invalid Hex Number" error.
• Using APEX_APPLICATION_TEMP_FILES to store and retrieve the image:
  • ❌ Returns "No file uploaded" error.
  • ✅ This worked in another APEX app but had issues displaying the image later.

Code for Using APEX Temporary Table (APEX_APPLICATION_TEMP_FILES)

DECLARE
    v_new_product_id NUMBER;
    v_blob BLOB;
    v_filename VARCHAR2(255);
    v_mime VARCHAR2(50);
    v_last_updated DATE;
BEGIN
    -- Get the next available PRODUCT_ID (max +1)
    SELECT NVL(MAX(PRODUCT_ID), 0) + 1 
    INTO v_new_product_id 
    FROM PRODUCTS;

    -- Retrieve uploaded file details from APEX temporary storage
    SELECT blob_content, filename, mime_type, created_on
    INTO v_blob, v_filename, v_mime, v_last_updated
    FROM APEX_APPLICATION_TEMP_FILES
    WHERE name = :P21_IMAGE_FILENAME1;

    -- Insert into the PRODUCTS table
    INSERT INTO PRODUCTS (
        PRODUCT_ID, PRODUCT_NAME, UNIT_PRICE, PRODUCT_IMAGE, 
        IMAGE_MIME_TYPE, IMAGE_FILENAME, IMAGE_LAST_UPDATED
    ) VALUES (
        v_new_product_id, :P21_PRODUCT_NAME1, :P21_UNIT_PRICE1, v_blob, 
        v_mime, v_filename, v_last_updated
    );

    -- Cleanup temporary file storage
    DELETE FROM APEX_APPLICATION_TEMP_FILES WHERE name = :P21_IMAGE_FILENAME1;

    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'No file uploaded.');
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20002, 'Unexpected error: ' || SQLERRM);
END;

Code for Direct BLOB Insert

DECLARE
    v_new_product_id NUMBER;
BEGIN
    -- Get the next available PRODUCT_ID (max +1)
    SELECT NVL(MAX(PRODUCT_ID), 0) + 1 
    INTO v_new_product_id 
    FROM PRODUCTS;

    -- Insert the image into the PRODUCTS table
    INSERT INTO PRODUCTS (
        PRODUCT_ID, PRODUCT_NAME, UNIT_PRICE, PRODUCT_IMAGE, 
        IMAGE_MIME_TYPE, IMAGE_FILENAME, IMAGE_LAST_UPDATED
    ) VALUES (
        v_new_product_id, :P21_PRODUCT_NAME1, :P21_UNIT_PRICE1, TO_BLOB(:P21_IMAGE), 
        :P21_IMAGE_MIME_TYPE1, :P21_IMAGE_FILENAME1, :P21_IMAGE_LAST_UPDATED
    );

    COMMIT;
END;

What I Need Help With

1. Is it possible to upload an image using an INSERT statement in APEX?
2. Why am I getting a "No file uploaded" error when using APEX_APPLICATION_TEMP_FILES?
3. How do I properly display the uploaded image after inserting it?

Additional Context

• In other apps, I have used Java to upload images to a remote database folder.
  • This has caused timing/order of events issues, so I'd prefer to keep everything in APEX and Oracle DB.
• Any cheat sheet or best practices for handling BLOB uploads in APEX would be greatly appreciated!

• file-upload
• blob
• oracle-apex

• Why "NOT A FORM"? Uploading images, as well as displaying them works fine since ... well, at least version 3.2 (I didn't use earlier versions). That's a well-known process, documented, with many examples on the Internet. Why don't you take that approach, instead of (what we usually call) reinvent the wheel? – Littlefoot Commented Feb 15 at 10:34
• I'd suggest filing this in the official oracle apex forum. I'm pretty sure this is not possible but chances are someone watching that forum call tell you exactly why :) – Koen Lostrie Commented Feb 15 at 18:20
• @Nicholas were you able to get a solution ? – Koen Lostrie Commented Feb 19 at 17:08

1 Answer 1

I noticed you mentioned the page item is called P21_IMAGE but in your code you have your where statement referring to P21_IMAGE_FILENAME1.

I think you need to check this first.

SELECT blob_content, filename, mime_type, created_on
    INTO v_blob, v_filename, v_mime, v_last_updated
    FROM APEX_APPLICATION_TEMP_FILES
    WHERE name = :P21_IMAGE_FILENAME1;

this may not be correct