| UTL_FILE and SQL_LANG [message #112528] |
Sat, 26 March 2005 15:17  |
casado
Messages: 2
Registered: March 2005
|
Junior Member |
|
|
Hello All,
1) Config
WinXp (Portuguese) SP1 running both:
Oracle Server 9.2.0.1.0
Oracle Client
2) CharSets
Windows codepage = 1252
Environment NLS_LANG = BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252
NLS_LANG in HOME0 = BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252
NLS_LANG in HOME1 = BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252
and
select * from nls_database_parameters
returns NLS_CHARACTERSET = AL32UTF8
3) Problem
I wrote a procedure that uses UTL_FILE to read lines from a text file. As the text file contains portuguese accented characters, I get inverted ? in line_buffer after a GET_LINE command.
This is because Oracle tries to read a WE8MSWIN1252 text file as if it were AL32UTF8.
changing
Environment NLS_LANG to BRAZILIAN PORTUGUESE_BRAZIL.AL32UTF8
NLS_LANG in HOME0 to BRAZILIAN PORTUGUESE_BRAZIL.AL32UTF8
NLS_LANG in HOME1 to BRAZILIAN PORTUGUESE_BRAZIL.AL32UTF8
solves the problem, but then client applications (sqlplus, sqlplusw and PL/SQL Developer) show Oracle messages (and menu items) with wrong characters in plece of the portuguese accented ones.
Changing the database NLS_CHARACTERSET to WE8MSWIN1252, although feasible, could create problems with a java application that accesses Oracle and uses Unicode.
So, it looks like I´m trapped btw choosing to have the Oracle Client NLS_LANG not match the Windows codepage OR to have the Oracle Client NLS_LANG not match the database NLS_CHARACTERSET.
Is there something I could fix in my UTL_FILE procedure to read the text file correctly ?
Best Regards,
Cesar Casado
|
|
|
|
| Re: UTL_FILE and SQL_LANG [message #112771 is a reply to message #112528] |
Tue, 29 March 2005 05:07  |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
|
No experience with it, but did you try to change NLS setting with ALTER SESSION (so, just for the time being, during the load of that file)?
|
|
|
|
|
|
| Re: UTL_FILE and SQL_LANG [message #112903 is a reply to message #112795] |
Tue, 29 March 2005 17:00 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
This isn't really a great solution, but maybe something to consider if all else fails...
When you submit a DBMS_JOB, it stores all the environmental NLS settings (see user_jobs.nls_env). You could try submitting a periodic dbms_job from a session with the environment you want - to do the loading into a table. Clients accessing the table would use their own NLS settings.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
