OraFAQ Forum: Client Tools » spool format UTF8 in sqlplus9

Home » Other » Client Tools » spool format UTF8 in sqlplus9 (sqlplus 9 winxp)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

spool format UTF8 in sqlplus9 [message #406355]

Wed, 03 June 2009 09:57

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

andrew again
Messages: 2577
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

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 1440 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

andrew again
Messages: 2577
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 20 02:12:02 CDT 2024