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

    APEX BFILE

    user56602
      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
          Hi,

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

          Regards

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

            http://apex.oracle.com/pls/otn/f?p=31517:15

            Code > Step 2

            Denes Kubicek
            -------------------------------------------------------------------
            http://deneskubicek.blogspot.com/
            http://www.opal-consulting.de/training
            http://apex.oracle.com/pls/otn/f?p=31517:1
            -------------------------------------------------------------------
            • 3. Re: APEX BFILE
              user56602
              Hello,

              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
                user56602
                Hello,

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

                Thanks!
                • 5. Re: APEX BFILE
                  Carsten Czarski-Oracle
                  Hi

                  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

                  declare
                  v_lob blob;
                  begin
                  -- 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;
                  end;

                  Note that you cannot insert into a BFILE - your target table column must be of the type BLOB

                  Regards

                  -Carsten
                  • 6. Re: APEX BFILE
                    steph0h
                    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!