TO_char returning slash value after converting a number to String

Question

I am having a database column amount [Data type Number(32,12)].When i use to_char on the amount field i get a slash value appended in the output.

Screenshot

When i directly used the value stored in the amount field ,i am getting the correct value

select TO_Char(0.000000000099,'FM99999999999999999999999999999990.099999999999') from dual;

Output:- 0.000000000099

Is it actually appended or is that just your GUI playing up? Can you `select ascii(substr(to_char(your_column, ''), -1)) from your_table`? If it's numeric it ought to be in the range 48-57. — Ben, Jan 11 '13 at 09:03
It is not because of the GUI.The value is getting stored in the view as / & #. The output of the above query you provided is coming as 47. What is this range 48-57? — user1501147, Jan 11 '13 at 09:15
It's the values of 0-9 [in ASCII](http://www.asciitable.com/) but fairly generally for most Western character sets. 47 is a `/` so you are getting one appended... — Ben, Jan 11 '13 at 09:20
Ok. But i am still not clear why the / is appended when i retrieve value from database. IF i run the below the same query with hardcoding the value in to_char function select TO_CHAR(0.000000000099,'FM99999999999999999999999999999990.099999999999') as amount from dual; I am getting the proper output 0.000000000099 of the above query. Also why does the TO_CHAR function round the values ? — user1501147, Jan 11 '13 at 09:27
If there genuinely is a / appended to the string then applying To_Number() to the result of your To_Char() will fail, so give that a go. — David Aldridge, Jan 11 '13 at 10:11
Is `WK_hold_trans_calculation` a table, or a view? If it's a view, is it based on a procedure/function? It seems like something is going on under the hood, if it isn't an SQL Developer bug, and it seems like that is already ruled out. Not sure how `to_char` would get pushed into the view anyway... — Alex Poole, Jan 11 '13 at 10:20
wk_hold_trans_calculation is a table and there is a amount field in the table of datatype Number(32,12). I am using Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production and PL/SQL Release 11.2.0.2.0 - Production — user1501147, Jan 11 '13 at 10:42
@user1501147 what happens if you create a simple test table `create table foo (id number(32, 12)); insert into foo values (0.000000000099);` and run the same select on that? also do you have `cursor_sharing` set to similar/force or any non standard parameters set? probably best to get Oracle support involved though (doesn't replicate here on my 11.2.0.2 db) — DazzaL, Jan 11 '13 at 15:56
It looks like you might have corrupted values in the table (was the data imported, by any chance?). Can you `select amount, dump(amount, 16)` for those two values, and add the results to the question? They ought to be `bb,64` and `bc,11,51` but I suspect they'll have more values after those. If they do it can maybe be recreated. — Alex Poole, Jan 11 '13 at 18:12

score 2 · Accepted Answer · edited May 23 '17 at 12:31

It looks like you have corrupted data in your table. Which leads to a few questions including how did it get there, and what can you do about it?

Corrupt numeric (or date) values often come from OCI programs, but there are some bug reports that suggest imp has been known to cause corruption. The internal representation is documented in support note 1007641.6, but I find something like this explanation easier to work with when recreating problems, and using a PL/SQL block is possible in place of an OCI program.

The two numbers you're having problems with should be represented internally like this:

select dump(0.000000000099, 16) as d1,
    dump(0.000000001680, 16) as d2
from dual;

D1                 D2
------------------ ---------------------
Typ=2 Len=2: bb,64 Typ=2 Len=3: bc,11,51

I haven't figured out exactly what values you have in your table, but I can show a similar result:

create table t42 (amount number(32,12)) nologging;

declare
    n number;
begin
    dbms_stats.convert_raw_value('bb65', n);
    insert into t42 (amount) values (n);
    dbms_stats.convert_raw_value('bc100000', n);
    insert into t42 (amount) values (n);
end;
/

Dumping the values shows they look a bit odd:

column d1 format a25
column d2 format a25
select amount, dump(amount) d1, dump(amount, 16) d2
from t42;

                     AMOUNT D1                        D2                      
--------------------------- ------------------------- -------------------------
              0.00000000010 Typ=2 Len=2: 187,101      Typ=2 Len=2: bb,65        
             0.000000001499 Typ=2 Len=3: 188,16,0     Typ=2 Len=3: bc,10,0

Running your formatting against that gives similar results:

select amount as actual__________amount,
    TO_CHAR(amount,'FM99999999999999999999999999999990.099999999999')
        as amount__________Changed
from t42
order by amount;    

     ACTUAL__________AMOUNT AMOUNT__________CHANGED                      
--------------------------- ----------------------------------------------
              0.00000000010 ############################################## 
             0.000000001499 0.00000000150/

If you can add the dump() output for your own data to the question then I can see if I can recreate exactly the values you're seeing.

Anecdotally, it might be possible to 'correct' this by updating the data, e.g.:

update t42 set amount = amount * 1;

select amount, dump(amount) d1, dump(amount, 16) d2
from t42;

                     AMOUNT D1                        D2                      
--------------------------- ------------------------- -------------------------
               0.0000000001 Typ=2 Len=2: 188,2        Typ=2 Len=2: bc,2         
             0.000000001499 Typ=2 Len=3: 188,15,100   Typ=2 Len=3: bc,f,64

select amount as actual__________amount,
    TO_CHAR(amount,'FM99999999999999999999999999999990.099999999999')
        as amount__________Changed
from t42
order by amount;

     ACTUAL__________AMOUNT AMOUNT__________CHANGED                      
--------------------------- ----------------------------------------------
               0.0000000001 0.0000000001                                   
             0.000000001499 0.000000001499

However, you have to ask what the actual correct value is, which probably comes back to how/why/when it was corrupted. I would be very wary of touching this data if it is at all important, and would really have to second @DazzaL's advice to get Oracle Support involved to sort it out.

I have one more question .You said that "some bug reports suggest imp has been known to cause corruption." What is this imp??? — user1501147, Jan 16 '13 at 11:08
@user1501147 - `imp` is the old [import tool](http://docs.oracle.com/cd/E11882_01/server.112/e22490/original_import.htm). Not sure if there are also problems with the newer [`impdp`](http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm); I've seen a few references to `imp` on the Oracle Support site. That's just one way the corruption might have occurred; if your data has not been migrated from another database then `imp` isn't relevant, and there's some other cause. — Alex Poole, Jan 16 '13 at 11:35
Hi alex, In the below article they clarified that conversion to 8 bit -UTF8 causes data corruption. http://www.coresolucoes.com.br/?p=251 Can this be the issue? will — user1501147, Jan 17 '13 at 09:41
@user1501147 - I would have thought that specific bug would only affect `VARCHAR2` columns, but it isn't clear. You really need Oracle Support to help identify the cause and possible correction, only they will have the right info and know the right questions to ask you. It might not be an Oracle bug at all, even if you have used `imp` or `impdp`, it could come from a bug in an OCI program for example - see support notes 979657.8, or more generally 311346.1 (wish I'd found that one earlier, though not sure I entirely agree with their detection section) - or JDBC (4338390.8). — Alex Poole, Jan 17 '13 at 10:01

TO_char returning slash value after converting a number to String

1 Answers1

Linked