Mar 16 2011

File Browser in APEX 4 with BLOB column specified in item source attribute

Category: APEXittichai @ 12:45 pm

In APEX 4, we now have the option to place where the uploaded file will be stored within the development workspace interface. The Storage Type setting of the File Browser item has two options:

File Browser Settings

  • Table WWV_FLOW_FILES stores the uploaded file in the table wwv_flow_files (or APEX_APPLICATION_FILES).  This is the default way in the previous versions of APEX. Note that if you still decide to store the uploaded files this way, it is suggested to move record to another custom table after upload, and then clean it up in this table because this is the shared default area (table) where every application will upload files to.
  • BLOB column specified in item source attribute will store the uploaded file in a custom table identified in the Automatic Row Processing (DML) process and the column specified in the item source attribute. This column has to be of data type BLOB.

To configure the File Browser with the BLOB column specified in item source attribute.

1. Create a table for storing uploaded files. One of the columns must be BLOB data type.

CREATE TABLE tbl_attach_file
(
  attach_id          NUMBER PRIMARY KEY,
  attach_data        BLOB,
  attach_mimetype    VARCHAR2(255),
  attach_filename    VARCHAR2(255),
  attach_last_update DATE ,
  attach_charset     VARCHAR2(128),
  attach_user        VARCHAR(10)
);

CREATE SEQUENCE SQ_ATTACH_FILE start with 1;

CREATE OR REPLACE TRIGGER tr_attach_file_BI
   BEFORE INSERT
   ON tbl_attach_file
   FOR EACH ROW
BEGIN
   SELECT SQ_ATTACH_FILE.NEXTVAL
     INTO :NEW.attach_id
     FROM DUAL;
END;
/

2. Create a File Browser item with the following settings.

If this is just simply for file upload, select No on Display Download Link. Otherwise, it will require additional Automated Row Fetch (DML). See note and comment below.

File Browser Settings

3.  The item’s source points to the BLOB column.

File Browser Source

4. Create an Automatic Row Processing  (DML) process.

DML

5. Create a button called Upload with Database Action – SQL Insert Action.

Initially I thought this should be sufficient, but when I ran the page, I got this error.

No DML Error

Apparently I got this error because I checked Yes to Display Download Link thus it requires a DML to fetch data. If this is set to No, it should be okay without the following Automated Row Fetch (DML). Thanks Patrick for pointing that out.

To workaround this, I created another DML but for Automated Row Fetch.

DML Fecth

For a purpose of blogging, we’re not going to run any row fetch, so the DML Fetch will have it disabled (its condition to Never). I’m still not sure why the Automated Row Fetch (DML) is needed (even being disabled) if we just want to upload files. But if it is not there, I got the ORA-2001: No Corresponding DML process shown in step 5.

A quick test shows that the file is uploaded to our custom table.

Upload Test

According to Oracle APEX document, the character set of the BLOB is not automatically set on upload. We will have to create an additional page item which is bound to the character set column. This will be where users can specify the character set for the file they are uploading.

Tags: , , , , ,

50 Responses to “File Browser in APEX 4 with BLOB column specified in item source attribute”

  1. Patrick Wolf says:

    Hi,

    the DML Fetch process is required in your example because you have enabled the “Display Download Link” feature, which requires the fetch process so that it knows where it has to read from. If you set that flag to “No”, the “Automatic Row Processing (DML)” should be sufficient.

    Regards
    Patrick

  2. Mauricio says:

    I created my page following your rules but when I ran the application to save an image it just doesnt Error: ORA-01403:no data found any solution?

  3. Mauricio says:

    Now I run a report to show te file or an omage but it just doesnt appear the rest of the data is ther but the field that contains the blob shows empty – how should i download it to a report

    • ittichai says:

      @ Mauricio how to do display data? Some tools might not be capable of display file content or image. Have you confirm with DBMS_LOB.GETLENGTH? I just want to double check.

      • Mauricio says:

        Thanks ittichai for taking your time finally repeated the report and now it just work know i have another plobem trying to break after a column but i think that may be another thread

      • Mauricio says:

        Ireally need that help I have a table named items with images shirts, skirts, pants, and another table with sizes and another one with colors, The item table has an image of each item but when i join 2 o 3 of this tables de image of course respeat it self for each characteristic I mean size 32
        size 34
        size 38
        is an image on the table items and i would like to show the image with all sizes but the image one time only like this

        size 32
        size 34
        size 38

        Is this possible? How?

  4. Mauricio says:

    Ireally need that help I have a table named items with images shirts, skirts, pants, and another table with sizes and another one with colors, The item table has an image of each item but when i join 2 o 3 of this tables de image of course respeat it self for each characteristic I mean $pants$ size 32
    $pants$ size 34
    $pants$ size 38
    $pants$ is an image on the table items and i would like to show the image with all sizes but the image one time only like this

    $pants$ size 32
    size 34
    size 38

    Is this possible? How?

    • ittichai says:

      @Mauricio I think you’re probably looking at an inline view for size – something like this select $pants$, (select size from size-table where …) from pants-table. But you have to manage how to display sizes in the 2nd column in the concatenated format.

      • Mauricio says:

        I tried to do that
        Select a.image_id, a.item_image, (Select b.color_desc from colors where a.image_id = b.image_id) from items a

        but i got this error:

        ORA-12899: value too large for column “APEX_040000″.”WWV_FLOW_WORKSHEET_COLUMNS”.”DB_COLUMN_NAME” (actual: 63, maximum: 30)

        Unable to create Dynamic Query page.

        what should I do

        • ittichai says:

          Your error indicated that data is too big (63 chars) for column size (30 chars).

          Note that for the inline view (if one image_id has multiple colors), you’ll need to concatenate to return one row (may be using “,” or “new line”), otherwise it will fail.

  5. Mauricio says:

    tHANK NOW I GOT THE RIGHT QUERY IT JUST RETURN ME VARIOUS ROWS IN ONLY ONE STRING NOW MY PROBLEN IS THAT I NEED TO MAKE A FROM TO ENTER THE DATA FOR THESE 2 TABLES HOW IS IT POSSIBLE IF WHEN I CREATE AN ITEM IM JUST ASKED FOR A COLUMN NAME BUT NO FOR A TABLE MY FOR ALREADY SAVES DE IMAGE, THE NAME FOR THE ITEM TABLE BUT NOW HOW CAN A INTRODUCE THE COLOR AND SAVE

  6. Mauricio says:

    Thank u i just got how to show some rows in in one string thats great but now there anothe problem when i use a third table for example
    pants jean blue
    black
    pink
    lino white
    black
    blue
    then the report shows like this

    pants blue black pink jean jean jean

  7. mauricio says:

    I will explain in a more real way
    have 4 tables Items, colors, materials, item_definition
    items have patch cords, optic fiber, switchs, routers
    colors black, white, red, green, gray
    material plastic, cuper, alumnium

    on table item definition there are many rows with exactly each item color and material
    I mean patch cords black plastic
    patch cords white plastic

    switch white null
    switch black null

    optic fiber white plastic
    white aluminium
    i need to show patch cords black, white plastic
    switch black, white
    optic white plastic, aluminium

    but i get patch cords black, white plastic, plastic
    switch black, white
    optic white, white plastic, aluminium

  8. Joel says:

    Hi i need to creat a multi-select fiel in my form but when i try to insert the error is declare

    Invalid numeric value 3:1:2 for column level my idea is a form where yo insert which level has a student passed for example

    Student Level
    Joel 1
    Joel 2
    Joel 3

    so in my form i select the student and in a multi select list select the 3 levels but when i save there goes the error, How can i insert a multirow select list in apex 4

  9. Mauricio says:

    Thank that really helped me, now I need something else when I execute than way to present a multi row registry I get some parameters repeated for example
    I have
    Item Color Fabric
    Jackets yellow jean
    Jackets blue jean
    Jackets black jean
    Jackets black leather

    with a query like that i get

    Jackets yellow blue black black jean jean jean leather

    I need to show yellow blu black jean leather
    Is ther a way to make something like a distinct yin that kind of query?

  10. Mauricio says:

    I am sorry about tghis but I cant find any answer about this either any forum Im trying to work with progress lists but when I create a region list type doesnt follow the sequence why, I´ve listened that some people have problems with this in apex on spanish but thats not my problem my apex is on english but still cant get it work help me please .
    If this theme is for another forum indicate me where should i post this

  11. Alberto says:

    Hi, I really need some help, I have a new tabular form based o an empty table for shipping orders, all the fields from this empty orders table are, of course, called from other tables so i make them all select list but there are som fields that i need to be filled when this selecs chage i mean
    _________________________________________________________
    articule v | seccion v | subseccion v | cuota |
    _________________________________________________________
    select lists articules, seccion and subseccion
    quota is a field on table articules that, when i select and articule the quota should be filled but how i do that

  12. Alberto says:

    I found some code for ajax but dont know how to use it where should i put this code yin the page like a process in the field where

  13. Alberto says:

    That ajax should be helpful if the code were used foe tabular forms I mean with field but its just explain for textfield on a regular form “PXX_#######” that really doesnt help me a need to use de code for report fields on a tabular form

  14. Matt says:

    I have the same file upload. If my form validation fails returning the user to the form, the file upload field is empty…and my user has to re-select a file to be uploaded. Any idea how to avoid this? Thank you.

    • ittichai says:

      Matt,
      I’m not sure how to do that. At least the file browser will stay at the directory where you last selected. My suggestion is that you could try a dynamic action where you save the text every time the selection is changed.

  15. Create an Application to Upload Files Using Oracle APEX, In Less Than 10 Minutes (Video) » Eddie Awad's Blog says:

    […] APEX includes declarative BLOB support that enables you to declaratively upload files in forms, and download or display files in reports (ittichai wrote about it here). […]

  16. Vas says:

    I have followed the same steps but the file I upload gets stored in the wwv_flow_files table and not in the custom table I have defined. Any suggestions to resolve this would be very helpful.

    • ittichai says:

      I assume you choose “BLOB column specified in item source attribute”?

      • Vas says:

        Yes, I chose “BLOB column specified in item source attribute”. However, it always gets stored in the WWV_FLOW_FILES table instead of the custom table I have created.

        Does any setting in APEX prevent us from using custom tables?

        • Vas says:

          I would wonder how I would share the application as its in the intranet. I would try to replicate the same in the Oracle’s Apex instance on the Internet so that you can help me debug the same.

          • ittichai says:

            You can use apex.oracle.com where the workspace can be requested for free. Most people are using it to share samples or collaborate. But keep in mind, with my busy schedule, I might not have a chance to take a look for awhile.

  17. Ray says:

    Hi,

    I am a biz user and not very technical (but am learning as I go.)This was a very easy to understand article. Thank you for laying it out as you did.

    Is there a part 2… How to create the download section so that users and get the uploaded attachments?

    thanks,
    ray

  18. kishore chepuri says:

    Hi,

    This is kishore chepuri. would u please tell the steps for sending the blob data to a pdf in apex with sales force.

  19. kishore chepuri says:

    hi,

    I want to display the pdf as reports . As we generated in salesforce. I am unale to convert into pdf format. Please help for this.

    • ittichai says:

      I believe this has to do with http header information. Please try to add this line and see.

      OWA_UTIL.mime_header (NVL (l_mime, ‘application/pdf’), FALSE);

  20. sree says:

    I am getting “NO Data Error”..How to Resolve this

    • ittichai says:

      I would recommend you to debug your codes using Toad or SQL Developer. Just simply set the group security ID in the tool so you can access APEX from external tool.

  21. Pat Miller says:

    Hi,
    I have tried your example trying to load a .csv file into the Blob column of your example table, but have not been successful. One question I had was, it was not clear to me the source for the Primary key for the DML process definition. It appears to be the Item name for the File Browse item according to your screen shot, but the Item name of the File browse item has a source of the ‘attach_data’ column which is not the primary key of the table.

    Pat

  22. Ashish says:

    hi ,

    I have a java exception stack trace which I have stored in the DB as BLOB. Now, I want to display the BLOB data as text on the oracle apex page/popup.

    I am using a DB link and it oracle apex throws an error if I create a display item for the remote DB.

    Can you please help me here,

  23. ahmed says:

    I want to upload excel and csv file in apex temp table througt file browser is it possible ?

  24. Pat Miller says:

    Hi,
    I have a process which creates files of consolidated configuration data from many machines on our college campus. I now want to automate an upload of this consolidated file from the machine which holds the file. Can this be done using an Apex application which runs a process to upload the file, parse it and then insert into a table?

    Pat

  25. Rajkumar says:

    Hi,

    Urgent….!!!
    Currently i am working on File upload using BLOB Content.
    I am facing an issues with upload button. After browsing my upload file (ex: test.csv) then click on upload button the upload button got disappears. i need another help, can we restrict the upload files only csv. please provide sample code.

    Regards,
    Raj

    • ittichai says:

      Button disappearing may be an issue with your current APEX. Try some other APEX installation or apex.oracle.com

      You can restrict only .csv just simply by validating the file extension.

Leave a Reply