REP - 1401 error, in after parameter trigger [message #337709] |
Thu, 31 July 2008 16:35 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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?
|
|
|
|
|
error still remains [message #338379 is a reply to message #337709] |
Mon, 04 August 2008 15:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 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?
|
|
|
|