#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2009
    Posts
    2
    Rep Power
    0

    Question Problem in conversion number(19) to varchar2(19)


    When I try to convert numeric values – number(19) p.s 111111111111111111, the to_char function returns ‘1111111111111110000’ because the to_char functions doesn’t support precision bigger than 15.

    Is there any way to solve it?

    Thanks!!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,800
    Rep Power
    349
    Originally Posted by vaseva
    because the to_char functions doesn’t support precision bigger than 15.
    From where did you get that idea?


    The following works without problems (showing that you can even use 30 digits in the to_char() method)
    Code:
    select to_char(123456789012345678901234567890) 
    from dual
    Please post a sample SQL statement (including sample data) that shows your problem.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2009
    Posts
    2
    Rep Power
    0
    I think I just found bug of oracle, when you insert a record into a table using a graphical interface of oracle, the to_char function returns:

    select testcol, to_char(testcol) from test_eva

    testcol | to_char(testcol)
    ------------------------------------------------
    1.11111111111111E18 | 1111111111111110000

    And when you insert into table a record using an insert statement

    insert into test_eva values (2222222222222222222)

    the to_char function returns:

    testcol | to_char(testcol)
    ------------------------------------------------
    1.11111111111111E18 | 1111111111111110000
    2.22222222222222E18 | 2222222222222222222
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,800
    Rep Power
    349
    The display is absolutely correct.

    If you want to_char() to format your number with scientific notation you have to pass the approriate formatting pattern for the to_char() function.

IMN logo majestic logo threadwatch logo seochat tools logo