Home » Developer & Programmer » Reports & Discoverer » REP - 1401 error, in after parameter trigger
REP - 1401 error, in after parameter trigger [message #337709] Thu, 31 July 2008 16:35 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
i am using a lexical parameter, 2 bind parameters...my
after parameter report trigger validates the entry of
two order numbers (bind variables on1 and on2)

function AfterPForm return boolean is
begin

  if :on1 is not null and :on2 is not null then
	:lp_order := 'and oh.order_number between :on1 and :on2';
if (:on1 = :on2 ) then
     :lp_order := 'and oh.order_number = :on1 ';
end if;
elsif :on1 is null and :on2 is not null then
	:lp_order := 'and oh.order_number <= :on2';

elsif :on1 is not null and :on2 is null then
	:lp_order := 'and oh.order_number >= :on1';

else    :lp_order := null;
end if;

  return (TRUE);
end;


when i am running the report, i am getting this error,
REP - 1401 afterpform parameter error occured
ORA - 06502 PLSQL Numeric or value error

there is one more bind parameter, orgid, but i am not
using it in this trigger, i am entering value at runtime

any idea how to solve it?
Re: REP - 1401 error, in after parameter trigger [message #337734 is a reply to message #337709] Thu, 31 July 2008 23:59 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is LP_ORDER's declaration? Its length, in particular? Is it wide enough to let the longest string in?
icon6.gif  Re: REP - 1401 error, in after parameter trigger [message #337750 is a reply to message #337709] Fri, 01 August 2008 00:47 Go to previous messageGo to next message
rohan171982
Messages: 1
Registered: July 2007
Location: MUMBAI
Junior Member

hi
first try to solve the problem with the help of exeption in
(when value_error).
catch the required error in exception and pass any value means null or 0 or else and then find where the error occurs.....
error still remains [message #338379 is a reply to message #337709] Mon, 04 August 2008 15:41 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
this error still remains

i have changed the lp_order lexical parameter datatype
from character to number, width 80
as order number column is number datatype..modified the code to

function AfterPForm return boolean is
begin

  if :on1 is not null and :on2 is not null then
	:lp_order := 'and oh.order_number between :on1 and :on2';
		ELSIF (:on1 = :on2 ) then
     :lp_order := 'and oh.order_number = :on1 ';

elsif :on1 is null and :on2 is not null then
	:lp_order := 'and oh.order_number <= :on2';

elsif :on1 is not null and :on2 is null then
	:lp_order := 'and oh.order_number >= :on1';

else    
	:lp_order := NULL;
end if;

  return (TRUE);
  
  exception
  	when value_error then
  	dbms_output.put_line('Error'||SQLERRM||' '||SQLCODE);
  	
end;




now it says rep - 1401 and also ORA-06503: PL/SQL: Function returned without value

which is shocking..i am returning true

Re: error still remains [message #338425 is a reply to message #338379] Tue, 05 August 2008 00:55 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
i have changed the lp_order lexical parameter datatype
from character to number

This is plain wrong and nonsense. LP_ORDER looks like this:
:lp_order := 'and oh.order_number = :on1 ';
"and" is a character string, "oh.order_number" is a character string, "= :on1 " is a character string. What made you do this change?!? It should remain a CHARACTER parameter, wide enough. 80? Why not 500, as it seems to be reasonably wide to hold such a contents?

Furthermore, you might try with this kind of syntax: instead of
:lp_order := 'and oh.order_number = :on1 ';
try with
:lp_order := 'and oh.order_number = ' || :on1;


Quote:
"Function returned without value" which is shocking..i am returning true

It may be shocking for you, but - Oracle doesn't tell lies so - think twice.
Your code produces the VALUE_ERROR error as you are trying to store character string into a NUMBER variable. Then the code switches over to exception handler section which is NOT handling anything; you are just displaying a message which Oracle does by itself, by default, and does not need your intervention of such a kind. Finally, function ends WITHOUT returning a value.

To illustrate it: this is your code, and it returns nothing:
exception
  	when value_error then
  	dbms_output.put_line('Error'||SQLERRM||' '||SQLCODE);
while this code returns "something":
exception
  	when value_error then
  	dbms_output.put_line('Error'||SQLERRM||' '||SQLCODE)
        return (false);

See the difference?
Re: REP - 1401 error, in after parameter trigger [message #338706 is a reply to message #337709] Tue, 05 August 2008 14:08 Go to previous message
ora1980
Messages: 251
Registered: May 2008
Senior Member
thanks a lot

Previous Topic: Sum and outer join
Next Topic: Deleted User -- Deleted Reports??!
Goto Forum:
  


Current Time: Tue Dec 06 16:15:48 CST 2016

Total time taken to generate the page: 0.05445 seconds