This content has been marked as final. Show 2 replies
Hi Tomas,1 person found this helpful
I'm sorry it took some time to prepare an answer for you...
Excel (at least Excel 2003) wants iso-8859-1 encodingThis hasn't changed with 2007 as far as I've experienced it - it still doesn't like UTF-8 in CSV files unless you use the import function.
So my procedure uses convert() to go from database charset UTF8 to WE8ISO8859P1.I've experimented a lot on that issue. If you ever have to deal with EURO signs (and a few other specialities) I'd consider WE8ISO8859P15.
Anyway, since you say you use a procedure, I assume you aren't using the APEX standard CSV function for export, right? We had similar issues with UTF8-Character sets on OHS, but no problems with standard CSV export on APEX Listener (we received ANSI file encoding as requested by the client) so I came to the conclusion this is not an APEX Listener specific issue, but has to be something in the way we build our custom export process using htp.p and owa_util.
I'm not familiar with Portal and APEX, but I assume that Portal uses mod_plsql and set PlsqlNLSLanguage to a Windows charset, e.g. AMERICAN_AMERICA.WE8MSWIN1252 or even your local territory. I guess this affects the output stream handling, but it's not the recommended way to run APEX. Since APEX has been "renamed" from HTMLDB to APEX, the installation guide requires AL32UTF8 as NLSLanguage parameter, and this is what the APEX Listener enforces by not giving you any option on that. But as I said before, we had the same problems with exports on OHS, so we sometimes ignored the installation guide to get proper files.
Without that option, there is one option that definetly works and one that might work, but I haven't implemented it (yet). So I start with the working one: Prepare a blob and download it as complete file using WPG_DOCLOAD. I'm not sure, but I guess the APEX standard export performs a similar operation. An additional advantage of that approach is the fact that you get proper filesize information when the download starts, so progress and time estimation is accurate...
I implemented the following procedure for the generic export (download) part:
To use this in your procedure, you need to do the following
PROCEDURE csv_export (in_clob IN CLOB, in_filename IN VARCHAR2, in_charset IN VARCHAR2 DEFAULT 'WE8MSWIN1252') AS l_blob BLOB; l_length INTEGER; l_dest_offset INTEGER := 1; l_src_offset INTEGER := 1; l_lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX; l_warning INTEGER; BEGIN -- create new temporary BLOB DBMS_LOB.createtemporary(l_blob, FALSE); -- tranform the input CLOB into a BLOB of the desired charset /** @TODO: check whether lang_context should be an additional parameter ** the DBMS_LOB documentation doesn't say much about that parameter **/ DBMS_LOB.convertToBlob( dest_lob => l_blob, src_clob => in_clob, amount => DBMS_LOB.LOBMAXSIZE, dest_offset => l_dest_offset, src_offset => l_src_offset, blob_csid => nls_charset_id(in_charset), lang_context => l_lang_context, warning => l_warning); -- determine length for header l_length := DBMS_LOB.getlength(l_blob); -- create response header OWA_UTIL.mime_header('text/comma-separated-values', false); htp.p('Content-length: ' || l_length); htp.p('Content-Disposition: attachment; filename="'||in_filename||'"'); -- close the headers OWA_UTIL.http_header_close; -- download the BLOB WPG_DOCLOAD.download_file( l_blob ); -- release BLOB from memory DBMS_LOB.freetemporary(l_blob); EXCEPTION WHEN OTHERS THEN DBMS_LOB.freetemporary(l_blob); RAISE; END csv_export;
Adapt that example as needed.
DECLARE -- other variables here l_clob CLOB; BEGIN -- create new temporary CLOB DBMS_LOB.createtemporary(l_clob, FALSE); -- loop to prepare your content - just an example -- use the one you use right now for streaming with htp.p -- and replace the htp.p with the append FOR a in 1..10 LOOP DBMS_LOB.append(dest_lob => l_clob, src_lob => 'any_VARCHAR2_or_CLOB_content_or_variable'); END LOOP; -- perform actual export/download csv_export(in_clob => l_clob, in_filename => yourfilename.csv); -- stop any other rendering unless you want that, e.g. for a confirmation or something similar apex_application.g_unrecoverable_error := true; -- release BLOB from memory DBMS_LOB.freetemporary(l_clob); EXCEPTION WHEN OTHERS THEN DBMS_LOB.freetemporary(l_clob); RAISE; END;
If you need streaming for some reason, you should start some research on HTP.PUTRAW and the surrounding procedures for setting transferencoding and headers to fit to that mode. It should work as well, but I don't like the 2000 bytes size limit that comes along with RAW and I knew the BLOB-approach works for proper downloads...
I hope this helps you solve your problem.
thanks for the explanation and sample!
I solved it another way :)
I switched format on the csv-eport from plain text, semicolon separated, to an html table export.
That way I can use html entities instead (ä etc) and more goodies are styles and (some) column width control.
My mime header is set to "application/vnd.ms-excel", otherwise like yours (minus the length).
Then I do
followed by a head with some style, the body and the table with my export. Even colspan works :)
<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">
Your solution might come in handy some other time though.