trouble in calling a function inside trigger [message #265009] |
Wed, 05 September 2007 04:23  |
sauk
Messages: 29 Registered: June 2007
|
Junior Member |
|
|
I have a form with a text item studyno(varchar2).which accepts an input and check whether it is valid.In when_validate_item trigger i am call ing a function named checkid,which is in the program units.
the function body for checkid is
FUNCTION checkid (id_to_check char := NULL)RETURN boolean IS
lencode number;
chk char(1);
code char(7);
prime integer;
BEGIN
lencode:=8;
chk := upper(ltrim(rtrim(substr(id_to_check,length(id_to_check)))));
code := ltrim(rtrim(substr(id_to_check,1,lencode - 1)));
prime:=length(chkstring);
-- fail if null or wrong length
if id_to_check is NULL or length(id_to_check) != lencode then
return FALSE;
end if;
-- fail if insufficient digits or alpha chars in number
if length(code) < lencode - 1 or not alldigit(code) then
return FALSE;
end if;
-- Oracle will raise an exception if we try to convert
-- a number with an alpha character in it. At this point
-- though, we know we only have digits so conversion
-- should not fail
--
-- fail if number evaluates to zero
if to_number(code) = 0 then
return FALSE;
end if;
-- part of this next check is not required. how can chk be null
-- if it has been taken as a substr from the original?
if instr(chkstring,chk) = 0 or chk is NULL then
return FALSE;
end if;
if chk != chkdgt(code, prime) then
return FALSE;
end if;
return TRUE;
END;
function body of chkstring
FUNCTION chkstring RETURN char IS
BEGIN
return 'ABCDEFGHJKLMNPQRSTVWXYZ';
END;
Function body of chkdgt
FUNCTION chkdgt(code in char,prime in integer) RETURN char IS
retchar char;
BEGIN
retchar:=substr(chkstring,1+mod(to_number(code)-1,prime),1);
return retchar;
END;
and function body of alldgt is
FUNCTION alldigit(str char) RETURN boolean IS
checker number;
BEGIN
checker:=to_number(str);
return TRUE;
exception
when VALUE_ERROR then
return FALSE;
END;
In when validate item
declare
studyno char( := :block.studyno;
invalid_Data Exception;
begin
if length(studyno) = '8' then
checkid(studyno);
return;
else
raise Invalid_Data;
end if;
Exception
when Invalid_Data then
Message('Invalid Data: Please check the syudyno');
Raise Form_trigger_Failure;
end;
this is giving me an error
checkid is not a procedure or is undefined..
could anyone suggest me how to solve this...
thanks in advance
sera
[Updated on: Wed, 05 September 2007 04:43] Report message to a moderator
|
|
|
|
|