I have a column of BLOB data that is a mix of ASCII and image data. I am populating the data via python by reading files in a binary manner:
file_content = open(current_file,'rb').read()
mycursor.execute(''' insert into file set blob_file_content = :BLOB_FILE_CONTENT where file_id = :FILE_ID '''
, BLOB_FILE_CONTENT = file_content
, FILE_ID = file_id)
When certain files display, they are truncated in my APEX app (sorry for the major redactions):
When the Download button is clicked, the file opens correctly in a native app:
You can see the text where the APEX app truncates it is: Donít.
The Download button calls the following PL/SQL procedure:
create or replace PROCEDURE get_blob_file (i_file_id IN VARCHAR2) IS
v_doc_name file.file_name%TYPE;
v_blob BLOB;
BEGIN
SELECT
blob_file_content
, file_name
INTO v_blob,
v_doc_name
FROM file
WHERE file_id = i_file_id;
sys.htp.init;
sys.owa_util.mime_header('application/json',false);
sys.htp.p('Content-length:'||sys.dbms_lob.getlength(l_blob));
sys.htp.p('Content-Disposition: attachment; filename="' || v_doc_name ||'"');
sys.htp.p('Cache-Control: max-age=3600');
sys.owa_util.http_header_close;
sys.wpg_docload.download_file(v_blob);
apex_application.stop_apex_engine;
EXCEPTION
WHEN apex_application.e_stop_apex_engine THEN
NULL;
WHEN OTHERS THEN
HTP.p('Something went very very wrong');
END;
The APEX app displays the text via the following SQL:
select
utl_raw.cast_to_varchar2(dbms_lob.substr(f.blob_file_content)) as file_content
, f.file_name
from file f
inner join file_extensions fe on fe.type_code = af.file_type_cd
and fe.meaning in ('TXT','CHECKSUM')
where f.parent_id = :P8_PARENT_ID
and f.blob_file_content is not null
If I run that same statement in SQL Developer, I get the full content of the file back, with � in place of the í. While not ideal, this is better than the truncated display.
Is there a way that I can tell utl_raw.cast_to_varchar2 to deal with these types of encoding errors better? Thanks for any assistance!