[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Cannot insert quoted floating point numeric that contains a period.

Posted on 2004-10-21
Medium Priority
Last Modified: 2013-12-11
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?
Question by:jpk01
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 12371983
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?

Author Comment

ID: 12373324
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.


Expert Comment

ID: 12378406
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?
LVL 22

Expert Comment

by:Helena Marková
ID: 12378539
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.

Accepted Solution

modulo earned 0 total points
ID: 12623448
PAQed with no points refunded (of 500)

Community Support Moderator

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question