Oracle PLSQL coding -- UTF-8 to UTF-BOM conversion

Question

I am working with a standard 11g Standard Edition Oracle database.

These are my NLS parameters:

NLS_LANGUAGE    AMERICAN
NLS_TERRITORY   AMERICA
NLS_CURRENCY    $
NLS_ISO_CURRENCY    AMERICA
NLS_NUMERIC_CHARACTERS  .,
NLS_CHARACTERSET    AR8MSWIN1256
NLS_CALENDAR    GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE   AMERICAN
NLS_SORT    BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT  HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY   $
NLS_COMP    BINARY
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET  AL16UTF16
NLS_RDBMS_VERSION   11.2.0.4.0

I have a script to get some data from oracle database to a TEXT file with delimiter ","

Oracle code:

BEGIN 
    F := UTL_FILE.FOPEN_nchar('DIRECTORY','courses.txt','w',32767);                 
    UTL_FILE.PUT_nchar (F,'EXTERNAL_COURSE_KEY,COURSE_ID,COURSE_NAME,AVAILABLE_IND,ROW_STATUS,DATA_SOURCE_KEY');
    UTL_FILE.NEW_LINE(F);
    FOR C1_R IN C1
    LOOP
        UTL_FILE.PUT_nchar(F,C1_R.EXTERNAL_COURSE_KEY);
        UTL_FILE.PUT_nchar(F,','||C1_R.COURSE_ID);
        UTL_FILE.PUT_nchar(F,','||C1_R.COURSE_NAME);
        UTL_FILE.PUT_nchar(F,','||C1_R.AVAILABLE_IND);
        UTL_FILE.PUT_nchar(F,','||C1_R.ROW_STATUS);
        UTL_FILE.PUT_nchar(F,','||C1_R.DATA_SOURCE_KEY);
        UTL_FILE.NEW_LINE(F);
    end loop;
  UTL_FILE.FCLOSE(F);
end;

This WILL GENERATE FILE IN THE SPECIFIED DIRECTORY WITH UFT-8 ENCODING .

This file is exported from oracle db is to upload in another application. nchar is used to export the courses with Arabic names to appear properly. Issue I am facing is that the file should have encoding UFT-8-BOM. If only the generated text file have UFT-8-BOM encoding, the Arabic text will upload correctly instead of showing unreadable symbols.

Please assist on how to export the same with UFT-8-BOM encoding from Oracle .

Thanks .

In general the BOM for UTF-8 is optional, I think you have to add it manually. This one may help: https://unix.stackexchange.com/a/475872/314876 (but you should find many others) — Wernfried Domscheit, Jul 25 '20 at 21:38
could you provide us some arabic data from db? data could be using AR8MSWIN1256 not UTF-8, so did you tried FOPEN function? — Eng. Samer T, Jul 26 '20 at 00:23
@Eng.SamerT DAI-315-Fall-20-21-1,DAI-315-Fall-20-21-1,التفسير-Evening,Y,ENABLED,Courses-Fall-20-21 DAI-317-Fall-20-21-1,DAI-317-Fall-20-21-1,النقد الأدبي-Evening,Y,ENABLED,Courses-Fall-20-21 I did not try FOpen option , Could you please provide me with some examples on how the Plsql can be edited using the same. — Vishnu, Jul 26 '20 at 07:20

score 2 · Answer 1 · answered Jul 25 '20 at 18:39

2

Just write 2 bytes in the beginning of your file: FE FF

answered Jul 25 '20 at 18:39

Sayan Malakshinov

8,492
1
19
27

Dear ,Could you please help with the Oracle Pl-SQL coding of the same ? – Vishnu Jul 26 '20 at 07:19

score 0 · Answer 2 · answered Jul 26 '20 at 13:47

0

F := UTL_FILE.FOPEN_nchar('DIRECTORY','courses.txt','w',32767);

UTL_FILE.PUT_RAW(F, HexToRaw('EFBBBF')); -- BOM

Thanks Everyone.

answered Jul 26 '20 at 13:47

Vishnu

1
4

Oracle PLSQL coding -- UTF-8 to UTF-BOM conversion

2 Answers2