File Upload Improvements in APEX 5.1

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

Updated 22/8/2020 with some corrections.

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.

NOTE (22/8/2020): this doesn’t stop a user from choosing files of other types (they can change the file type selector) and doesn’t validate the file types.

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, restrict the actual file type or 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.

  14. Restriction e.g. to Excel (XLSX)
    Under “Setting” set “File Types” with the following content to restrict file browse to XLSX only.

    accept=”.xls,.xlsx, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel

    @See source: https://w3c.github.io/html/sec-forms.html#element-attrdef-input-accept

  15. Many thanks for this clear article !

  16. Hi!
    I’m trying to use the upload method to send e-mails with attachments (without saving them), but I’m stucked. Could you please help?

    The procedure:

    create or replace procedure email_sample(
        l_id number,
        p_from in varchar2,
        p_body in varchar2,
        arr apex_application_global.vc_arr2
    )
    as
    begin
        arr := apex_util.string_to_table(v('P13_UPLOAD'));
        l_id := apex_mail.send (
             p_from => p_from,
             p_to => 'XYZ',
             p_subj => 'ABC',
             p_body => p_body,
             p_body_html => p_body
        );
        FOR c1 IN (
            SELECT filename, blob_content, mime_type 
            FROM apex_application_temp_files
        ) LOOP
            APEX_MAIL.ADD_ATTACHMENT(
                p_mail_id    => l_id,
                p_attachment => c1.blob_content,
                p_filename   => c1.filename,
                p_mime_type  => c1.mime_type);
            END LOOP;
        DELETE APEX_APPLICATION_TEMP_FILES;
    end email_sample;
    
    
    Compilation failed,line 9 (10:24:33)
    PLS-00363: expression 'ARR' cannot be used as an assignment targetCompilation failed,line 9 (10:24:33)
    PL/SQL: Statement ignoredCompilation failed,line 10 (10:24:33)
    PLS-00363: expression 'L_ID' cannot be used as an assignment targetCompilation failed,line 10 (10:24:33)
    PL/SQL: Statement ignored
    
    • Hi Kata,

      The error PLS-00363 is telling you that you are trying to assign a value to a parameter that is an IN parameter for your procedure. I think you meant to declare l_id and arr as local variables, e.g.:

      create or replace procedure email_sample(
          p_from in varchar2,
          p_body in varchar2
      )
      as
          l_id number;
          arr apex_application_global.vc_arr2;
      begin
          arr := apex_util.string_to_table(v('P13_UPLOAD'));
          l_id := apex_mail.send (
      ...
      
  17. Hi, I have problem where in IE, the file path gets displayed in the field rather than just the file name like it does in Chrome. Have you got any idea of how to fix this issue?
    Thanks

    • I’m pretty sure that’s not an issue per se, it’s just how IE has implemented the file browser item. I don’t know of any way to customise how it works.

  18. Virikshit SEMBHOO
    7 July 2020 - 7:26 pm

    Works Perfectly. Thank you.

  19. Hi Jeffery,

    How are You , Very Good by Grace of God.

    I am working as oracle forms/report developer. and I have very good knowledge on Importing Data with Validation in 6i but same thing
    I want to do in Oracle Apex 19/20
    for e.g. I want to Upload
    EMPNO and His Salary and update EMP Table,
    whatever EMPNO in CSV file,
    1) I want to Check that employee number exists in EMP table
    2) SAL field value is Number ?
    First above 2 points to validate and Update EMP table.

    Please Help me How to do it in Simple Sql Statement.

    From Oracle Apex I got below Query but how to read
    apex_application_temp_files
    ************************************
    select line_number, col001 AS EMPNO, col002 as SAL
    from apex_application_temp_files f,
    table( apex_data_parser.parse
    (
    p_content => f.blob_content,
    p_add_headers_row => ‘N’,
    p_max_rows => 500,
    p_skip_rows => 1,
    p_store_profile_to_collection => ‘FILE_PARSER_COLLECTION’,
    p_file_name => f.filename ) ) p
    where f.name = :IMPORT_FILE_NM ;

    Please resolve it for me and for your well wisher Viewer.

    Thanks
    Mahendra (samarthsagar107@gmail.com)

    • Hi Mahendra,

      If the IDs in the CSV are unique, a MERGE is probably the easiest way to build this, e.g.:


      merge into emp tgt
      using (
      select line_number, col001 AS EMPNO, col002 as SAL
      from apex_application_temp_files f,
      table( apex_data_parser.parse
      (
      p_content => f.blob_content,
      p_add_headers_row => ‘N’,
      p_max_rows => 500,
      p_skip_rows => 1,
      p_store_profile_to_collection => ‘FILE_PARSER_COLLECTION’,
      p_file_name => f.filename ) ) p
      where f.name = :IMPORT_FILE_NM
      ) src
      on (tgt.empno = src.empno)
      when matched then update set
      tgt.sal = to_number(src.sal);

  20. I am having an issue while file uploading in apex. I want to restrict the upload only to the pdf format.

    By using the file type, during browse I am able to get only the pdf files but when I select all files, I get all the files of other formats and when I select them I am able to upload and store the file into the database but I dont want this. If I select any other format, it should either give the error or should not upload the file.

    I am using 5.1.4.00.08 version.

    Kindly help me with this.

    • Hi Khyati,

      Thanks, I never noticed that the file browser allows the user to override the file types indicated by the item. Since the file type is never validated setting the mime types is apparently more of a preference rather than a restriction.

      If you want to enforce the file types allowed, I suggest you try the Dropzone plugin, which does validate the file type after it is uploaded (even if the user overrides the file browser).

      Thanks for your note – I’ve updated the post accordingly.

      Jeff

  21. Can you please create a quick demo with DB table and Image file uploaded like Employee Profile Master: emp_name, emp_id, emp_photo, emp_joiningdate kind of simple form where all feilds are mandatory to post. Can you please create a sample CRUD for this for the demo purpose in APEX 20 latest version.

    Also at the end show Image in grid listing.

  22. where do you want to keep it in the apex oracel cloud where do you store the image, I mean where is the image data storage because I experienced an error when my system uploaded a pdf file but the data type in the db is varchar not a blob, because my expectations are stored in the db is the upload link so that it can be accessed but apparently it doesn’t enter the apex oracle db, please help friends

Leave a Reply

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