2 Replies Latest reply: Feb 29, 2012 12:20 AM by IvanBlanarik RSS

    NUMBER - numeric overflow

    IvanBlanarik
      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-Oracle
          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
            Thank you, Gary