Members   Search   Help    Register    Login   Home
Home » SQL & PL/SQL » Client Tools » spool format UTF8 in sqlplus9 (sqlplus 9 winxp)
spool format UTF8 in sqlplus9 [message #406355] Wed, 03 June 2009 09:57 Go to next message
joanbn
Messages: 2
Registered: June 2009
Junior Member
Hi,

I have a problem with spool. I need to create a text file with UTF8 format.

In my environment I have the registry parameters below:

ACP: 1252
OEMCP: 850
NLS_LANG: .UTF8

The database I connect has the nls_characterset = WE8MSWIN1252

Sqlplus always creates the file with windows-1252 format. I changed ACP to 65001 (I guess is the UTF8).

I tryed to make some convertions by

CONVERT(string, 'UTF8', 'WE8MSWIN1252')

But it didn't work. Anyone knows something about it?

Thanks for help.
Report message to a moderator
Re: spool format UTF8 in sqlplus9 [message #406382 is a reply to message #406355] Wed, 03 June 2009 14:13 Go to previous messageGo to next message
andrew again
Messages: 2447
Registered: March 2000
Senior Member
Try this:
1) open DOS prompt (cmd.exe)
2) C:\>set NLS_LANG=AMERICAN_AMERICA.al32utf8
3) start non-gui sqlplus (I'm use 10g version), but should be the same behaviour for 9i:
C:\>sqlplus user/pass@dbname

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 3 12:00:56 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

SQL> -- verify NLS_LANG setting in effect
SQL> @%NLS_LANG%
SP2-0310: unable to open file "AMERICAN_AMERICA.al32utf8"
SQL> spool c:\t.txt
SQL> -- euro
SQL> select unistr('\20AC') from dual;

UNIS
----
Γé¼   <== ignore what you see here - it depends on DOS codepage, font etc.

SQL> spool off

4) open the spool file, trim out the junk, then view in a hex editor. You should see E2 82 AC which is correct for Euro in UTF8. http://www.eki.ee/letter/chardata.cgi?ucode=20ac
Report message to a moderator
Re: spool format UTF8 in sqlplus9 [message #406498 is a reply to message #406382] Thu, 04 June 2009 04:56 Go to previous messageGo to next message
joanbn
Messages: 2
Registered: June 2009
Junior Member
Thank you, now it seems to work.

But the problem is that I use .NET to create the same file as well. And bot files should match exactly the same. Now they almost match, but the header seems to be different. Check the image and you will see "EF BB BF" that generates the .NET proces. And the sqlplus does not.

I do not know if this is a problem.

Thanks again.

  • Attachment: out.jpg
    (Size: 935.21KB, Downloaded 25 times)
Report message to a moderator
Re: spool format UTF8 in sqlplus9 [message #406563 is a reply to message #406498] Thu, 04 June 2009 11:46 Go to previous message
andrew again
Messages: 2447
Registered: March 2000
Senior Member
EF BB BF is the correct BOM for UTF8 encoded files ==> http://en.wikipedia.org/wiki/Byte-order_mark

There are several google hits about sqlplus not liking to read BOM header bytes in input files. In general, SQLplus isn't unicode capable (on windows at least). Maybe there's a windows command to change the format of the spool file into UTF8 once it's created by sqlplus. Also see chcp 65001 (utf8 codepage)
Report message to a moderator
Previous Topic:Toad--how to find table name based on FK column name
Next Topic:NLS_DATE_FORMAT
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Nov 9 02:16:28 CST 2009

Total time taken to generate the page: 0.32258 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.