How to upload file as blob to my table | Oracle Apex |

Question

I have been trying since yesterday no where I am able to find a proper post how to upload file as blob to table.

Below is my table :

create table upload_file
(
  fileblob  blob,
  filename  varchar2(250),
  mimetype  varchar2(250),
  createDate date
);

I have created a page name as Testing and Added below items

P1_CHOOSE_FILE  [ file browse ]
P1_MIMETYPE     [ Hidden ]
FILE_NAME       [ Hidden ]
CREATED         [ Hidden ]

On P1_CHOOSE_FILE , I set a property called : Blob column specified in Item source attribute and value required as ON

Then created one Button with Dynamic action

Now my biggest challenge is How to insert my choose file to my upload_file table using PL/SQL code

I tried report with form but it does not work for me in APEX 20.x due to restrictions setting done to it. So want to achieve using PL/SQL code.

Please demonstrate with image and code and how to achieve it

Answer 1

Not sure if it will help you, but here's how I did on my application :

My table :

CREATE TABLE inv_tb_document(
    pk_document INT, --primary key
    nom_document VARCHAR(255) CONSTRAINT ct_nn_nom_document NOT NULL, --document name
    blob_document BLOB CONSTRAINT ct_nn_blob_document NOT NULL, --blob column
    mimetype_document VARCHAR(255) CONSTRAINT ct_nn_mimetype_document NOT NULL, --mimetype column
    charset_document VARCHAR(255), --charset column
    commentaire_document VARCHAR(75) CONSTRAINT ct_nn_commentaire_document NOT NULL, -- irrelevant for your case
    date_document TIMESTAMP(8) CONSTRAINT ct_nn_date_doducment NOT NULL, --irrelevant too
    CONSTRAINT ct_pk_document PRIMARY KEY(pk_document)
);

Now, here's what I did for my form :

Here are the attributes of my File Browser Item :

---

I don't have any custom code to insert the document on my tables, I hope this could help you anyway.

Answer 2

not an exact answer to your specific question, but if you change Storage Type as Table APEX_xx_Temp. you could go like this:

(select filename from apex_application_temp_files  where name = :P1_CHOOSE_FILE)
,(select mime_type from apex_application_temp_files  where name = :P1_CHOOSE_FILE)
,(select blob_content from apex_application_temp_files  where name = :P1_CHOOSE_FILE)