This discussion is archived
2 Replies Latest reply: Feb 28, 2012 10:20 PM by IvanBlanarik RSS

NUMBER - numeric overflow

IvanBlanarik Journeyer
Currently Being Moderated
Hi all,
I just want to ask about the precision and scale of NUMBER datatype in SQL Developer. It seems to me, that it doesn't have default values but it's defined as NUMBER(10, 0)...
This piece of code works fine in SQL Worksheet and SQL*Plus, but not in developer:
variable var number;
exec :var := 0.12345678901;
print :var;

VAR
-
0

variable var number;
exec :var := 12345678901;
print :var;

Error report: Numeric overflow
Is this a standard and expected behavior? Is there any way how to change it (some configuration)?
Thanks,

ivoB
  • 1. Re: NUMBER - numeric overflow
    Gary Graham Expert
    Currently Being Moderated
    Hi Ivan,

    There is currently no way I know of to configure the output format of a bind variable for run script. I guess this is because SQL Developer does not support "set numformat" yet. It appears to assume a NUMBER(10,0) format.

    Let's change your example slightly and see what the difference is between Run Statement (Ctrl+Enter) versus Run Script (F5):
    variable var number
    column var format 9.999999999999
    exec :var := 0.12345678901;
    print :var;
    select :var as xyz, 0.12345678901 as xyz from dual;
    
    --Run Statement will prompt for the value of 'var', then display both bind and literal to 11 digit precision:
    Row XYZ           XYZ_1
    1   0.12345678901 0.12345678901
    
    --Run Script truncates or overflows the bind variable as you say, but at least it respects the "col ... format" syntax:
    anonymous block completed
    VAR
    -
    0
    
    XYZ           XYZ
    ---           ---
    .000000000000 .123456789010  
    SQL*Plus behaves as one would expect, depending on the value of numformat. For the decimal fraction case, it truncates to 10 digits if numformat is set to the default. Similarly, for the overflow case with a default numformat setting, it uses scientific notation:
           VAR
    ----------
    1.2346E+10
    I logged a bug:
    Bug 13790813 - FORUM: BIND VARIABLES IN RUN SCRIPT LIMITED TO NUMFORMAT(10,0) RANGE

    Regards,
    Gary
    SQL Developer Team
  • 2. Re: NUMBER - numeric overflow
    IvanBlanarik Journeyer
    Currently Being Moderated
    Thank you, Gary

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points