Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502 issue after 10g migration
ORA-06502 issue after 10g migration [message #434171] Mon, 07 December 2009 09:54 Go to next message
haris
Messages: 3
Registered: June 2009
Junior Member
Hi,
We have recently migrated from 9i to 10g(10.2.0.4 to be more precise)
the below issue being faced by us after the migration
We have an sp that is returning a large value (truncated to 40 digits by oracle as its number)
this out parameter value is then being assigned to a variable of size 23 and we are getting ORA-06502
numer value too large
we have done a fix for this by rounding it off to 23 before assigning it to the variable The same sp is working fine in case of 9i database and its returning a 12 digit number instead of a 40 digit
please find the psuedo code for the same
sp
(
a t1.age%type
b t1.name%type
c number
d number
)
{
c=1/3;
}

At the calling side
v varchar(23);
sp
(
a t1.age%type
b t1.name%type
c number
d number
)
select to_char(abs(c)) into :v from dual;

here c=0.3333333333333333333333333333333333 that is rounded to 40 digits.
Is there some kind of parameter setting that the error is handled in 9i but not in 10g
Re: ORA-06502 issue after 10g migration [message #434173 is a reply to message #434171] Mon, 07 December 2009 10:01 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

OK, you know what is causing the error.
Good for you.

It is not clear to me exactly what your question is for us.

SUBSTR(YOUR_VAR,1,23)
Re: ORA-06502 issue after 10g migration [message #434176 is a reply to message #434171] Mon, 07 December 2009 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no parameter setting (but maybe indirectly cursor_sharing one).
This is why you have to properly define your column datatype, variable datatype and correctly write SQL accordingly, all are part of the same code.

Regards
Michel
Re: ORA-06502 issue after 10g migration [message #434177 is a reply to message #434176] Mon, 07 December 2009 10:15 Go to previous messageGo to next message
haris
Messages: 3
Registered: June 2009
Junior Member
Thank you

The query is same for the 9i database and its not giving an error is there a parameter for precision that is set in 9i .
could you please throw some light on this ...
Re: ORA-06502 issue after 10g migration [message #434180 is a reply to message #434177] Mon, 07 December 2009 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Things changes with versions.
If your application are not clear and precisely defined the types then you rely on default/implicit behaviour which changes when Oracle wants (or as a side effect of code evolution or compilator).

Regards
Michel
Re: ORA-06502 issue after 10g migration [message #434185 is a reply to message #434177] Mon, 07 December 2009 10:32 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>The query is same for the 9i database and its not giving an error is there a parameter for precision that is set in 9i .
There is 1 possible "solution" applied in V10:
COMPATIBLE=9.2.0.7
but this essentially negates doing the upgrade.
The procedure needs to be changed to eliminate the error.
so Just Do It!
Re: ORA-06502 issue after 10g migration [message #434256 is a reply to message #434185] Tue, 08 December 2009 03:25 Go to previous message
haris
Messages: 3
Registered: June 2009
Junior Member
9i had the value rounded to 12 but 10g its still 40
so that was why I was asking about some parameter configuration
Thanks a lot to all of you for your reply
Previous Topic: Insert column value by select statement ..
Next Topic: SQL Query find out the bottom two date against each a_id
Goto Forum:
  


Current Time: Sat Dec 10 20:36:25 CST 2016

Total time taken to generate the page: 0.06614 seconds