5 Replies Latest reply: Mar 28, 2013 5:15 PM by Sergiusz Wolicki-Oracle RSS

    utl_file output converted from database characterset

    medlocrf
      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
          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
            What are your Linux locale settings? Are they using utf8?

            -----------
            Sybrand Bakker
            Senior Oracle DBA
            • 3. Re: utl_file output converted from database characterset
              medlocrf
              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
                medlocrf
                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
                  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