Error: SyntaxError: JSON.parse Error: Invalid character at position:1 in pl/sql

Question

I am getting this error:

Error: SyntaxError: JSON.parse Error: Invalid character at position:1

on executing the below code in oracle apex5.2 during submit page, below code is for downloading a file using pl/sql in oracle apex.

I created a button; when we click that button the below code will execute and submit page also will happen.

Declare
    dest_loc11  BLOB := empty_blob();
    dest_loc    BLOB := empty_blob();
    dest_loc2   BLOB := empty_blob();
    dest_loc3   BLOB := empty_blob();
    dest_loc4   BLOB := empty_blob();
    dest_loc5   BLOB := empty_blob();
    dest_loc6   BLOB := empty_blob();
    src_loc     BLOB := empty_blob();
    l_zip_file  blob;
    v_length    integer;
    v_inp       varchar2(32767);
    v_count     number;
    n           number:=1;
    v_pr1_idx   number;
    v_pr1_idx2  number;
    V_ID        NUMBER;
    v_pr_1      varchar2(32767):='130614';
    V_RES       VARCHAR2(32767);
    V_PRINT     VARCHAR2(32767):='';
    V_PROMPT    VARCHAR2(32767);
    V_PRINT_RAW BLOB;
    csv_file    utl_file.file_type;
    V_NO_DATA   varchar2(32767);
    v_pr_11     varchar2(32767);

BEGIN       
    DBMS_LOB.CREATETEMPORARY(
        lob_loc => dest_loc11,
        cache   => true,
        dur     => dbms_lob.session
      );
    DBMS_LOB.OPEN(dest_loc11, DBMS_LOB.LOB_READWRITE);

    V_PRINT:='udfhsdhgfszhduhjsdzvcjhzxjcvhzxc';

    V_PRINT_RAW := utl_raw.cast_to_raw( V_PRINT );

    v_length := dbms_lob.getlength(V_PRINT_RAW);

    DBMS_LOB.WRITEAPPEND (
        lob_loc   => dest_loc11,
        amount    => v_length,
        buffer    => V_PRINT_RAW);

    DBMS_LOB.CLOSE(dest_loc11);
    sys.htp.init;

    sys.owa_util.mime_header( 'text/plain', FALSE );

    sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( dest_loc11));

    sys.htp.p('Content-Disposition: attachment; filename="' ||'bala.sql' || '"' );

    sys.htp.p('Cache-Control: max-age=3600');-- tell the browser to cache for one hour, adjust as necessary

    sys.owa_util.http_header_close;

    sys.wpg_docload.download_file( dest_loc11);

    DBMS_LOB.FREETEMPORARY (dest_loc11);
    apex_application.stop_apex_engine;
end;

Are you sure it's coming from that code - it doesn't contain any JSON handling? What is the full error stack you see? — Alex Poole, Mar 01 '18 at 10:50
Yeah, I've seen this error, but often relates to bad AJAX definition, not directly from your code. Check the debug log for specific errors. — Scott, Mar 02 '18 at 02:55

score 1 · Accepted Answer · answered Apr 27 '18 at 13:53

You need to set Reload on Submit to Always at page level in the page designer. Then you can successfully download the file. Also your code has a chance of raising Value error when v_length is less than 1 . Make sure you eliminate appending the text to the blob when v_length is outside the limits 1-32767

score 0 · Answer 2 · answered Mar 05 '18 at 02:59

It sounds like you have a PL/SQL Dynamic Action that fires when the button is clicked, then that downloads your file? Nope, you can't download a file via Ajax.

Download a file by jQuery.Ajax

What you need to do is redirect the user to a different page, or back to the current page with a different REQUEST value, so you know you're supposed to be downloading the file. Then, put your code in a Before Header process on that page.

Error: SyntaxError: JSON.parse Error: Invalid character at position:1 in pl/sql

2 Answers2