Home » Developer & Programmer » Forms » trouble in calling a function inside trigger
trouble in calling a function inside trigger [message #265009] Wed, 05 September 2007 04:23 Go to next message
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(Cool := :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

Re: trouble in calling a function inside trigger [message #265051 is a reply to message #265009] Wed, 05 September 2007 05:49 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, read the OraFAQ Forum Guide before you post your next message. Learn how to properly format it in order to make it readable.

You've created a FUNCTION and it's name is 'checkid'.

In WHEN-VALIDATE-ITEM trigger you are calling a PROCEDURE, not a FUNCTION:
if length(studyno) = '8' then  --> why '8'? it is not a string!
   checkid(studyno);           --> this is how a procedure is called
...

If you want to call a function, you'll have to return its value into a variable, or - as you've put it to return a BOOLEAN - use it directly in an IF-THEN-ELSE, such as:
IF length(studyno) = 8        -- note this! Result of the LENGTH function is a NUMBER, not string
THEN
   IF checkid(studyno) 
   THEN
      do_something;
   END IF;
...
Re: trouble in calling a function inside trigger [message #265075 is a reply to message #265051] Wed, 05 September 2007 06:23 Go to previous message
sauk
Messages: 29
Registered: June 2007
Junior Member
dear littlefoot,

Thanks a lot.. its working now!! sorry for the wrong way of formatting!!

Sera

[Updated on: Wed, 05 September 2007 06:23]

Report message to a moderator

Previous Topic: initialize set_block_property
Next Topic: SHOW WINDOW
Goto Forum:
  


Current Time: Fri Dec 09 09:52:06 CST 2016

Total time taken to generate the page: 0.26559 seconds