This discussion is archived
5 Replies Latest reply: Nov 16, 2008 8:25 AM by ATD RSS

Show Custom Error Message in Apex

ZKay Journeyer
Currently Being Moderated
Hi,

I have created a Master-Detail form on Dept and Emp tables. In Dept table, I have deptsal column which is updated by a trigger on EMP table.
DEPT table has following trigger:

create or replace trigger "TRG_DEP_SAL"
BEFORE UPDATE on "DEPT"
FOR EACH ROW
BEGIN
IF :NEW.deptsal > 100 THEN
RAISE_APPLICATION_ERROR(-20001,'Dept Salary too high!');
END IF;
END;

So, when I enter salary on Apex Form which increases the limit of deptsal e.g. 100, it gives the following error message:


Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-20001: Dept Salary too high! ORA-06512: at "ZAHIDKHANUK.TRG_DEP_SAL", line 4 ORA-04088: error during execution of trigger 'ZAHIDKHANUK.TRG_DEP_SAL' ORA-06512: at "ZAHIDKHANUK.BIUD_EMP", line 11 ORA-04088: error during execution of trigger 'ZAHIDKHANUK.BIUD_EMP', update "ZAHIDKHANUK"."EMP" set "EMPNO" = :b1, "DEPTNO" = :b2, "ENAME" = :b3, "JOB" = :b4, "MGR" = :b5, "HIREDATE" = :b6, "SAL" = :b7, "COMM" = :b8, "ID" = :b9 where "EMPNO" = :p_pk_col
Error Unable to process update.
OK


How can I display my own message?

Thanks,
Zahid
  • 1. Re: Show Custom Error Message in Apex
    ATD Guru
    Currently Being Moderated
    Hi Zahid,

    You may find it easier to perform a page validation before allowing the submit to take place. That way, you can use the error messaging system within Apex to display the error.

    Andy
  • 2. Re: Show Custom Error Message in Apex
    ZKay Journeyer
    Currently Being Moderated
    Hi Andy,

    Thanks for your reply.
    The detail part of the Form has multiple rows. Can I use sum of the sal column for all the rows being displayed to do the validation? Means if I query dept 10 and all its employees and change salary for some of the employees, it should validate on submit button that total dept sal should be below 100.

    Regards,
    Zahid
  • 3. Re: Show Custom Error Message in Apex
    ATD Guru
    Currently Being Moderated
    Hi,

    Yes, you can do that. You would need to create a page validation that loops through the records in the tabular form, checks the values entered and then returns an error message.

    Create a new validation. Set it to a Page level validation, select PL/SQL and then Function returning error text, then give it a name. The PL/SQL will be something like:
    BEGIN
     FOR i IN 1..APEX_APPLICATION.G_F01.COUNT
     LOOP
      IF TO_NUMBER(APEX_APPLICATION.G_F04(i)) > 100 THEN
       RETURN 'Item ' || TO_CHAR(i) || ' - DEPTSAL must not be greater than 100';
      END IF;
     END LOOP;
     RETURN NULL;
    END;
    This will loop through the records in column 4 and check the amount. If it is greater than 100, an error message is generated displaying the row with the error. This will stop the process on the first error. If there are no errors NULL is returned which indicates that the validation test has passed.

    You should replace G_F04 with the correct column for your page - do a View Source on the loaded page and check for the "name" attribute for the column. G_F04 is for "f04", G_F05 is for "f05" etc.

    Andy
  • 4. Re: Show Custom Error Message in Apex
    ZKay Journeyer
    Currently Being Moderated
    Thanks Andy, it works.

    Cheers,
    Zahid
  • 5. Re: Show Custom Error Message in Apex
    ATD Guru
    Currently Being Moderated
    You're welcome, Zahid

    Andy

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points