StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCan dbms_utility.get_time rollover?
text
Body
copied!<p>I'm having problems with a mammoth legacy PL/SQL procedure which has the following logic:</p> <pre><code>l_elapsed := dbms_utility.get_time - l_timestamp; </code></pre> <p>where <code>l_elapsed</code> and <code>l_timestamp</code> are of type <code>PLS_INTEGER</code> and <code>l_timestamp</code> holds the result of a previous call to <code>get_time</code></p> <p>This line suddenly started failing during a batch run with a <code>ORA-01426: numeric overflow</code></p> <p>The documentation on <code>get_time</code> is a bit vague, possibly deliberately so, but it strongly suggests that the return value has no absolute significance, and can be pretty much any numeric value. So I was suspicious to see it being assigned to a <code>PLS_INTEGER</code>, which can only support 32 bit integers. However, the interweb is replete with examples of people doing exactly this kind of thing.</p> <p>The smoking gun is found when I invoke <code>get_time</code> manually, it is returning a value of <strong>-214512572</strong>, which is suspiciously close to the min value of a 32 bit signed integer. I'm wondering if during the time elapsed between the first call to <code>get_time</code> and the next, Oracle's internal counter rolled over from its max value and its min value, resulting in an overflow when trying to subtract one from the other.</p> <p>Is this a likely explanation? If so, is this an inherent flaw in the <code>get_time</code> function? I could just wait and see if the batch fails again tonight, but I'm keen to get an explanation for this behaviour before then.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload