This discussion is archived
6 Replies Latest reply: Nov 8, 2011 3:11 AM by steph0h RSS


user56602 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated

    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 Oracle ACE Director
    Currently Being Moderated
    I think this is described here:

    Code > Step 2

    Denes Kubicek
  • 3. Re: APEX BFILE
    user56602 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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
    steph0h Newbie
    Currently Being Moderated
    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!


  • Correct Answers - 10 points
  • Helpful Answers - 5 points