I have to store graphical documents in an Oracle Database using Apex. I have designed an application based on the Sample Application for Customers, Product, Orders Page 3 and 6 using apex_util.get_blob_file_src and a File Browse Item to store the document in a BLOB field. That works perfectly.
Now I had to change the application to store the BLOB content in a file and set a pointer in a BFILE column leaving the BLOB column empty. Therefore I wrote a view converting BFILE to BLOB and some instead of triggers to convert BLOB to BFILE.
Then I rewrote my Apex pages using the view instead of the table. It doesn’t work. I noticed that when using the table Apex is first executing an INSERT leaving the BLOB empty and after that an UPDATE to fill the BLOB column when creating a new record. When using the view instead of the table the INSERT is executed but not the UPDATE.
So I wanted to write a page process to update the table manually. But I found that there was no entry in the view APEX_APPLICATION_FILES generated by Apex. So I have no chance to obtain the BLOB value and use it to update my table.
What can I do to have the BLOB content written by Apex to the table the view APEX_APPLICATION_FILES is selecting from?
an important thing for your application is the nature of BFILEs - they're read only.
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10645/adlob_intro.htm#i1006165 BFILEs are read-only data types. The database allows read-only byte stream access to data stored in BFILEs. You cannot write to a BFILE from within your application.
So why are you wanting to use BFILEs instead of BLOBs?
in most cases BLOBS are the better choice - you have simplified Backup & Recovery, data
integrity and many other advantages ...
WWV_FLOW_FILES uses APEX security mechanisms - so you cannot see any entry when selecting it
from e.g. SQL*Plus. If you want to check the entries you need to use SQL Workshop.
If you create a File Browse Item named P1_FILE you can select the BLOB content with
-- get uploaded content
select CLOB_CONTENT into v_lob from wwv_flow_files where name = :P1_FILE;
-- insert into own table
insert into ...
-- remove from WWV_FLOW_FILES after copying
delete from wwv_flow_files where name = :P1_FILE;
Note that you cannot insert into a BFILE - your target table column must be of the type BLOB