Cannot insert quoted floating point numeric that contains a period.

Posted on 2004-10-21
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
    LVL 2

    Expert Comment

    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

    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.

    LVL 2

    Expert Comment

    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

    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

    PAQed with no points refunded (of 500)

    Community Support Moderator

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Prepare to Pass the CompTIA A+ 900 Series Exam

    CompTIA aims to adapt its A+ Certification to reflect the most current knowledge and skills needed by today's IT professionals--and this year's 2016 exam is harder than ever. This certification is one of the most highly-respected and sought after in IT.

    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…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    934 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now