I am trying to create a custom export function where data contains UTF8 characters. Now if I open the csv in excel directly, the UTF8 characters are shown as junk, but however if I mention the character encoding while importing the csv, the characters are shown properly. I need to know what is to be writing in the mime_header so that if the csv is opened directly in excel it must show the utf8 characters properly.
Here is the code I am using
DECLARE
RetVal NUMBER;
PI_SHORTNAME VARCHAR2(32767);
PI_YEAR NUMBER;
PI_ANALYSIS_OEM VARCHAR2(32767);
PI_QUESTION_TYPE VARCHAR2(32767);
PO_HTML CLOB;
lv_type varchar2(50);
lv_filename varchar2(100);
v_xml VARCHAR2 (32000);
v_clob CLOB;
BEGIN
lv_type := 'Verbatim';
lv_filename := :P13_SHORTNAME || '_' || :P13_YEAR || '_' || lv_type;
--OWA_UTIL.MIME_HEADER ( 'Content-Type:application/octet;charset=AL32UTF8', false);
--owa_util.mime_header( 'application/octet', FALSE, 'AL32UTF8' );
--owa_util.mime_header( 'application/csv; charset=UTF-8', FALSE, 'AL32UTF8' );
--owa_util.mime_header('Content-Encoding: UTF-8');
owa_util.mime_header('Content-type: application/csv; charset=UTF-8');
-- Set the name of the file
--owa_util.mime_header('Content-Disposition: attachment; filename=' ||lv_filename|| '.csv');
htp.p('Content-Disposition: attachment; filename=' ||lv_filename|| '.csv');
-- Close the HTTP Header
owa_util.http_header_close;
PI_SHORTNAME := :P13_SHORTNAME;
PI_YEAR := :P13_YEAR;
PI_ANALYSIS_OEM := :P13_CSOE_ID;
PI_QUESTION_TYPE := :P13_OUTPUT;
v_clob := PO_HTML ------- PO_HTML contains the csv data
WHILE LENGTH (v_clob) > 0
LOOP
BEGIN
IF LENGTH (v_clob) > 16000
THEN
v_xml := SUBSTR (v_clob, 1, 16000);
HTP.prn (v_xml);
v_clob := SUBSTR (v_clob, LENGTH (v_xml) + 1);
ELSE
v_xml := v_clob;
HTP.prn (v_xml);
v_clob := '';
v_xml := '';
END IF;
END;
END LOOP;
htmldb_application.g_unrecoverable_error := true;
END;