PLS_INTEGER giving Error

oracleplsqlstored-procedures

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.

Best Answer

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!

Related Question