Home » SQL & PL/SQL » SQL & PL/SQL » How to find max value of a variable. (merged)
How to find max value of a variable. (merged) [message #403073] Wed, 13 May 2009 11:10 Go to next message
mr_nice_guy
Messages: 10
Registered: September 2008
Junior Member
Hi All,

I need your help in finding the max value at RUNTIME that can be stored in a variable.

Current scenario is as follows:

1.) Declared a variable v_nbr of type TABLE.COLUMN%TYPE which is NUMBER(6,2).
2.) Involved a mathematical calculation and assigned the result to v_nbr.
3.) Exception 06502 - precision error is thrown when the value exceeds 9999.99

Requirement :
I donot want to hard code the logic to compare the value of v_nbr with 9999.99 and restrict the user if it exceeds the value. .

I want to build the code such that if in future the precision and scale of the field of the table changes say from NUMBER(6,2) to NUMBER(8,2) of the field then my code is able to handle that.

Hope I was able to explain my case.

Thanks in advance.

Re: How to find max value of a variable. [message #403076 is a reply to message #403073] Wed, 13 May 2009 11:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can look at user_tab_columns - the data_precision and data_scale are the two fields that you're looking for.
Re: How to find max value of a variable. [message #403080 is a reply to message #403073] Wed, 13 May 2009 11:18 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>I donot want to hard code the logic to compare the value of v_nbr with 9999.99
Why are you limiting the range of values?

>and restrict the user if it exceeds the value.
Restrict in what way?
Why not an EXCEPTION block for ORA-06502 error?
Re: How to find max value of a variable. (merged) [message #403085 is a reply to message #403073] Wed, 13 May 2009 11:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Assigned the type of the variable as a table column type then if you change the column type, the code automatically satisfy the new type.

Regards
Michel
Re: How to find max value of a variable. (merged) [message #403153 is a reply to message #403073] Wed, 13 May 2009 23:58 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
Requirement :
I donot want to hard code the logic to compare the value of v_nbr with 9999.99 and restrict the user if it exceeds the value. .


You use one inner block.
Assign that max value to the v_nbr inside inner block.
Also add EXCEPTION section with inner block to handle 06502 exception.

In this way, although the exception is generated, it will be handled by inner exception section and code wont terminate pre-maturely.

regards,
Delna
Previous Topic: how can i write regular expression for this
Next Topic: Updating a BLOB object via PLSQL
Goto Forum:
  


Current Time: Sat Dec 03 01:07:12 CST 2016

Total time taken to generate the page: 0.07815 seconds