Thursday, December 07, 2017

Errors when downloading a file on page submit in Oracle Application Express 5.1 or later...

Recently, Sharon Kennedy from our team approached me for some help with file download in Oracle Application Express (APEX).  Sharon is the primary developer of Oracle Live SQL (among many of her other responsibilities), and she wanted to initiate a file download in a page process, after page submission.  Since I've done this 100 times in APEX applications, should be easy, right?

Back in 2014, I wrote a short blog post showing how to generate a link to download a file from a BLOB stored in a table.  But this problem was slightly different.  The application flow was:

  1. In Oracle Live SQL Administration, an administrator would click the button "Download Oracle Content"
  2. The page would then be submitted, and a PL/SQL page process would fire, which would query all of the static scripts and tutorials from Live SQL, zip them up using APEX_ZIP, and initiate a file download.

However, when the button was clicked, the page would be submitted, no file download would be initiated, and the following error was displayed on the page:

Error: SyntaxError: Unexpected token r in JSON at position 0

After spending more than an hour debugging the Live SQL application, I resorted to a simpler test case.  I created a trivial application with a button on the first page, which would submit and invoke the PL/SQL page process:

    l_file_blob blob;  
    l_file_name apex_application_files.filename%type;  
    l_file_mimetype apex_application_files.mime_type%type;  
    select blob_content, mime_type, filename into l_file_blob , l_file_mimetype , l_file_name from apex_application_files where id = 2928972027711464812;     
    sys.owa_util.mime_header( l_file_mimetype , false );  
    sys.htp.p('Content-Disposition: attachment; filename="' || l_file_name ||'"');  
    sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( l_file_blob ));  
    sys.wpg_docload.download_file( l_file_blob );  
    -- Stop page processing  
    apex_application.stop_apex_engine ;  

With my test case, it was exactly the same error encountered, the meaningless error message of "Error: SyntaxError: Unexpected token r in JSON at position 0".

I finally gave up and contacted Patrick Wolf on the APEX product development team, who helped me solve this problem in one minute.  Granted...Patrick was both the creator of the problem and the creator of the solution!

To resolve this problem:

  1. Open the page in Page Designer in Application Builder
  2. Edit the page attributes
  3. In the Advanced section of the page properties on the right hand side of Page Designer, change "Reload on Submit" to "Always" (changing it from "Only for Success" to "Always")
That's it!

Setting "Reload on Submit" to "Always" will POST the page and render the result using the behavior as it was in APEX 5.0 and earlier.  In APEX 5.1, if Reload on Submit is set "Only for Success" (the default), it will use the new optimized page submission process, and expect a specifically formatted JSON result returned from the APEX engine.  Obviously, when I employ a page process which overrides the HTP buffer and emit binary content (instead of a well-formed JSON result), the libraries on the page don't know how to deal with that, and thus, results in this obtuse "Unexpected token r..." message.


Saeed Hassanpour said...

Hi Joel,
Two weeks ago, it occurred this error when I want to download file and I tried several hours and eventually solve it by exactly this solution (change "Reload on Submit" to "Always") but I have not known what happened.

Thanks to explain this process.

Unknown said...

I have a 5.1 page with 2 IG regions and 1 static-content region. Within the static-contact region I have a button that does the download and gets the "Error: SyntaxError: Unexpected token % in JSON at position 0".

When you change the page to "Always" and then run the page, you get:
For an Editable Interactive Grid, the Page attribute 'Reload on Submit' must be set to 'Only for Success'.

Is there something else I can do to make the download work on my page?

Joel R. Kallman said...

Hi "Unknown",

If you have an Interactive Grid on the page, then your best option is to download the file via a link like I blogged about earlier:


Unknown said...

Hi Joel,

The same error occured with Apex 5.0.3 and the fix proposed works with 5.1 (change "Reload on Submit" to "Always")
Any idea for latest version?


Joel R. Kallman said...

Hi Matthieu,

I'm sorry, but I don't understand what you mean by "latest version". Can you please clarify?