Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

577 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