2 Replies Latest reply on Dec 9, 2011 7:58 AM by Tomas Albinsson

    Changing character encoding

    Tomas Albinsson
      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
        • 1. Re: Changing character encoding
          Udo
          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
          1 person found this helpful
          • 2. Re: Changing character encoding
            Tomas Albinsson
            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