Results 1 to 2 of 2

Thread: Number Format

  1. #1
    Join Date
    Mar 2004
    Posts
    17

    Unanswered: Number Format

    Hello,

    I'm having dificulties understanding the way Oracle handles regional settings.

    Suppose I have the following query:

    INSERT INTO table (price) VALUES (0,23)

    Then Oracle returns a "too many values" error message.

    Since I am a developer, not the DBA, there is no chance to change Oracle NLS's settings to set the comma character as the decimal places delimiter. My software has to work with Spanish regional settings, ie: "," as the decimal places separator and "." as thousands separator.

    Is there any way to specify in a SQL statement that 0,23 is a number and not two separate values, without altering any settings in the Server?

    Thanks in advance,

    Miguel

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    SQL> select 1/4 from dual;

    1/4
    ----------
    .25

    SQL> alter session set nls_numeric_characters=',.';

    Session altered.

    SQL> select 1/4 from dual;

    1/4
    ----------
    ,25


    SQL> select to_number('1,5') + 1 from dual;

    TO_NUMBER('1,5')+1
    ------------------
    2,5

    Numbers must be enclosed in quotes in SQL statements when decimal character is not a period.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •