6 Replies Latest reply on Nov 8, 2011 11:11 AM by steph0h


      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?
        • 1. Re: APEX BFILE
          Carsten Czarski-Oracle

          an important thing for your application is the nature of BFILEs - they're read only.

          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 ...


          • 2. Re: APEX BFILE
            Denes Kubicek
            I think this is described here:


            Code > Step 2

            Denes Kubicek
            • 3. Re: APEX BFILE

              I want to write an application with an XE-db, but I want to upload a lot of files.
              Something like an picture gallery.

              thanks for your help!
              • 4. Re: APEX BFILE

                the problem ist that there is no entry in wwv_flow_files even when using the table and inserting into the BLOB column.

                • 5. Re: APEX BFILE
                  Carsten Czarski-Oracle

                  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

                  v_lob blob;
                  -- 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


                  • 6. Re: APEX BFILE
                    This is a real show-stopper. I was cheking my upload-page for an hour now before finding out through your posting that I just looked the wrong way ... thanks!