Scientific notation for SCN

[Ganesh]

Can anyone help me understand following:

**********
SQL> select MAX(NEXT_CHANGE#) from V$ARCHIVED_LOG;

MAX(NEXT_CHANGE#)
-----------------
1.3278E+10

SQL> select TO_CHAR(MAX(NEXT_CHANGE#)) from V$ARCHIVED_LOG;

TO_CHAR(MAX(NEXT_CHANGE#))
----------------------------------------
13278230177

SQL> select TO_CHAR(MAX(NEXT_CHANGE#), 999999999999999999999) from V
$ARCHIVED_LOG;

TO_CHAR(MAX(NEXT_CHANGE#))
----------------------------------------
13278230177

**********

But when I give input 1.3278E+10 to my calculator it returns me the
value as 13278000000. So I am not sure why "TO_CHAR" returned
13278230177. So what oracle is doing extra that we are not getting
here.

Thanks in advance.
Ganesh Tambat

[Kasparov]

I am using Oracle 8i.

[Charles Hooper]

The above issue is caused by a default formatting specification for
SQL*PLus. For example:
SQL> SELECT 13278230177 MY_NUMBER FROM DUAL;

MY_NUMBER
----------
1.3278E+10

The number is converted to scientific notation with several lost
digits.

Now addressing the issue:
SQL> COLUMN MY_NUMBER FORMAT 99999999999

SQL> SELECT 13278230177 MY_NUMBER FROM DUAL;

MY_NUMBER
------------
13278230177

Trying again with one less digit:
SQL> SELECT 1327823017 MY_NUMBER2 FROM DUAL;

MY_NUMBER2
----------
1327823017

The reason why the TO_CHAR method seemed to work is because the
numeric value was converted to a VARCHAR2 (string) type value where no
characters may be considered as non-significant for rounding purposes
when displaying the result.

SQL> SELECT DUMP(13278230177) VALUE FROM DUAL;

VALUE
--------------------------------
Typ=2 Len=7: 198,2,33,79,24,2,78

SQL> SELECT DUMP(TO_CHAR(13278230177)) VALUE FROM DUAL;

VALUE
----------------------------------------------
Typ=1 Len=11: 49,51,50,55,56,50,51,48,49,55,55


Typ=1 indicates VARCHAR2, Typ=2 indicates NUMBER.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.