How to setup a correct NLS LANG in PL/SQL Developer?

[Tomas]

Hi folks,

I have a problem with a displaying Easter Europe characters.

Environment variable:
NLS_LANG=CZECH_CZECH REPUBLIC.AL32UTF8

Please see me db settings:

SQL> select * from NLS_SESSION_PARAMETERS;

PARAMETER                                                                        VALUE
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
NLS_LANGUAGE                                                                     CZECH
NLS_TERRITORY                                                                    CZECH REPUBLIC
NLS_CURRENCY                                                                     Kč
NLS_ISO_CURRENCY                                                                 CZECH REPUBLIC
NLS_NUMERIC_CHARACTERS                                                           ,.
NLS_CALENDAR                                                                     GREGORIAN
NLS_DATE_FORMAT                                                                  DD.MM.RR
NLS_DATE_LANGUAGE                                                                CZECH
NLS_SORT                                                                         CZECH
NLS_TIME_FORMAT                                                                  HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT                                                             DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT                                                               HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT                                                          DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY                                                                Kč
NLS_COMP                                                                         BINARY
NLS_LENGTH_SEMANTICS                                                             BYTE
NLS_NCHAR_CONV_EXCP                                                              FALSE

17 rows selected

SQL> select * from NLS_DATABASE_PARAMETERS;

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_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.1.0

20 rows selected

SQL>
SQL> select  x.*
  2      from XMLTABLE(
  3           xmlnamespaces('http://wwwinfo.mfcr.cz/ares/xml_doc/schemas/ares/ares_answer_or/v_1.0.3' as "are"
  4           , 'http://wwwinfo.mfcr.cz/ares/xml_doc/schemas/ares/ares_datatypes/v_1.0.3' as "D"),
  5           '/are:Ares_odpovedi/are:Odpoved/D:Vypis_OR'
  6                      PASSING httpuritype('http://wwwinfo.mfcr.cz/cgi-bin/ares/darv_or.cgi?ico=60743280').getXML()
  7                      COLUMNS
  8                         ico varchar2(50) PATH 'D:ZAU/D:ICO'
  9                        ,org_name varchar2(200) PATH 'D:ZAU/D:OF'
 10                        ,ulica varchar2(200) PATH 'D:ZAU/D:SI/D:NU'
 11                      )  x
 12     where rownum = 1;

ICO                                                ORG_NAME                                                                         ULICA
-------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
60743280                                           ST

 

[Marco Kalter]

What exactly goes wrong? Can you provide an example?

 

[Tomas]

Hi Marco,

a problem is an output in PL/SQL. A "czech characters" are being displayed incorrectly in query results.

I've posted example in my first post, but "dodgy" characters from result were removed when a post was submitted.

Can you run above select? Do you get result OK?

Thanks,
Tomas

 

[Marco Kalter]

I get an "ORA-29273: HTTP request failed".

 

[Tomas]

In 11g you need to add a record to ACL:

REM ==>  RUN as SYS user !!!

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'AGD_ACL_ARES.xml',
    description  => 'Access to ARES cz',
    principal    => 'DEV',
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  COMMIT;
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'AGD_ACL_ARES.xml',
    host        => '193.86.123.*',
    lower_port  => NULL,
    upper_port  => NULL);

  COMMIT;
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'AGD_ACL_ARES.xml',
    host        => 'wwwinfo.mfcr.cz',
    lower_port  => NULL,
    upper_port  => NULL);

  COMMIT;
END;
/

SELECT acl,
       principal,
       privilege,
       is_grant,
       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM   dba_network_acl_privileges;

 

[Tomas]

Any updates?

 

[Marco Kalter]

Unfortunately I cannot get your code to work here. Do you have a simpler example?