5 Replies Latest reply: Nov 16, 2008 10:25 AM by ATD RSS

    Show Custom Error Message in Apex

    ZKay
      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
          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
            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
              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
                Thanks Andy, it works.

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

                  Andy