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

Question

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!

Shows up where as [unsupported datatype]? This sounds like a limitation of your table-viewing tool. — Tony Andrews
– Tony Andrews, Commented Sep 6, 2017 at 15:31
In Oracle Apex when I do a query to see if the record was created. — greg
– greg, Commented Sep 6, 2017 at 15:42

Tony Andrews · Accepted Answer · 2017-09-06 16:07:14Z

1

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.

edited Sep 6, 2017 at 16:07

answered Sep 6, 2017 at 15:52

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

greg Over a year ago

I do already have a page for that and the attached file can't be opened. I have plenty of blob columns in tables throughout this application, but I've never tried to insert blob data via an INSERT statement before and it's not bringing over the attached document properly. It put data into the column, but it's inaccessible or unabled to be opened once downloaded.

greg Over a year ago

Worked beautifully. Thanks a lot!

greg Over a year ago

Tony, this worked, but apex_application_files is a temporary space, sometimes these users go back days later and the file is no longer in apex_application_files. Is there anyway to copy the file that's stored in the table and copy it to another table directly?

Tony Andrews Over a year ago

Yes, that's what my answer is doing: copying blob_content from apex_application_files into your attachments_avail table. You need to do this immediately after uploading the file, not days later. Use another table if you need to.

Collectives™ on Stack Overflow

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

1 Answer 1

Inserting the BLOB

Viewing the BLOB

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Inserting the BLOB

Viewing the BLOB

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related