Forum Stats

  • 3,728,021 Users
  • 2,245,519 Discussions
  • 7,853,248 Comments

Discussions

Changing character encoding

Tomas Albinsson
Tomas Albinsson Member Posts: 143
Hi,

I have a procedure in my database that produces a .csv-output for Excel.
Using http headers I get Excel to open the "file" produced.

My problem is our swedish characters, åäö.
Excel (at least Excel 2003) wants iso-8859-1 encoding for these characters to work.
So my procedure uses convert() to go from database charset UTF8 to WE8ISO8859P1.

This worked fine under Oracle Portal but not so under Apex Listener on WebLogic.
I think the listener is converting my iso text to utf on the way to the browser.

Is this so?

I've read that it "defaults to utf8" and "bound to utf8" but nothing official.
My listener version is 1.1.3.243.11.40


Kind regards

Tomas
jariola

Answers

  • Udo
    Udo Member Posts: 1,987
    Hi Tomas,

    I'm sorry it took some time to prepare an answer for you...
    Excel (at least Excel 2003) wants iso-8859-1 encoding
    This 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:
    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;
    To use this in your procedure, you need to do the following
    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;
    Adapt that example as needed.
    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.

    -Udo
    Udo
  • Tomas Albinsson
    Tomas Albinsson Member Posts: 143
    Hi Udo,

    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
    <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">
    followed by a head with some style, the body and the table with my export. Even colspan works :)

    Your solution might come in handy some other time though.


    Thanks again

    Tomas
This discussion has been closed.