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

"it dosent seem to work" - not enough info.. Are you using `utl_file.fopen_nchar` or just `fopen`? — Sayan Malakshinov, Apr 22 '21 at 10:57
Which character do you expect? Character 15711167 => EFBBBF does not exist. The UTF-8 Byte values is `U+FEFF`, BYTE ORDER MARK. This should be the first character in a document. — Wernfried Domscheit, Apr 22 '21 at 10:58
I am using fopen only. But in the output file i see below characters in columm customer name i get the below in some fields. — Gaurav Thuckral, Apr 22 '21 at 13:17
×˜×™×‘ ×˜×¢× ×¨×©×ª×•×ª ×‘×¢"×ž ×—×‘×™×‘ ×‘×¢×™×¨ ×‘×¢"×ž ×“×•×¨ ××œ×•×Ÿ ×ž×ª×—×ž×™× ×§×ž×¢×•× ××™×™× ×”×•× ×¡× ×˜×¨ (×¢×©×” ×–××ª ×‘×¢×¦×ž×š) ×‘×¢"×ž ×˜×™×‘ ×˜×¢× ×¨×©×ª×•×ª ×‘×¢"×ž ×—×‘×™×‘ ×‘×¢×™×¨ ×‘×¢"×ž ×“×•×¨ ××œ×•×Ÿ ×ž×ª×—×ž×™× ×§×ž×¢×•× ××™×™× — Gaurav Thuckral, Apr 22 '21 at 13:18
Hi @SayanMalakshinov i am using fopen only. Could please share a doc regarding the use of fope_nchar. Also how these two are different? — Gaurav Thuckral, Apr 23 '21 at 06:07
Hi @WernfriedDomscheit please have a look again at the problem, i have added all the necessary information as requested. — Gaurav Thuckral, Apr 23 '21 at 06:19
You added your PL/SQL code but you did not provide any additional information for your problem. What do you expect from `UTL_FILE.PUT_LINE(output_file1,chr(9)||CHR(15711167));` and what do you get? — Wernfried Domscheit, Apr 23 '21 at 10:13
Hi @WernfriedDomscheit i have attached a screenshot of the junk characters which are there in the output file. Please let me know if you can see the ss attached. — Gaurav Thuckral, Apr 23 '21 at 13:17
Key ID Part Site Demand Type Demand Date Qty Driver Part Name Driver Part Site Order Number Order Customer Name 006-8604919_BUD_20042021_7360MC666_IL 006-8604919 BUD Sales order 20-Apr-21 80 7360MC666 IL 6505126.Standard IL.ORDER ENTRY ×˜×™×‘ ×˜×¢× ×¨×©×ª×•×ª ×‘×¢"×ž — Gaurav Thuckral, Apr 23 '21 at 13:21
You still don't answer my question. A single `UTL_FILE.PUT_LINE(...)` cannot generate such screenshot. Again, what do you expect from the single `UTL_FILE.PUT_LINE(...)` command and what do you get? — Wernfried Domscheit, Apr 23 '21 at 13:21
I want to handle this invalid characters "×˜×™×‘ ×˜×¢× ×¨×©×ª×•×ª ×‘×¢"×ž" — Gaurav Thuckral, Apr 23 '21 at 13:21
These characters are not present in the source side. but i see them in the file that is the problem. — Gaurav Thuckral, Apr 23 '21 at 13:22
Sorry, I give up! Focus on the single line, then you can try to solve the issue. Provide the binary data from HEX-Editor. Or use `DBMS_OUTPUT.PUT_LINE` — Wernfried Domscheit, Apr 23 '21 at 13:23
Previously i have used this UTL_FILE.PUT_LINE(output_file1,chr(9)||CHR(15711167)); to solve the issue but it is not working in this scenario. — Gaurav Thuckral, Apr 23 '21 at 13:24
No Problem @WernfriedDomscheit Thanks for taking a look at it !! — Gaurav Thuckral, Apr 23 '21 at 13:25
I had faced this similar issue in that scenario someone suggested me to use this "UTL_FILE.PUT_LINE(output_file1,chr(9)||CHR(15711167));" to solve the issue and it worked!! But not sure why it is not working now :| — Gaurav Thuckral, Apr 23 '21 at 13:27
"someone suggested me..." - do you know why? Do a last trial: `UTL_FILE.FOPEN` and `UTL_FILE.PUT_LINE` write the files in the database character set. Perhaps the character set (check with `SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET'`) has been changed. — Wernfried Domscheit, Apr 23 '21 at 13:38
Was it the same when it was working? What is the first line of a working file? What is the first line of a non-working file? What do you get from `select DMD_CUSTOMER_NAME from ncr_pegging_Details`? **Don't paste screenshots!** (see https://meta.stackoverflow.com/a/285557/3027266, esp. https://stackoverflow.com/questions/9246051/how-do-i-fix-this-missing-semicolon-syntax-error-in-javascript). Provide the information of file from HEX-Editor, preferably only from column "Order Customer Name" — Wernfried Domscheit, Apr 23 '21 at 16:00

score 1 · Answer 1 · answered Apr 22 '21 at 12:16

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;

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

1 Answers1