Problem
I had this data in a BLOB column. I knew that it was a spreadsheet but couldn't understand the format stored.
Solution
Turns out that this is a base64 encoded string. The header is for information purposes, but if you copy the data string, starting "UEsDBBQABgAI...." to this nifty utilty, it displays the spreadsheet perfectly.
In other words, from a pure data perspective, all we're interested in is that long string of data.
The approach is thus:
1. Create a CLOB from a BLOB. (Yours might already be in CLOB format)
2. Strip out the header, so that our clob just starts with "UEsDBBQABgAI...."
3. Decode the base64 and store it as a blob
4. Use it. In my case I wanted to display it, but you can store it or wahatever...
Approach
1. Create a CLOB.
vclobtmp := Clobfromblob(vblob); --Essentially just dbms_lob.Converttoclob
Gotcha: Don't try to work with a blob: Extracting Text from a BLOB field — oracle-tech
2. Strip out the header
v_position := dbms_lob.Instr(vclobtmp, 'Content-Transfer-Encoding: base64',
1, 1
)
+ 33;
dbms_lob.Copy(vclob, vclobtmp, dbms_lob.Getlength(vclobtmp), 1, v_position);
My header was always going to end with Content-Transfer-Encoding: base64, 33 characters long string. Add some more flexibility of you want other ways to find the end of your header.
Gotcha: dbms_lob.Substr really doesn't work well for large clobs. It will just silently return null...
3. Decode the base64.
This was really time consuming because the data is binary after this step.
DECLARE
vclob CLOB;
vclobtmp CLOB;
vblob BLOB;
vmimetype VARCHAR2(1000);
v_position INTEGER;
v_progress VARCHAR2(100);
FUNCTION Clobfromblob (p_blob BLOB)
RETURN CLOB
IS
l_clob CLOB;
l_dest_offsset INTEGER := 1;
l_src_offsset INTEGER := 1;
l_lang_context INTEGER := dbms_lob.default_lang_ctx;
l_warning INTEGER;
BEGIN
IF p_blob IS NULL THEN
RETURN NULL;
END IF;
dbms_lob.Createtemporary(lob_loc => l_clob, CACHE => FALSE);
dbms_lob.Converttoclob(dest_lob => l_clob, src_blob => p_blob,
amount => dbms_lob.lobmaxsize, dest_offset => l_dest_offsset,
src_offset => l_src_offsset, blob_csid => dbms_lob.default_csid,
lang_context => l_lang_context, warning => l_warning);
RETURN l_clob;
END;
BEGIN
dbms_lob.Createtemporary(lob_loc => vclob, CACHE => TRUE,
dur => dbms_lob.call);
dbms_lob.Createtemporary(lob_loc => vclobtmp, CACHE => TRUE,
dur => dbms_lob.call);
dbms_lob.Createtemporary(lob_loc => vblob, CACHE => TRUE,
dur => dbms_lob.call);
SELECT part_data,
Substr(part_type, 1, Instr(part_type, ';') - 1)
INTO vblob, vmimetype
FROM (your table)
WHERE .....;
v_progress := 'blob to clob';
vclobtmp := Clobfromblob(vblob);
--Strip the header from the attachment data
v_position := dbms_lob.Instr(vclobtmp, 'Content-Transfer-Encoding: base64',
1, 1
)
+ 33;
dbms_lob.Copy(vclob, vclobtmp, dbms_lob.Getlength(vclobtmp), 1, v_position);
v_progress := 'decodebase64';
vblob := apex_web_service.Clobbase642blob(vclob);
sys.htp.init;
v_progress := 'mime_header';
sys.owa_util.Mime_header(vmimetype /*'application/octet-stream'*/, FALSE
/*,'UTF-8' */);
sys.htp.P('Content-length: '
|| sys.dbms_lob.Getlength(vblob));
sys.htp.P('Content-Disposition: attachment; filename="'
||'Mason3.xlsx'
||'"');
sys.owa_util.http_header_close;
v_progress := 'download_file';
sys.wpg_docload.Download_file(vblob);
dbms_lob.Freetemporary (vblob); --do not forget!!
apex_application.stop_apex_engine;
EXCEPTION
WHEN no_data_found THEN
NULL;
WHEN OTHERS THEN
Raise_application_error(-20000, 'Fetch attachment: '
||v_progress
||' - '
||SQLERRM);
END;
Comments
Post a Comment