Home » Developer & Programmer » Reports & Discoverer » REP-1401 ORA-06502: PL/SQL: numeric or value error
REP-1401 ORA-06502: PL/SQL: numeric or value error [message #627615] Thu, 13 November 2014 02:45 Go to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi all,
FUNCTION Cf_10formula 
RETURN NUMBER 
IS 
BEGIN 
    SELECT Nvl(Round(SUM(amount)), 0) 
    INTO   :CP_5 
    FROM   hhxw_region_col_v 
    WHERE  category IN ( 'Intercompany - CATV' ) 
           AND Trunc(gl_date) BETWEEN To_date(:P_FROM_DATE, 'DD/MM/RRRR') AND 
                                      To_date( :P_TO_DATE, 'DD/MM/RRRR'); 

    RETURN :CP_5; 
END;

But here I was getting amount 40000. But I want the amount to be displayed 40,000 . So to achieve this i make some changes in my Formula column query , and for this am getting error.
MSG-00187: From Date 01-Sep-2014
REP-1401: 'cf_10formula': Fatal PL/SQL error occurred.
ORA-06502: PL/SQL: numeric or value error


1. I have manually increased the size of formula column to 50. Initially it was 10. datatype is number.
2. The max length of amount is 13. I have taken this from the base table.
Placeholder is having 30 width & datatype is number.

So based on this data, please let me know whether i am on right track or not ?

Please help me on this

Thanks

Report message to a moderator

Re: REP-1401 ORA-06502: PL/SQL: numeric or value error [message #627617 is a reply to message #627615] Thu, 13 November 2014 03:06 Go to previous messageGo to next message
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you've got a number function returning into a number field then all you should need to do to make the comma appear is to change the number format mask in field.

However, Why are you selecting the value into a report item? Select into a local variable and return that.

And you should avoid applying functions to DB columns in where clauses wherever possible, so oracle can use indexes on them if applicable:
AND gl_date >= To_date(:P_FROM_DATE, 'DD/MM/RRRR') 
AND gl_date < To_date( :P_TO_DATE, 'DD/MM/RRRR') + 1;

Report message to a moderator

Re: REP-1401 ORA-06502: PL/SQL: numeric or value error [message #627618 is a reply to message #627617] Thu, 13 November 2014 03:19 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
First of all Thanks a lot cookiemonster.. Smile
Quote:
However, Why are you selecting the value into a report item? Select into a local variable and return that.

I need to display 2 column's one is CF_10 and another one is CP_5
Quote:
you should avoid applying functions to DB columns in where clauses wherever possible, so oracle can use indexes on them if applicable:

Can you please explain more on this? I am not getting your point.
And i have another doubt suppose local variable is v_amount and assigned into the Place Holder column like below 
:CP_5 := nvl(trunc(v_amount),0);

So can i use in the return like below
return (nvl(:CP_5,0));

Report message to a moderator

Re: REP-1401 ORA-06502: PL/SQL: numeric or value error [message #627619 is a reply to message #627618] Thu, 13 November 2014 04:03 Go to previous messageGo to next message
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
Senior Member
mist598 wrote on Thu, 13 November 2014 09:19

Quote:
you should avoid applying functions to DB columns in where clauses wherever possible, so oracle can use indexes on them if applicable:

Can you please explain more on this? I am not getting your point.

If there's an index on gl_Date then oracle will not use it if you apply a function (like trunk) to gl_date in the where clause.

mist598 wrote on Thu, 13 November 2014 09:19

And i have another doubt suppose local variable is v_amount and assigned into the Place Holder column like below 
:CP_5 := nvl(trunc(v_amount),0);

So can i use in the return like below
return (nvl(:CP_5,0));

Probably, give it a try.

Report message to a moderator

Re: REP-1401 ORA-06502: PL/SQL: numeric or value error [message #627620 is a reply to message #627619] Thu, 13 November 2014 04:14 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
Probably, give it a try.

Thanks cookiemonster and i tried & got the result(in the TOAD with PL/SQL Coding)

[Updated on: Thu, 13 November 2014 04:16]

Report message to a moderator

Re: REP-1401 ORA-06502: PL/SQL: numeric or value error [message #627645 is a reply to message #627620] Thu, 13 November 2014 10:40 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As CP_5 is already assigned with the NVL function, there's no point in returning NVL(CP_5, 0) as it can't be NULL.

Report message to a moderator

Re: REP-1401 ORA-06502: PL/SQL: numeric or value error [message #627679 is a reply to message #627645] Fri, 14 November 2014 04:30 Go to previous message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
As CP_5 is already assigned with the NVL function, there's no point in returning NVL(CP_5, 0) as it can't be NULL.

Yes You are right Littlefoot and Thanks.. Smile

Report message to a moderator

Previous Topic: Print ID Card from Report6i
Next Topic: Repeat a record in the details
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 23 06:17:54 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.