CODEWITHSUNDEEP
oracle-databaseplsqloracle11gcharacter-encodingutl-file
Vishnu
Vishnu

Reputation: 1

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

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 .

Upvotes: 0

Views: 3191

Answers (2)

Vishnu
Vishnu

Reputation: 1

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

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

Thanks Everyone.

Upvotes: 0

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

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

Upvotes: 2

Related Questions