How to handle Chineese/Japenese characters while writing data to a .csv file using utl.file wirte plsql

Question

I was trying to use the below to handle the Japenese characters in my data set but it dosent seem to work.

UTL_FILE.PUT_LINE(output_file1,chr(9)||CHR(15711167));

Can anyone suggest any alternatives for the same.

Below id the PL-SQL code.

 v_sql_stmt := 'select (dep.PEG_ITEM_NAME||''_''||substr(dep.DMD_ORG_CODE ,5)||''_''||to_char(Dep.SUP_SUGG_DUE_DATE,''ddmmyyyy'')|| ''_'' ||ind.Peg_Item_Name|| ''_'' ||substr(ind.DMD_ORG_CODE ,5)) ID,
dep.PEG_ITEM_NAME Part,
substr(dep.DMD_ORG_CODE ,5) Site,
ind.DMD_ORDER_TYPE Demand_Type,
Dep.SUP_SUGG_DUE_DATE Due_Date,
Dep.DMD_USING_REQ_QTY Demand_Qty,
ind.Peg_Item_Name Driver_Part,
substr(ind.DMD_ORG_CODE ,5) Driver_Site,
Ind.DMD_ORDER_NUMBER Sales_Order,
Ind.DMD_CUSTOMER_NAME Customer_Name
from ncr_pegging_Details dep, ncr_pegging_Details ind
where ind.batch_num=dep.batch_num 
and ind.batch_num=1846
and UPPER(dep.dmd_order_type)  in(''PLANNED ORDER DEMAND'',''WORK ORDER DEMAND'',''TRANSFER ORDER DEMAND'')
and UPPER(ind.dmd_order_type)  in(''SALES ORDER'',''FORECAST'')
and dep.PEG_END_PEGGING_ID=ind.peg_pegging_id
and ind.peg_pegging_id=ind.PEG_END_PEGGING_ID
AND substr(dep.DMD_ORG_CODE,5) in (select LOOKUP_CODE from apps.NCR_SHR_LOOKUP_VALUES@'
                      || v_db_link
                      || ' where LOOKUP_TYPE = ''NCR OCP ANP ORG LOOKUP'')
Group by (dep.PEG_ITEM_NAME||''_''||substr(dep.DMD_ORG_CODE ,5)||''_''||to_char(Dep.SUP_SUGG_DUE_DATE,''ddmmyyyy'')|| ''_'' ||ind.Peg_Item_Name|| ''_'' ||substr(ind.DMD_ORG_CODE ,5)),
dep.PEG_ITEM_NAME,substr(dep.DMD_ORG_CODE ,5),ind.DMD_ORDER_TYPE,Dep.SUP_SUGG_DUE_DATE,Dep.DMD_USING_REQ_QTY,ind.Peg_Item_Name,substr(ind.DMD_ORG_CODE ,5),
Ind.DMD_ORDER_NUMBER,Ind.DMD_CUSTOMER_NAME 
UNION 
 select (dep.PEG_ITEM_NAME||''_''||substr(dep.DMD_ORG_CODE ,5)||''_''||to_char(Dep.SUP_SUGG_DUE_DATE,''ddmmyyyy'')|| ''_'' ||ind.Peg_Item_Name|| ''_'' ||substr(ind.DMD_ORG_CODE ,5)) ID,
dep.PEG_ITEM_NAME Part,
substr(dep.DMD_ORG_CODE ,5) Site,
ind.DMD_ORDER_TYPE Demand_Type,
Dep.SUP_SUGG_DUE_DATE Due_Date,
Dep.DMD_USING_REQ_QTY Demand_Qty,
ind.Peg_Item_Name Driver_Part,
substr(ind.DMD_ORG_CODE ,5) Driver_Site,
Ind.DMD_ORDER_NUMBER Sales_Order,
Ind.DMD_CUSTOMER_NAME Customer_Name
from ncr_pegging_Details dep, ncr_pegging_Details ind
where ind.batch_num=dep.batch_num 
and ind.batch_num=1846
and UPPER(dep.dmd_order_type)  in(''PLANNED ORDER DEMAND'',''WORK ORDER DEMAND'',''TRANSFER ORDER DEMAND'')
and UPPER(ind.dmd_order_type)  in(''TRANSFER ORDER DEMAND'')
AND dep.PEG_END_PEGGING_ID=ind.PEG_END_PEGGING_ID
and dep.PEG_PREV_PEGGING_ID=ind.PEG_PEGGING_ID
AND substr(dep.DMD_ORG_CODE,5) in (select LOOKUP_CODE from apps.NCR_SHR_LOOKUP_VALUES@'
                      || v_db_link
                      || ' where LOOKUP_TYPE = ''NCR OCP ANP ORG LOOKUP'')
Group by (dep.PEG_ITEM_NAME||''_''||substr(dep.DMD_ORG_CODE ,5)||''_''||to_char(Dep.SUP_SUGG_DUE_DATE,''ddmmyyyy'')|| ''_'' ||ind.Peg_Item_Name|| ''_'' ||substr(ind.DMD_ORG_CODE ,5)),
dep.PEG_ITEM_NAME,substr(dep.DMD_ORG_CODE ,5),ind.DMD_ORDER_TYPE,Dep.SUP_SUGG_DUE_DATE,Dep.DMD_USING_REQ_QTY,ind.Peg_Item_Name,substr(ind.DMD_ORG_CODE ,5),
Ind.DMD_ORDER_NUMBER,Ind.DMD_CUSTOMER_NAME ' ;

        OPEN demand_ref_cur FOR v_sql_stmt;

        FETCH demand_ref_cur BULK COLLECT INTO demand_tbl;
        IF demand_tbl.count > 0 THEN
            v_file_name := 'AnaplanDemand.tab';
            output_file1 := utl_file.fopen('DEM_TOP_EXP_OCP', v_file_name, 'W',32700);
            UTL_FILE.PUT_LINE(output_file1,chr(9)||CHR(15711167));
            v_data := NULL;
            dbms_output.put_line('------------------------------------------------------');
            dbms_output.put_line(' File creation start.');
            dbms_output.put_line('Anaplan Report Name - ' || v_file_name);
            v_data := 'Key ID'
                      || chr(9)
                      || 'Part'
                      || chr(9)
                      || 'Site'
                      || chr(9)
                      || 'Demand Type'
                      || chr(9)
                      || 'Demand Date'
                      || chr(9)
                      || 'Qty'
                      || chr(9)
                      || 'Driver Part Name'
                      || chr(9)
                      || 'Driver Part Site'
                      || chr(9)
                      || 'Order Number'
                      || chr(9)
                      || 'Order Customer Name';

            utl_file.put_line(output_file1, v_data);
            FOR i IN demand_tbl.first..demand_tbl.last LOOP
                v_count := v_count + 1;
                v_data := demand_tbl(i).id
                          || chr(9)
                          || demand_tbl(i).part
                          || chr(9)
                          || demand_tbl(i).site
                          || chr(9)
                          || demand_tbl(i).demand_type
                          || chr(9)
                          || demand_tbl(i).due_date
                          || chr(9)
                          || demand_tbl(i).demand_qty
                          || chr(9)
                          || demand_tbl(i).driver_part
                          || chr(9)
                          || demand_tbl(i).driver_site
                          || chr(9)
                          || demand_tbl(i).sales_order
                          || chr(9)
                          || demand_tbl(i).customer_name;

                utl_file.put_line(output_file1, v_data);
                v_data := NULL;
            END LOOP;

            dbms_output.put_line('Total Records written in file is ' || v_count);
            utl_file.fclose(output_file1);
        END IF;
    END ncr_ocp_anp_demand_extract_proc;

Attached is the output file. Please have a look and let me know if anything else is needed.

Output file ss

Answer 1

Not clear what you are looking for.

CHR(15711167 USING NCHAR_CS)

returns "뮿" -> U+BBBF: Hangul Syllable Myuh

15711167 decimal is EFBBBF hex, looks like CHR(... USING NCHAR_CS) ignores the first Byte, because it requires an even number of bytes.

So, CHR(48063 USING NCHAR_CS) gives the same, because 48063 decimal is BBBF hex.

If you know the Unicode codepoint you can also use

UTL_I18N.RAW_TO_CHAR(HEXTORAW(TO_CHAR('BBBF', 'fmXXXX')), 'AL16UTF16')

Note, this works only for the Basic Multilingual Plane. If you like to test the full Unicode range, you can use these functions.

-- Similar to Oracle function CHR/NCHR but accept supplementary characters above 65535 (U+FFFF) without transformation to UCS-2
CREATE OR REPLACE FUNCTION CHRF(codepoint INTEGER) RETURN VARCHAR2 DETERMINISTIC IS
    sg1 VARCHAR2(4);
    sg2 VARCHAR2(4);
BEGIN
    IF codepoint <= 65535 THEN
        RETURN UTL_I18N.RAW_TO_CHAR(HEXTORAW(TO_CHAR(codepoint, 'fmXXXX')), 'AL16UTF16');
    ELSE
        sg1 := LPAD(TO_CHAR(TO_NUMBER('D800', 'XXXX') + TRUNC((codepoint - 2**16) / 2**10), 'fmXXXX'), 4, '0');
        sg2 := LPAD(TO_CHAR(TO_NUMBER('DC00', 'XXXX') + (codepoint - 2**16) MOD 2**10, 'fmXXXX'), 4, '0');
        RETURN UTL_I18N.RAW_TO_CHAR(HEXTORAW(sg1||sg2), 'AL16UTF16');
    END IF; 
END CHRF;


-- Similar to Oracle function UNISTR but accept supplementary characters above U+FFFF without transformation to UCS-2.
-- Unlike UNISTR this function supports only one single character
CREATE OR REPLACE FUNCTION UNISTRF(codepoint VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
    RETURN CHRF(TO_NUMBER(REGEXP_SUBSTR(codepoint, '[[:xdigit:]]{1,6}$'), 'XXXXXX'));
END UNISTRF;