OraFAQ Forum: Reports & Discoverer » 'afterpform': Fatal PL/SQL error occurred, PL/SQL: numeric or value error

Home » Developer & Programmer » Reports & Discoverer » 'afterpform': Fatal PL/SQL error occurred, PL/SQL: numeric or value error (RDBMS 11.2.0.2.0, Oracle Applications : 12.1.3,Report builder 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

'afterpform': Fatal PL/SQL error occurred, PL/SQL: numeric or value error [message #542691]

Thu, 09 February 2012 13:57

orauser123
Messages: 13
Registered: November 2010
Location: New York

Junior Member

Hi All,
I am facing a problem in running custom report.

---------------------------------------------------------------
=> DB Server Info
RDBMS : 11.2.0.2.0
Oracle Applications : 12.1.3

-------------------------------------------------------------
I am getting following error.
REP-1401: 'afterpform': Fatal PL/SQL error occurred.
ORA-06502: PL/SQL: numeric or value error

custom report two parameters one is date parameter and name parameter

date parameter is using as fnd_date.canonical_to_date(:P_EFFECTIVE_DATE) in queries.

if I give name parameter for individual, then it works fine.

if I give name parameter as "All", then its erroring. I tried it in my best way, but its not working.

variable data length increased though its not working, can any one help me on this please....

Here is the Afterpform code
----------------------------------------------------------------
function AfterPForm return boolean is
lName VARCHAR2(5000); -- its changed from lName varchar2(50);
begin
IF :P_NAME 'All' THEN
lName:= 'AND name =:P_NAME';
ELSE
lName:='AND name in (select name from <******>
order by name
)';
END IF;

:P_Where_Clause := lName;
return (TRUE);
end;
--------------------------------------------------------------

Thanks to all in advance.

[Updated on: Thu, 09 February 2012 14:22]

Report message to a moderator

Re: 'afterpform': Fatal PL/SQL error occurred, PL/SQL: numeric or value error [message #542700 is a reply to message #542691]

Thu, 09 February 2012 14:45

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Well, I doubt that you posted real query (regardless <****> stuff) because IF misses the equal sign. It should be

IF :P_NAME = 'All' THEN

so it would fail before the error message you mentioned.

Apart from that, how is :P_NAME declared; what is its datatype and size?

Why did you mention "fnd_date.canonical_to_date(:P_EFFECTIVE_DATE)" at all? It isn't used in a trigger.

Perhaps you could include SRW.MESSAGE calls into the trigger code so that you could follow its execution and narrow the error cause.

If you post some code in your next messages, please, format it and enclose into [code] tags to preserve formatting. If you don't know how to do it, take a few seconds look here: How to use [code] tags and make your code easier to read?.

Report message to a moderator

Re: 'afterpform': Fatal PL/SQL error occurred, PL/SQL: numeric or value error [message #542709 is a reply to message #542700]

Thu, 09 February 2012 18:55

orauser123
Messages: 13
Registered: November 2010
Location: New York

Junior Member

Thank you for prompt reply,
:P_NAME is userparameter in custom report, that takes "All" and "individual name" to get data.
p_where_clause is lexical which gets substituted in queries.
[code]
function AfterPForm return boolean is
lName VARCHAR2(5000); -- its changed from lName varchar2(50);
begin
IF :P_NAME = 'All' THEN
lName:='AND name in (select name from <table>
order by name
)';
ELSE
lName:= 'AND name =:P_NAME';
END IF;
:P_Where_Clause := lName;
return (TRUE);
end;
[code]

Why did you mention "fnd_date.canonical_to_date(:P_EFFECTIVE_DATE)" at all? It isn't used in a trigger.
-- thought this could be an error. yes its not used in afterpform trigger.

there is a formula column too that refers to parameter name.

please let me know debugging techniques to solve this issue.
always thanks to all and giving support here to the persons like me.

[Updated on: Thu, 09 February 2012 19:08]

Report message to a moderator

Re: 'afterpform': Fatal PL/SQL error occurred, PL/SQL: numeric or value error [message #542732 is a reply to message #542709]

Fri, 10 February 2012 00:10

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

How about re-reading How to use [code] tags and make your code easier to read? and really following what's said in there. Do you see your code formatted? I don't.

I asked what is P_NAME's datatype and size - you didn't answer that either.

Debugging techniques? I suggested SRW.MESSAGE - you didn't use it.

So what do you expect from us, when you don't provide requested information and do nothing in solving the problem yourself?

Report message to a moderator