Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

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?
1 Solution
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now