How do I download a blob from a table to a file on the user's local computer using a stored procedure?

Question

My team has a table with documents stored as BLOBs. Our customers need to be able to download the documents to their local hard drives. During a major upgrade last fall, this stopped working. I've searched a number forums and posts and have not found anything that helps.

We have an existing stored procedure that uses wpg_docload.download_file( l_lob ); to do the download. Now my boss has tasked me to "Find another way". By not working, it opens a dialog to request a file name to save to, creates the file on disk but does not download any data into the file. So we are left with a file with 0 bytes in it. As far as I can tell, it is failing silently without any error messages.

The documents can be a number of types, including (but not limited to) .pdf, .docx, .xlsx, .txt, etc. All act the same.

After some research I tried the following:

(Apologies if the formatting is screwed up. I can't get it to look correct as I am attempting to edit.

` owa_util.mime_header (v_mime_type, FALSE); htp.p('Content-Disposition: attachment; filename="' || p_filename); owa_util.http_header_close;

    for i in 1 .. ceil(DBMS_LOB.GETLENGTH(l_data) / v_buf_len) loop
        v_varchar := dbms_lob.substr(l_data, v_buf_len, v_start);
        htp.prn(v_varchar);
        v_start := v_start + v_buf_len;
    end loop;`

Where the mime type simply comes from the file name extension. Files may be .pdf, .docx, .doc (this code goes back a long long ways), .xlsx.

When I run this I get data to my local disk. But the document cannot be opened. The app I am using to open complains that the file is not in the correct format or has been corrupted.

After further research I tried convert the BLOB to a CLOB with the following code:

owa_util.mime_header (v_mime_type, FALSE); htp.p('Content-Disposition: attachment; filename=' || p_filename); owa_util.http_header_close; `
dbms_lob.createTemporary(clob_data, false);

    for i in 1 .. ceil(DBMS_LOB.GETLENGTH(l_data) / v_buf_len) loop
        v_varchar := utl_raw.cast_to_varchar2(dbms_lob.substr(l_data, v_buf_len, v_start));
        dbms_lob.writeappend(clob_data, length(v_varchar), v_varchar);
        dbms_lob.write(clob_data, length(v_varchar), 1, v_varchar);
        htp.prn(v_varchar);
        v_start := v_start + v_buf_len;
    end loop;

` This throws an exception Error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small

What is the proper way to get a blob from a table and send to a file on the user's local disk?

Some more specific information on the upgrade:

App Previous Now Oracle 12 19 Weblogic 10.3.6 12.2.1.4 Apache OHS 11.9 2.4 Mod_owa N/A 2.11.10 ORDS N/A 19.2

I am a software developer, not a sysadmin, so I do not really understand the ins and outs of these various parts of the system.