Re: Form level procedures and functions

TIM -- Some help...

Q: I understand that pl-sql supports the data type BOOLEAN - can I

   pass a boolean data type back to an on-validate-field trigger ?

1. Sure. Presume you have a Form-level function defined as follows so that it returns boolean TRUE in the case the employee number passed in exists in the EMP table, and FALSE otherwise.

       FUNCTION Valid_Employee( fp_EmpNo NUMBER )
         RETURN BOOLEAN
       IS
         CURSOR c_Emp ( cp_EmpNo NUMBER ) 
             IS SELECT 'x'
                  FROM EMP
                 WHERE EMPNO = cp_EmpNo;
         lv_FoundOne BOOLEAN;
         lv_Tmp      VARCHAR2(1);
       BEGIN
         OPEN c_Emp(fp_EmpNo);
         FETCH c_Emp INTO lv_Tmp;
         lv_FoundOne := c_Emp%FOUND;
         CLOSE c_Emp;


         --

         -- Return the Boolean outcome of the 

         -- above FETCH.


         --

         RETURN lv_FoundOne;
       END;

   Then, you could have an ON-VALIDATE-FIELD trigger    (WHEN-VALIDATE-ITEM in Forms 4.0 parlance) which contains the code:

       IF NOT Valid_Employee(:YOUREMPBLOCK.EMPNO) THEN
         MESSAGE('Give your fail message here');
         RAISE Form_Trigger_Failure;
       END IF;

   Or, if you're looking for something more generic, you could write a    form-level procedure called Assert, for example, that says:

       PROCEDURE Assert( pp_expr BOOLEAN ) IS
       BEGIN
         IF NOT pp_expr THEN
           MESSAGE('Could have more customized message here.');
           RAISE Form_Trigger_Failure;
         END IF;
       END;

    In which case your Validation trigger becomes:

       Assert( Valid_Employee( :YOUREMPBLOCK.EMPNO ) );   

   You could get fancy and have each Valid_% routine set a global with    a custom error message that the 'Assert' procedure could print, but    this should get you started.

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _   Steve Muench Email: smuench_at_oracle.com   Forms Development
  Product Manager Received on Wed Oct 13 1993 - 03:18:34 CET