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)
icon6.gif  'afterpform': Fatal PL/SQL error occurred, PL/SQL: numeric or value error [message #542691] Thu, 09 February 2012 13:57 Go to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 18841
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?.
icon3.gif  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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 18841
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?
Re: 'afterpform': Fatal PL/SQL error occurred, PL/SQL: numeric or value error [message #542847 is a reply to message #542732] Fri, 10 February 2012 07:56 Go to previous messageGo to next message
dllinf
Messages: 3
Registered: June 2011
Junior Member
checks the size and type of the variable :P_Where_Clause
Re: 'afterpform': Fatal PL/SQL error occurred, PL/SQL: numeric or value error [message #542872 is a reply to message #542847] Fri, 10 February 2012 11:02 Go to previous message
orauser123
Messages: 13
Registered: November 2010
Location: New York
Junior Member
got it solved, size was the problem for p_name and p_where clause.
Thank you very much for helping.
Previous Topic: REP-1825: Before Report trigger returned FALSE.
Next Topic: Is there a way to automate Oracle Reports?
Goto Forum:
  


Current Time: Wed Apr 23 10:47:00 CDT 2014

Total time taken to generate the page: 0.11584 seconds