| REP - 1401 error, in after parameter trigger [message #337709] |
Thu, 31 July 2008 16:35  |
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  |
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 |
 |
Littlefoot
Messages: 21809
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 ';
Furthermore, you might try with this kind of syntax: instead of:lp_order := 'and oh.order_number = :on1 '; :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);exception
when value_error then
dbms_output.put_line('Error'||SQLERRM||' '||SQLCODE)
return (false);
See the difference?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
