2

I had this scenario now if i select more then 16 digits in plsql or sql plus i get wrong values:

 // the below from plsql
select 12345678901213 from dual
1.23456789101112E16

what is the cause of that ? and how to fix that in my procedures ? because I am concatenate more then 16 digits and inserting them in a table.

the below from sqlplus

enter image description here

Moudiz
  • 7,211
  • 22
  • 78
  • 156

1 Answers1

4

SQLPLUS

Use SET NUMWIDTH, example from sqlplus command line:

SQL> SHOW NUMWIDTH
numwidth 10
SQL> select 12345678901213 from dual;

12345678901213
--------------
    1,2346E+13

SQL> SET NUMWIDTH 20
SQL> select 12345678901213 from dual;

      12345678901213
--------------------
      12345678901213

PL/SQL

Cast the value to the correct datatype, example (from sqlplus):

SQL> set serveroutput on
SQL> DECLARE
  2    my_number NUMBER;
  3  BEGIN
  4    select 12345678901213 into my_number from dual;
  5    dbms_output.put_line('Number:'|| my_number);
  6  END;
  7  /
Number:12345678901213
Juan Diego Godoy Robles
  • 14,447
  • 2
  • 38
  • 52
  • Note that this only works in sql plus , for plsql it doesn't. this link showed how to fixe it for plsql for future reference https://stackoverflow.com/questions/4063167/how-to-set-numwidth-in-the-grid-output-of-pl-sql-developer – Moudiz Sep 28 '17 at 10:45
  • Note, `NUMWIDTH 10` is just the default value in SQL*Plus – Wernfried Domscheit Sep 28 '17 at 11:00