Home » SQL & PL/SQL » SQL & PL/SQL » Checking for maximum value reached
Checking for maximum value reached [message #206280] Wed, 29 November 2006 07:30 Go to next message
Messages: 72
Registered: May 2005
Location: Bangalore
Hello All,

In our one of the packages( with old code) we have declarations which has BINARY_INTEGER type. Now it happens sometimes that its maximum value is reached and we get and numeric overflow in the application.
So either we have to change it to NUMBER or %TYPE.

Is there is any other way through which we can check whether the value is reaching near to its maximum and we can change it as precautionary measure instead of getting an overflow error. ?

There are lot of places in the code where BINARY_INTEGER is used.

Also there is one more idea for that:
In the package can we create a procedure or function which checks all the variables used in the package call( for some major tables which has lot of data) and insert into one temp table based on some condition like it is nearing to its maximum value which is defined by Oracle.
Then we will run one script , which check regularly for any possible overflows. The script will present the data in a proper format which describes all the details like its maximum value, current value etc.

Re: Checking for maximum value reached [message #206290 is a reply to message #206280] Wed, 29 November 2006 08:31 Go to previous message
Messages: 7880
Registered: March 2000
Senior Member
This kind of maintenance is based on pure luck. Let's hope you are in time if some value gets near the maxvalue.
You should look at what value a variable can get theoretically, and adjust its type accordingly, not create some subsystem that keeps track of variables that one day could or could not overflow.
You could for example be logging integers that always get values near maxvalue, but that can NEVER overflow...
In short: bad approach if you'd ask me.
Previous Topic: can not truncate table
Next Topic: Date Calculation and Conversion to Year, Month, Day format
Goto Forum:

Current Time: Mon Oct 24 02:37:38 CDT 2016

Total time taken to generate the page: 0.09161 seconds