Sunday, August 21, 2011

how to extract you data in utf8 text encoded file

Usually oracle using utl_file package will export data in ascii encoded text which is not common as data integration encoding between heterogeneous information systems . i was asked to deliver the exported text in utf8 for integration purposes with a Microsoft e-learning system.


To transfer ascii file encoding to utf8 you should put a BOM at the start of the utf8 new file then encode it from ascii to raw to utf8 statement line by line as follows in the example .



CREATE OR REPLACE PROCEDURE SYS.utf8file
IS
bom_raw RAW (3);
text VARCHAR2 (32767);
text_raw RAW (32767);
filehandler UTL_FILE.file_type;
BEGIN
-- write the bom section
bom_raw := HEXTORAW ('EFBBBF');
--prepare the text
text := 'this is a utf8 file encoded ';
--convert it to raw encoded text
text_raw := UTL_I18N.string_to_raw (text, 'UTF8');
-- open the file with the nchar for new encoding in the Directory BBSIS
filehandler := UTL_FILE.fopen_nchar ('BBSIS', 'utf8text.txt', 'w', 32767);
-- write the bom section
UTL_FILE.put_nchar (filehandler, UTL_I18N.raw_to_nchar (bom_raw, 'UTF8'));
-- Now. write out the rest of our text retrieved from Oracle with its UTF8 encoding
UTL_FILE.put_nchar (filehandler, UTL_I18N.raw_to_nchar (text_raw, 'UTF8'));
-- Close the unicode (UTF8) encoded text file
UTL_FILE.fclose (filehandler);
EXCEPTION
WHEN UTL_FILE.invalid_path
THEN
raise_application_error
(-20000,
'ERROR: Invalid path for file or path not in INIT.ORA.'
);
END;

/


3 comments:

  1. Thank you. I works well and I can build on it in a current project

    ReplyDelete
  2. Hi, How do I do this on Oracle 9i?
    It seams that UTL_I18N requires 10.

    Thank you,

    ReplyDelete
  3. Great and I have a swell give: Where To Start Home Renovation home renovation quotation

    ReplyDelete