CODEWITHSUNDEEP
oracle-databaseplsqloracle-apexoracle-apex-5
greg
greg

Reputation: 85

PL/SQL Process to upload file into BLOB column of a different from apex form

Currently had a request to automatically add data in a form window into two tables at once. I was able to accomplish this with simple insert functions as processes, but for the life of me I can not figure out how to get the attached file in the file browse to attach to the other table using am insert function. The blob column always shows up as [unsupported datatype].

Here's my current insert code, let me know I'm being an idiot and missing something simple.

    insert into ATTACHMENTS_AVAIL ("ADDED_FILE", "MIMETYPE", "FILENAME", "CONTRACTOR_ID", "DATE_ADDED", "TYPE") 
values 
(:P159_RESUME,
:P159_MIMETYPE,
:P159_FILENAME,
:P159_CONTRACTOR_ID,
sysdate,
'Resume');

ADDED_FILE is the blob column and :P159_RESUME is the file browse form.

Thanks again!

Upvotes: 0

Views: 4709

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132580

Inserting the BLOB

Your insert statement isn't getting the BLOB data, just the APEX file ID. You need to do something more like this:

insert into ATTACHMENTS_AVAIL ("ADDED_FILE", "MIMETYPE", "FILENAME", "CONTRACTOR_ID", "DATE_ADDED", "TYPE") 
select blob_content,
       :P159_MIMETYPE,
       :P159_FILENAME,
       :P159_CONTRACTOR_ID,
       sysdate,
       'Resume'
from apex_application_files where name = :P159_RESUME;

In fact, apex_application_files has other columns to tell you the mime type etc.

Viewing the BLOB

You are trying to view the BLOB data in APEX's SQL Workshop. That can't display BLOBs (it just shows "[unsupported datatype]" as you found), but that doesn't mean the BLOB data is invalid. A BLOB can contain anything - music in MP3 format, picture in JPEG, video in MP4, Microsoft Word document, etc. etc. No tool can "display" all of these.

If you create a new page with a report on it to show the data, you can set the BLOB column's Type to Display Image if the BLOB always contains an image, or to Download BLOB if it may contain non-image data.

Upvotes: 1

Related Questions