numeric string (with decimal point like '1234.56')

[mrega]

I'm having problems testing a procedure (in a test window) when I try to assign a numeric string (with decimal point like '1234.56') to a number type variable. I simpify de procedure to the simplest form:

PROCEDURE P_TEST IS
l_var NUMBER;
BEGIN
l_var := '1234.56';
END;

and when I test it, it raise a "Numeric or value error" error.

I think that this may be because of my Windows Regional configuration (I define comma ',' as decimal separator), so I replace the dot by a comma. I test the procedure again and it worked right, but I don't think that this where the real source of the problem, because another partner (with the same regional configuration) tested the procedure before I change it, and it worked fine but then, after I modified it, he began to have the same problem that I had, but this time with the comma.

Can you help me with this?
Thank you.

 

[Marco Kalter]

This problem is caused by the NLS_LANG value in the registry of your Primary Oracle Home. It is set to a language where the decimal separator is a comma (e.g. SPANISH_SPAIN.WE8MSWIN1252). You can either change this language, or specify the NLS_NUMERIC_CHARACTERS value in the registry to override the decimal separator implied by the NLS_LANG value, or omit the quotes in your PL/SQL Code (recommended):

PROCEDURE P_TEST IS
  l_var NUMBER;
BEGIN
  l_var := 1234.56;
END;

This always compiles and runs correctly, regardles of your NLS settings.