jpk01
asked on
Cannot insert quoted floating point numeric that contains a period.
I´m refactoring a legacy app in PHP.
The existing code quotes all fields while cunstructing the SQL command.
Whew I attempt to insert arecord as follows:
INSERT into Table VALUES ('10,5');
and the column is declared as a NUMERIC (10,2) for instance, evrything works fine.
but if I attempt to insert arecord as follows:
INSERT into Table VALUES ('10.5');
returns an ORA-01722: invalid number.
Changing NLS_NUMERIC_CHARACTERS has not solved the problem.
Any suggestions?
The existing code quotes all fields while cunstructing the SQL command.
Whew I attempt to insert arecord as follows:
INSERT into Table VALUES ('10,5');
and the column is declared as a NUMERIC (10,2) for instance, evrything works fine.
but if I attempt to insert arecord as follows:
INSERT into Table VALUES ('10.5');
returns an ORA-01722: invalid number.
Changing NLS_NUMERIC_CHARACTERS has not solved the problem.
Any suggestions?
ASKER
Regional settings are for Spanish (Mexican) we have tried using Spanish (Spain) and US English with same results.
INSERT into Table VALUES (10.5); executes but does not solve our problem. We have several thousand lines of code in PHP from the legacy app. that wrap numeric inserts in quotes, so we don´t want to change the SQL code in the legacy app.
On the customers production environment the quoted number inserts works, but on our test environment they don´t.
Due to security constraints we do not have access to the customers server.
INSERT into Table VALUES (10.5); executes but does not solve our problem. We have several thousand lines of code in PHP from the legacy app. that wrap numeric inserts in quotes, so we don´t want to change the SQL code in the legacy app.
On the customers production environment the quoted number inserts works, but on our test environment they don´t.
Due to security constraints we do not have access to the customers server.
Tell me, is the Values always either 10,5 OR 10.5, or can it be a mixture depending on where the data came from?
Maybe this will help you:
NLS Format Masks
The following table describes valid National Language Support (NLS) format masks.
Element Example Description
C C999 Returns the international currency symbol.
L L9999 Returns the local currency symbol.
D 99D99 Returns the decimal separator.
G 9G999 Returns the group (thousands) separator.
comma 9,999 Displays a comma in this position.
period 9.999 Displays a decimal point in this position. Displays a decimal point in this position.
NLS Format Mask Examples
Format Mask Description
L99G999D99 Displays the local currency symbol, group, and decimal separators: if NLS_LANG=American, this item displays as $1,600.00; if NLS_LANG=Norwegian, this item displays as Kr.1.600,00.
C99G999D99 Displays the appropriate international currency symbol: if NLS_LANG=American, this item displays as USD1,600.00; if NLS_LANG=French, this item displays as FRF1.600,00.
NLS Format Masks
The following table describes valid National Language Support (NLS) format masks.
Element Example Description
C C999 Returns the international currency symbol.
L L9999 Returns the local currency symbol.
D 99D99 Returns the decimal separator.
G 9G999 Returns the group (thousands) separator.
comma 9,999 Displays a comma in this position.
period 9.999 Displays a decimal point in this position. Displays a decimal point in this position.
NLS Format Mask Examples
Format Mask Description
L99G999D99 Displays the local currency symbol, group, and decimal separators: if NLS_LANG=American, this item displays as $1,600.00; if NLS_LANG=Norwegian, this item displays as Kr.1.600,00.
C99G999D99 Displays the appropriate international currency symbol: if NLS_LANG=American, this item displays as USD1,600.00; if NLS_LANG=French, this item displays as FRF1.600,00.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What happens if you
INSERT into Table VALUES (10.5);
i.e without the quotes?
Also do you have any odd Regional settings?