oracle11gsqlplusspool

Oracle spool format issue

I am using oracle spool to export the oracle table data to excel file and it contain the 15 digit number but it displayed like below instead of 981239712396123

9.81239E+18

How to display the text only with 15 characters length and not with exponential values?

Solution

  • You need to set the numwidth setting in your script to the correct width in order to display your number in non-scientific notation. Here's a short demonstration for you:

    SQL> set numwidth 7
SQL> select 12345678 from dual;

12345678
--------
 1.2E+07

SQL> set numwidth 8
SQL> /

12345678
--------
12345678

    Alternatively, you can format individual columns and that will override the numwidth setting, e.g.:

    SQL> set numwidth 7
SQL> col col1 format 999999999
SQL> select 12345678 col1 from dual;

      COL1
----------
  12345678