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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi jpk01,

What happens if you
INSERT into Table VALUES (10.5);

i.e without the quotes?

Also do you have any odd Regional settings?
jpk01Author Commented:
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.

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?
Helena Markováprogrammer-analystCommented:
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.
PAQed with no points refunded (of 500)

Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.