0

I have a Package that throws multiple errors like this one:

  IF EXIST > 0 THEN
    STATUS:= -1;
    pMSJ_ERROR:= 'ErrorXX: Fecha de transacción no puede ser mayor';

But when DBA run this package in another ambient the package is saved like this:

  IF EXIST > 0 THEN
    STATUS:= -1;
    pMSJ_ERROR:= 'ErrorXX: Fecha de transacci¿n no puede ser mayor';

So I think the problem is because the databases have different "nls_language":

select value from v$system_parameter where name = 'nls_language';
DB1 Dev: LATIN AMERICAN SPANISH
DB2 Test: AMERICAN

How can I deal with the different nls_languages?

If the dba from DB2 use:

alter session set nls_language = 'AMERICAN';

do the trick?

Both DBs are Oracle 11g

The character set in both DBs are the same:

SELECT value FROM NLS_DATABASE_PARAMETERS WHERE parameter = 'NLS_CHARACTERSET';
 -- value = WE8MSWIN1252
Rodrick
  • 595
  • 10
  • 27
  • What happens when they try that - or more likely if they change to the DB1 language rather than staying with 'American'? What are the database character sets of both databases, is the same client being used to access both, and what is the NLS_LANG on the client(s)? – Alex Poole May 18 '16 at 14:20
  • We did not try that yet. I want to be sure first. The NLS_CHARACTERSET is the same for both BDs = "WE8MSWIN1252", and I looking at this with SQL Developer 4.1.3.20 with NLS Language = AMERICAN – Rodrick May 18 '16 at 14:45
  • What is the NLS_LANG for whoever is seeing the correct characters? And where are they seeing them? If they're viewing a file in a shell then the shell and editor settings need to be suitable. It's not necessarily a database problem. – Alex Poole May 18 '16 at 15:05
  • OK - since you are on Windows, I will unashamedly point you to an older post of mine at OTN. Specifically reading about NSL_LANG. You don't care about the "GUI vs. DOS-style client" part, but you will see how to set or change NLS_CHARACTERSET for the client session for the GUI. https://community.oracle.com/thread/3907535?start=0&tstart=0 It's just my reading notes as I was reading the NLS_LANG FAQ (linked to in my Answer). –  May 18 '16 at 16:48
  • Which tool is your DBA using? TOAD? SQL*Plus? others? – Wernfried Domscheit May 18 '16 at 17:39
  • @WernfriedDomscheit is using SQl*Plus. – Rodrick May 18 '16 at 18:25
  • **Errata** to my comment above: Replace "NLS_CHARACTERSET" with "charset component of NLS_LANG". Sorry for the confusion! –  May 19 '16 at 03:04

2 Answers2

2

Parameter NLS_LANGUAGE has nothing to do with character sets, it defines the language of Oracle (error) messages.

Try following, then you will see it.

alter session set NLS_LANGUAGE = 'SPANISH';
select 1/0 from dual;

alter session set NLS_LANGUAGE = 'AMERICAN';
select 1/0 from dual;

Regarding your problem, you have two solutions.

  1. Check the codepage which is used by your terminal and set NLS_LANG value accordingly.

    Example:

    C:\>chcp
Active code page: 850    
C:\>set NLS_LANG=.WE8PC850
C:\>sqlplus ...

    In case your DBA works with Unix/Liunx the chcp equivalent is locale charmap or echo $LANG

  2. Change the current codepage according to your NLS_LANG value. On Windows this is for example:

    C:\>set NLS_LANG
NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
C:\>chcp 1252
Active code page: 1252
C:\>sqlplus ...

You can do similar on Unix/Linux.

See also this anser to get some more information.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
0

This most likely depends on the character set each database uses, not the language. And the character set of a database cannot be changed.

However, it is possible that in fact the problem is only on the DBA's display, not in the database itself. The DBA needs to have the proper character set to display Spanish letters. This, unfortunately, can't be set during a session already in progress; NLS_LANG can only change the region and the language, not the character set. How to do this depends on many things, including operating system (and, for Windows, whether he/she uses SQL*Plus or another DOS-like client, or Toad or SQL Developer or another GUI). Full details here:

http://www.oracle.com/technetwork/products/globalization/nls-lang-099431.html

This document also shows how to find the current session's (client-side) character set. It is non-trivial, as explained in the document.

  • The character set in both DBs are the same = "WE8MSWIN1252" and i don't think it's a display thing because this is causing that some application that generates some CSV files showing the ¿ characters and also on shell application. – Rodrick May 18 '16 at 14:47
  • @Rodrick - 'display' in this case really means 'client', so whatever is generating the CSV needs an appropriate NLS_LANG setting. The shell and whatever you view the report in also need to be able to handle the character set. – Alex Poole May 18 '16 at 15:03
  • Yes, sorry for not being precise. By "display" I mean the characters displayed on the client side, using the client character set. –  May 18 '16 at 16:50