Oracle APEX 5文件浏览器BLOB存储：DML流程主键问题求助

Hey there, let's work through this BLOB upload issue you're facing in Oracle APEX 5—since you're new to the platform, I'll break this down step by step to make it clear.

Your primary pain point is figuring out how to handle the ID primary key in your DML process, and that's almost certainly the root of your upload errors. Let's start there.

1. First: Ensure Your Primary Key ID Gets Populated #

Your MATERIALS table has ID as a NOT NULL primary key, so you need a way to generate a unique value for it every time you insert a new record. You have two solid options here:

Option A: Use a Sequence + Trigger (Automatic, No Page Setup Needed) #

This is the most hands-off approach—create a sequence to generate IDs, then a trigger to auto-populate ID before inserting:

First, run this SQL to create the sequence:

CREATE SEQUENCE MATERIALS_SEQ
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;

Then create a trigger to use this sequence:

CREATE OR REPLACE TRIGGER MATERIALS_BI_TRG
BEFORE INSERT ON MATERIALS
FOR EACH ROW
BEGIN
  IF :NEW.ID IS NULL THEN
    SELECT MATERIALS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
  END IF;
END;
/

Once this is set up, Oracle will automatically fill in the ID value every time you insert a new row—you won't have to handle it in your APEX page at all.

Option B: Generate the ID in Your APEX Page #

If you prefer to control the ID generation directly in APEX:

• Create a hidden page item (e.g., P1_ID) on your upload page.
• Add a Before Header process to set this item's value to the next sequence number:

  :P1_ID := MATERIALS_SEQ.NEXTVAL;
  

• Make sure this process runs before your DML process.

2. Configure Your DML Process Correctly #

Now let's fix the DML process setup:

• Go to your page's Processes section and create an Automatic Row Processing (DML) process.
• Set the Table/View Name to MATERIALS.
• Under Primary Key Column, select ID (this tells APEX which column is the primary key for the table).
• For Primary Key Source:
  • If you used the trigger (Option A), select Existing Trigger (Trigger will populate key value).
  • If you're using the page item (Option B), select Item and choose your hidden item (e.g., P1_ID).
• Ensure the process's Action is set to Insert (or Insert or Update if you want to support editing existing records later).
• Double-check that all your form items (like MATERIAL_NAME, M_COURSE_ID) are mapped correctly to their corresponding table columns in the Items to Submit section (or set it to All Items to include everything).

3. Verify Your File Browse Item Configuration #

You mentioned you set the File Browse storage type to BLOB column, but let's confirm all mappings are correct (since mismatched columns can cause errors too):

• Edit your File Browse item.
• Go to the Storage tab (in APEX 5, this might be under Advanced depending on your theme).
• Set Storage Type to BLOB Column.
• Fill in these fields to match your table:
  • Table Name: MATERIALS
  • BLOB Column: CONTENT
  • Filename Column: FILENAME
  • MIME Type Column: MIME_TYPE
  • Character Set Column: CHARSET
  • Document Size Column: DOC_SIZE
• Save the item.

4. Common Troubleshooting Checks #

If you're still getting errors after these steps:

• Make sure your DML process is enabled and runs when you click your upload button (check the When Button Pressed setting to match your upload button's name).
• Verify that non-NULL columns (only ID in your table) are being populated—if you didn't use the trigger, ensure your P1_ID item is getting a value.
• Check if any other page processes are interfering with the insert (e.g., a process that clears the ID item before the DML runs).

Once you've gone through these steps, your upload should work, and you can then set up the download functionality by creating a link that references the CONTENT BLOB column, with the FILENAME and MIME_TYPE values to handle the download correctly.

内容的提问来源于stack exchange，提问作者Frankieeee