0

My procedure giving numeric overflow error:

ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at "HDM_DBUSER2.HDM_CLEANUP_UTIL", line 1196

because it exceeds pls_integer maximum value +2,147,483,647. How to overcome this error?

I have to purge 2234202989 rows. So I am getting error.

sp_log_message (LOG_ERROR, 'sp_instrumentationdata', lsCurrentTable, 'Exception', NULL, SQLERRM);

Here it is showing .. This table having starttime and endtime two .. date fileds .. I am using endtime .. I find out max.endtime .. min.endtime and max-min(endtime). I got 144 days .. I have given package.sp_prc(keep days); exec hdm_cleanup_util.sp_instrumentationdata(143). Means i am going to purge Just one day data. whenever I execute the above command .. immediately I am getting ERROR at line 1: ORA-01426: numeric overflow ORA-06512: at "HDM_DBUSER2.HDM_CLEANUP_UTIL", line 1196 .. error.

4
  • For such big table you should consider partitioning Nov 17 '20 at 13:04
  • 1
    how does the number of rows cause this error. can you show your code wehre this error is raised?
    – miracle173
    Nov 17 '20 at 14:07
  • sp_log_message (LOG_ERROR, 'sp_instrumentationdata', lsCurrentTable, 'Exception', NULL, SQLERRM); Here it is showing .. This table having starttime and endtime two .. date fileds .. I am using endtime .. I find out max.endtime .. min.endtime and max-min(endtime). I got 144 days .. I have given package.sp_prc(keep days); exec hdm_cleanup_util.sp_instrumentationdata(143). Means i am going to purge Just one day data. whenever I execute the above command .. immediately I am getting ERROR at line 1: ORA-01426: numeric overflow ORA-06512: at "HDM_DBUSER2.HDM_CLEANUP_UTIL", line 1196 .. error. Nov 17 '20 at 14:34
  • You can edit your question with those details as that's where they belong, and you'll be able to format them better too.
    – Andriy M
    Nov 18 '20 at 12:59
0

Why are you purging 2 billion rows in one go?

If that's the entire content of the table, why not just truncate it?

If you really must do this in one go, you'll have to "upgrade" from PLS_INTEGER to INTEGER (a predefined subtype of NUMBER). OK, you lose the efficiencies of PLS_INTEGER, but you also lose the 32-bit limitation that it imposes on you.

Most "purging" activities I've encountered "nibble away" at data rather than trying to remove great chunks of stuff. If, say, you had top get rid of six years of very sizeable .. stuff .., you might set up your purging process to remove, one week's worth of data. You'd configure it with a "start date" including the very oldest data that you want to get rid of and run it. Then you'd "move" the start date "up" a week and run it again.

The chances are you can run this several times each the day (and more so as the backlog reduces and the process speeds up) until you're running dozens of times a day and clearing years-worth of data in doing so.

Eventually, your "start date" would catch up with today, at which point you simply leave it running daily, just taking the last few days. By that time, your now "Daily Maintenance Run" will have been well and truly tested!

2
  • Yes .. correct .. I am going to delete 1 day data .. But it is more than 1 crore records and also it is not accepting to run purge. Because .. it is throwing exception .. ORA-01426: numeric overflow. Now I am in trouble. I am using endtime data column .. Nov 17 '20 at 12:42
  • I think the "efficiencies of PLS_INTEGER" applies only for PL/SQL calculations - and is really negligible if you want to delete 2234202989 rows from a table. Nov 17 '20 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.