This discussion is archived
5 Replies Latest reply: Mar 28, 2013 3:15 PM by Sergiusz Wolicki (Oracle) RSS

utl_file output converted from database characterset

374365 Newbie
Currently Being Moderated
I have a developer that is sending output to a file using utl_file. Here is a snippet of his code:

fileHand UTL_FILE.file_type;
buffer VARCHAR2(1000);

UTL_FILE.put_raw(fileHand, UTL_RAW.cast_to_raw(buffer), TRUE);

In the database there is data such as:

Stéphanie Chéry

but in the output file it is written as:

Stéphanie Chéry

basically it is being converted to WE characterset.

This is RDBMS 11.2.0.2 64bit running on RHE 5. The linux user account executing the proc that generates the output file has NLS_LANG=AMERICAN_AMERICA.AL32UTF8 in it's environment. We don't understand why the characterset is being converted. he has tried using the NCHAR functions of UTL_FILE and still gets the same results.

Any idea what our issue is?

Below is SELECT * FROM NLS_DATABASE_PARAMETERS from the database

"PARAMETER","VALUE"
"NLS_LANGUAGE","AMERICAN"
"NLS_TERRITORY","AMERICA"
"NLS_CURRENCY","$"
"NLS_ISO_CURRENCY","AMERICA"
"NLS_NUMERIC_CHARACTERS",".,"
"NLS_CHARACTERSET","AL32UTF8"
"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_NCHAR_CHARACTERSET","AL16UTF16"
"NLS_COMP","BINARY"
"NLS_LENGTH_SEMANTICS","CHAR"
"NLS_NCHAR_CONV_EXCP","FALSE"
"NLS_RDBMS_VERSION","11.2.0.2.0"
  • 1. Re: utl_file output converted from database characterset
    sb92075 Guru
    Currently Being Moderated
    do you have a data storage problem or data presentation problem?

    man od
    in order "dump" file content

    od -x <file_name>
  • 2. Re: utl_file output converted from database characterset
    sybrand_b Guru
    Currently Being Moderated
    What are your Linux locale settings? Are they using utf8?

    -----------
    Sybrand Bakker
    Senior Oracle DBA
  • 3. Re: utl_file output converted from database characterset
    374365 Newbie
    Currently Being Moderated
    1076:jobsub@elver:/home/jobsub> env
    _=*8512*/bin/env
    CLASSPATH=
    COBDIR=/opt/FJSVcbl64
    COBPATH=/home/jobsub:/app/sct/banner_du/links:/app/sct/banner_du/general/exe
    COBPREF=perl /app/sct/banner_du/links/banfjsv.pl
    COBSUFX=
    DATAFILE_HOME=
    DATA_HOME=/app/sct/dataload/DUSIMS
    DISPLAY=localhost:10.0
    G_BROKEN_FILENAMES=1
    HISTSIZE=1000
    HOME=/home/jobsub
    INPUTRC=/etc/inputrc
    LANG=en_US.UTF-8
    LC_ALL=en_US.UTF-8
    LC_COLLATE=en_US.UTF-8
    LC_CTYPE=en_US.UTF-8
    LC_MESSAGES=en_US.UTF-8
    LC_MONETARY=en_US.UTF-8
    LC_NUMERIC=en_US.UTF-8
    LC_TIME=en_US.UTF-8
    LD_LIBRARY_PATH=/app/oracle11/product/11.2/lib:/app/oracle11/product/11.2/lib32:/opt/FJSVcbl64/lib:/opt/FJSVXbsrt/lib:/opt/FJSVXmeft/lib:/app/sct/banner_du/general/exe:/usr/local/lib
    LESSOPEN=|/usr/bin/lesspipe.sh %s
    LOGNAME=jobsub
    LS_COLORS=no=00:fi=00:di=01;34:ln=01;36:pi=40;33:so=01;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=01;32:*.cmd=01;32:*.exe=01;32:*.com=01;32:*.btm=01;32:*.bat=01;32:*.sh=01;32:*.csh=01;32:*.tar=01;31:*.tgz=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.gz=01;31:*.bz2=01;31:*.bz=01;31:*.tz=01;31:*.rpm=01;31:*.cpio=01;31:*.jpg=01;35:*.gif=01;35:*.bmp=01;35:*.xbm=01;35:*.xpm=01;35:*.png=01;35:*.tif=01;35:
    MAIL=/var/spool/mail/jobsub
    NLS_LANG=AMERICAN_AMERICA.AL32UTF8
    ORACLE_HOME=/app/oracle11/product/11.2
    ORACLE_LPARGS=-Plp
    ORACLE_LPPROG=lpr
    ORACLE_PATH=.:/home/jobsub:/app/sct/banner_du/links:/app/sct/banner_du/admin
    ORACLE_SID=DUSIMS
    ORA_NLS10=/app/oracle11/product/11.2/nls/data
    ORA_NLS11=/app/oracle11/product/11.2/nls/data
    ORA_NLS33=/app/oracle11/product/11.2/ocommon/nls/admin/data
    PATH=:/app/sct/banner_du/fixes_DUSIMS:/opt/FJSVcbl64/bin:/opt/FJSVXbsrt/lib:/opt/FJSVXmeft/lib::/app/sct/banner_du/general/exe:/app/sct/banner_du/admin:.:/app/sct/jobsub:/app/sct/dlp:/app/sct/jobsub/scripts:/usr/java/jdk1.6.0_25/bin:/bin:/usr/bin:/usr/local/bin:/usr/openv/netbackup/bin:/opt/sct/bin:/app/oracle11/product/11.2/bin:/app/sct/banner_du/links
    PS1=!:$LOGNAME@$HOSTNAME:$PWD>
    PWD=/home/jobsub
    SHELL=/bin/ksh
    SHLVL=1
    SQLPATH=.:/home/jobsub:/app/sct/banner_du/links:/app/sct/banner_du/admin
    SRCE_LINK=
    TERM=vt100
    USER=jobsub
    ASTFEATURES=UNIVERSE - ucb
    A__z="*SHLVL
  • 4. Re: utl_file output converted from database characterset
    374365 Newbie
    Currently Being Moderated
    Don't believe I have a data storage problem. The output appears to be what happens if you explicitly convert the data to WE8ISO8859P15 though I can't figure out why or where it's doing the convert.
  • 5. Re: utl_file output converted from database characterset
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    I suspect the problem is with how you look at the file. What you show in your post looks like UTF-8-encoded Western European text when viewed with a iso-8859-1 editor (actually, there should be no "Â" character but I have assumed for now this is an error in the posting). The text that UTL_FILE produced with the listed code should be encoded in AL32UTF8, that is, in UTF-8. Copy this file to a Windows machine and open it in Notepad specifying UTF-8 in the Encoding list of the Open File window. If you see the data correctly then, then the problem is with how you look at the generated file, not with the file itself.


    -- Sergiusz

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points