TEST WINDOW BUG

[DoDo]

Hi,
I am trying to test the procedure which converts formatted string to a number.
Test window does not work regardless of settings.
The only solution is to put the following code in the test scripts.
Execute immediate 'ALTER SESSION set NLS_NUMERIC_CHARACTERS =''.,''';
then the test procedure.
Please note that the procedure works well when run in Command window or sql+.

error: ORA-06502 Numeric or value error

code: v_test := to_number(replace(v_MT_slog.P32a_3,',','.'));

It seems that the Test window takes its own NLS_NUMERIC_CHARACTERS.

 

[Marco Kalter]

Can you send me the Test Script file by e-mail?

 

[DoDo]

The problem seems to be a little bigger than I thought and has nothing to do with the test script but with the NLS parameters.

NLS parameters, defined in the "NLS Options ", are refer to?

My setting is:
'User defined' Decimal Symbol = '.' Digit Group Symbol = ','

Test window and DBMS_jobs window do not use a parameter as defined, I suppose, they take a default value based on the region, since for Croatia is defined Decimal Symbol = ',' Digit Group Symbol = '.'

This behavior was not in version 7, although it was not possible to set NLS parameters, everything was working properly because the value was taken as defined in the database.

Please, return the old functionality or at least enable the selection of options, or make a "test windows" and "DMMS_jobs windows" to use the parameters defined in the options.

THX

 

[DoDo]

Any news?

 

[Marco Kalter]

Can you send me something to reproduce the problem?

 

[DoDo]

Assumptions:

Database properties (database_properties):
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET EE8ISO8859P2
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 10.2.0.3.0

Database USER properties (v$parameter):
nls_language CROATIAN
nls_territory CROATIA
nls_sort
nls_date_language
nls_date_format
nls_currency
nls_numeric_characters
nls_iso_currency
nls_calendar
nls_time_format
nls_timestamp_format
nls_time_tz_format
nls_timestamp_tz_format
nls_dual_currency
nls_comp
nls_length_semantics BYTE
nls_nchar_conv_excp FALSE

PLDEV properties:

PL/SQL Developer
Version 8.0.0.1480
Preferences
Session mode: Multi
OCI Library: C:\ccc\9i\bin\oci.dll
Use OCI7: False
Character Sets
Character size: 1 byte(s)
CharSetID: 32
NCharSetID: 2000
Unicode Support: True
NLS_LANG: CROATIAN_CROATIA.EE8MSWIN1250
NLS_CHARACTERSET: EE8ISO8859P2
NLS_NCHAR_CHARACTERSET: AL16UTF16

DLLs
C:\aaa\bbb\product\11.1.0\client_2\oci.dll

Using
Home: OraOdac11g_home2
DLL: C:\aaa\bbb\product\11.1.0\client_2\oci.dll
OCI: version 11.1
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

Test this procedure:

create or replace procedure bug_demo Is
v_var Varchar2(100);
v_num Number(15,2);
begin
v_var := '145548415,54';
v_num := to_number(replace(v_var,',','.'));
end bug_demo;

within test window:
begin
-- Call the procedure
bug_demo;
end;
-- This reproduce error: ORA-06502 numeric or value error: character to number conversion error

then try:
begin
-- Call the procedure
Execute immediate 'ALTER SESSION Set NLS_NUMERIC_CHARACTERS = ''.,''';
bug_demo;
end;
-- This works fine

then try both versions of the procedure on PLDEV 7.1
--all works fine

then replace with:
v_num := to_number(replace(v_var,',','.'),'9999999999999.99');
and try on PLDEV 8:
--all works fine

I think the problem is in the version 11 of OCI.DLL
While trying to execute within diferent sqlpluses instaled on diferent folders, the error was only with sqlplus in version 11 folder.

When forced (by editing PATH in environment) PLDEV to use OCI.DLL version 9 it started to work properly. But this is not a very good solution.

Still, there is a question why does not PLDEV use my settings for OCI instead of first found in PATH environment variable.

 

[Marco Kalter]

My guess is that you have multiple Oracle Homes with different NLS settings in their respective registry sections. Can you verify this?

 

[DoDo]

No, all Oracle Homes have same NLS settings
and none has NLS_NUMERIC_CHARACTERS.