title: Can dbms_utility.get_time rollover?
body: <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>