File Upload Improvements in APEX 5.1

Updated 10/10/2017 now that APEX 5.1 has been out for a while.

file_upload_5_1_ea

The standard File Upload item type is getting a nice little upgrade in Apex 5.1. By simply changing attributes on the item, you can allow users to select multiple files (from a single directory) at the same time.

In addition, you can now restrict the type of file they may choose, according to the MIME type of the file, e.g. image/jpg. This file type restriction can use a wildcard, e.g. image/*, and can have multiple patterns separated by commas, e.g. image/png,application/pdf.

file_upload_5_1_ea_demo

Normally, to access the file that was uploaded you would query APEX_APPLICATION_TEMP_FILES with a predicate like name = :P1_FILE_ITEM. If multiple files are allowed, however, the item will be set to a colon-delimited list of names, so the suggested code to get the files is:

declare
  arr apex_global.vc_arr2;
begin
  arr := apex_util.string_to_table(:P1_MULTIPLE_FILES);
  for i in 1..arr.count loop
    select t.whatever
    into   your_variable
    from   apex_application_temp_files t
    where  t.name = arr(i);
  end loop;
end;

You can play with a simple demo here: https://apex.oracle.com/pls/apex/f?p=UPLOAD_DEMO&c=JK64 . (UPDATE 10/10/2017: recreated demo on apex.oracle.com) If you want to install the demo app yourself, you may copy it from here.

If you want to support drag-and-drop, image copy&paste, load large files asynchronously, or restrict the maximum file size that may be uploaded, you will probably want to consider a plugin instead, like Daniel Hochleitner’s DropZone.

Interactive Grids (APEX 5.1 EA) and TAPIs
Send SMS, MMS and Voice messages from Oracle PL/SQL

Comments

  1. The link to the demo does not work….

    • That’s because it’s on the Early Access site which gets wiped from time to time. I suspect 5.1 will be released soon though 🙂

  2. Thanks!
    But how to view/display a pdf file from report?

  3. Thanks for showing these new attributes off.

    Do you have any idea if they’ve enhanced the display of the resulting tag so it will display file names longer than 30 characters? I’ve found that annoying for a long time.

  4. Hi Stew,

    I believe you can change it by setting the width attribute, although I’m not sure if this works the same across all browsers.

    e.g. in my demo I’ve set Custom Attributes to style="width:500px"

    Jeff

  5. houshmand rastin
    11 October 2017 - 1:28 pm

    hi,

    why on upload file not show image or preview befor insert into table?
    or how show preview image for upload file?

    • Hi houshmand rastin,

      If you would like to show a preview of the image, I recommend using the DropZone plugin which has this feature built-in.

      Cheers,
      Jeff

  6. How can i get the code of the application ?

  7. Hi, great work. Thanks lot.

    I suggest to use unique identifier number for ID

    l_random_file_id :=to_number(sys_guid(), ‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX’);

    if you want the UUID in the canonical format 8-4-4-4-12 36 characters
    this functions may be useful.

    CREATE OR REPLACE function OPEN_PA.uuidN_to_uuid ( pNumber in number) return PA_DEFS.UUID36_TYPE is
    –PA_DEFS.UUID36_TYPE is varchar2(36 byte) parameter.
    begin
    return trim(lower( regexp_replace((to_char(pNumber,’0XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX’)),'([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})’, ‘\1-\2-\3-\4-\5′)));
    exception
    when others then
    return null;
    end ;
    /

    CREATE OR REPLACE function OPEN_PA.uuid_to_uuidn ( puuid in varchar2) return number is
    begin
    return to_number ( replace(puuid,’-‘,”),’XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX’ );
    exception
    when others then
    return null;
    end ;
    /

  8. Hi,

    Nice one,

    Can we implement download button for each row in Interactive grid like IR? If we can, please suggest..

    Thank you.

  9. How do you clear out the Interactive grid on each successive “upload”?? I would think that APEX_APPLICATION_TEMP_FILES needs to be deleted before each “upload”??

    • Jeffrey Kemp
      18 May 2018 - 7:58 am

      Hi Larry,

      If you want to clear out the temp table you can just add a PL/SQL process that runs DELETE APEX_APPLICATION_TEMP_FILES;.

      In my application, I haven’t bothered because this table should get automatically purged some time after the session has ended.

      Jeff

  10. I’m fairly new to APEX so bear with me…

    When I add a process to “delete” the data it seems to delete the “temp” files before it has a chance to load them. I want the “temp” files delleted after they’re loaded into the grid.

    Where would I put the “delete” statement??

    • Jeffrey Kemp
      22 May 2018 - 6:21 pm

      Hi Larry,

      Check the order in which the processes are run – make sure your delete occurs after submit, after your process runs which copies the files to their intended destination.

      If you’d like more assistance, please recreate your process on apex.oracle.com and I may be able to help you. Alternatively, describe exactly what you mean by “loading the files into the grid”.

      Jeff

  11. Hey Jeff, sorry, I’ve not been able to get back to your question.

    I’m using your program ” File Upload Demo – Demo (Native))

    which after you select a file from the file browser, press upload, that file is put into the Interactive Report(IR) “APEX_APPLICATION_TEMP_FILES” via the select statement “select * from apex_application_temp_files”.

    Let’s say I select one file only and press “Update”. That one file is loaded into the IR. I then select another file(only 1 file) and press “Update”. That file is ALSO added to the IR BUT now there are two records.

    I only want the latest record to display in the IR. How do you “clear/purge/refresh” the IR to remove the “first” record and ONLY display the latest record.

    Thanks, Larry

    • Jeffrey Kemp
      7 June 2018 - 8:28 am

      Hi Lary,

      In that case I would question why you need to report from the apex_application_temp_files table in the first place.

      The reason the table allows multiple records is because the user may be allowed to upload multiple files in one go.

      Normally you would design the process so that it copies the files out of apex_application_temp_files, storing them in your own database table(s), and then delete them from the apex_application_temp_files table. That way the temp table is clear when the user wants to upload more file(s).

      Alternatively, if you wish to use the apex_application_temp_files table as a sort of “preview” area before the user “processes” the files, you could add a “Clear” button which the user may click to clear out the table. This button would invoke a process which deletes the rows from apex_application_temp_files.

      Jeff

  12. Jeff, that is close to what I want to do.

    I plan to load multiple files into my table.

    Let’s say a user tries to load 3 files into apex_application_temp_files which will call a “page submit” process to try and insert into my table. If 1 of the 3 files is incorrect(wrong extension or something), I want to display the 3 files on an IR so the user knows which files they tried to upload. The user will need to re-select the 2 files that are “good” and then insert them. I plan on deleting the apex_application_temp_files at that time. I believe as it works now, the IR will show the 3 files from the first upload try and 2 more files on the second upload attempt thus producing 5 rows in the IR.

    Somehow(Dynamic Action??), I want to clear/purge the IR when the user presses the “Upload” button so I know the IR is empty before trying to display any apex_application_temp_files .

    By the way, I did a brief Dynamic action to try and insert the records from apex_application_temp_files but it seemed that those “files” were NOT stored there unless I did a “Page Submit”. Does this seem correct??

    Thanks

    • When the user clicks “Upload”, if it is doing a page submit then you should be able to do whatever you need to do (e.g. clear out apex_application_temp_files) as a normal on page submit process. You shouldn’t need a DA for that.

      Yes, the files don’t get uploaded and stored unless you issue a page submit.

  13. Hi Jeff

    This is great, it helped me a lot. I was able to upload multiple files into my custom table using your suggested code. I am very grateful.

Leave a Reply

Your email address will not be published / Required fields are marked *