Is it possible to overflow an Oracle NUMBER type?

Question

I'm working with process scheduling software called Appworx. In it, each process and subprocess can have an arbitrary number of "conditions", which if true, some conditional action is taken.

One of the possible conditional actions is a goto statement, where a plain integer is the label (each condition being numbered starting at 1). I'd like to use this feature to evaluate and run a few tasks in a loop, but you can only goto higher-numbered conditions (Don't ask me why... this seems to ruin most of the utility).

I have reason to believe that all of this is evaluated by Oracle on the backend. And having looked at the schema for Appworx, it appears that the goto labels are all NUMBER(12,0). I suspect that the logic that checks whether a label is lower than the current condition is something like:

where label > current_condition

So, if I were to supply a goto with a high enough value, I think it would cheat the checking and allow me to do simple loops. At least if Oracle used normal integers. Is it possible to overflow them, and what value would I use to overflow the value back to 1?

I suppose the Oracle version matters quite a bit, if so, it's 11g.

PS Also, if anyone would care to re-tag this for me, please add "appworx"

Yes, PL/SQL can overflow an integer declared as number(12,0). Unlike some programming languages, it catches that error and raises an exception instead of rolling over. — Shannon Severance
– Shannon Severance, Commented Mar 18, 2013 at 21:20

Egor Skriptunoff · Accepted Answer · 2013-03-18 19:07:44Z

3

Oracle numbers are in fact floating point numbers with 40 decimal digit significand.
So, they can't be overflowed.

(10^40-1) is the maximal integer number which can be increased by 1.
proof

NUMBER(12,0) is a subtype of type NUMBER.
That is, it consists of NUMBER type and a restriction checker.

answered Mar 18, 2013 at 19:07

Egor Skriptunoff

23.8k2 gold badges38 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Aldridge Over a year ago

I think that it's important to note that the Oracle Number data type is a base 10 floating point, not the base 2 IEEE754-type usually encountered in computing systems. I'm not sure that that has an impact on whether they can overflow or not -- that's really a matter of how the software treats overflow situations (representing as infinity, for example, as IEEE754 does).

Bob Jarvis - Слава Україні · Accepted Answer · 2013-03-20 18:26:47Z

Well, that depends on your definition of 'overflow'. If you define 'overflow' as 'find a value n where n + 1 < n', then no, there's no such value. If you define 'overflow' as 'raises an exception', then yes, it's quite possible to perform an operation on a NUMBER(12,0) where an exception is raised.

Run the following:

DECLARE
  n  NUMBER(12, 0);
BEGIN
  n := 999999999999;  -- Twelve 9's
  DBMS_OUTPUT.PUT_LINE('1 : n=' || n);
  n := n + 1;
  DBMS_OUTPUT.PUT_LINE('2 : n=' || n);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Exception: ' || SQLCODE || '  ' || SQLERRM);
END;

As you can see, the following exception is thrown when attempting to execute "n := n + 1":

ORA-06502: PL/SQL: numeric or value error: number precision too large

So it's quite possible to overflow a subtype of NUMBER. However, given that you're hoping to find a value n where n + 1 < n, I think you're out of luck.

And if you really want to provoke this behavior using the base NUMBER type, just execute

n := POWER(10, 126);

Of course, for truly nasty behavior in NUMBER you need to get it to produce a NaN (Not a Number):

n := 9999999999999999999999999999999999999999 * POWER(10, 125);
DBMS_OUTPUT.PUT_LINE('n=' || n);

produces

n=~

WTF?!? '~'? What the heck is '~'? Well, it appears that this is Oracle's way of printing a NaN. And the really fun part? Once you've got a NaN in a variable, any operation you perform on that variable will produce another NaN. Quietly. Silently. Without warning. Without recourse. Try:

DBMS_OUTPUT.PUT_LINE('n * 1234=' || n * 1234);  -- produces n * 1234=~
DBMS_OUTPUT.PUT_LINE('n / 5678=' || n / 5678);  -- produces n / 5678=~

Hey - have fun sweating your financials! :-)

In actual practice you're very unlikely to encounter this behavior, but it's the kind of thing you really need to be aware of - not only because encountering it can really ruin your month, but because (and you can count on this) next week the clueless guy in the cube by the bathroom is going to be asking about this - and you will now know all about it. (And you will now be able to rest easy, comfortable in the knowledge that this guy really is clueless and thus deserves to be parked in The Cube From Hell. I mean, you picked up on this on StackOverflow, right? So how hard can it be? :-)

Share and enjoy.

Collectives™ on Stack Overflow

Is it possible to overflow an Oracle NUMBER type?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related