APEX

Hello Apex Gurus !

SCENARIO:
* I have an interactive grid with a PERCENTAGE colum. The SUM of the percentage should be always 100.
* I cannot use FRONT END, because it would allow corrupt data to enter into the database. (by filtering records, or any other way). It has to be in the database side.

MY-SOLUTION:
* I created a separate table that stores the PK and the SUM of the children percentages.
* This table has a deferrable constraint, so it will complain ONLY at the end of the transacion. (Not on each row)
Example:
alter table MYTABLE_SUM
add constraint chk_total_100
check (sum_percent = 100)
DEFERRABLE INITIALLY DEFERRED;

* I created a ON STATEMENT TRIGGER to delete this entire PK and populate with the current SUM.
* It works perfectly! But Apex doesn't capture the error message.
This is my trigger:

create or replace TRIGGER MYTABLE_COMPOUND
FOR INSERT OR UPDATE OR DELETE OF STP_PERCENT ON MYTABLE
COMPOUND TRIGGER
  v_accn_no MYTABLE.accn_no%type; 
  v_frozen_fy MYTABLE.frozen_fy%type; 
  v_frz_fy_type MYTABLE.frz_fy_type%type;

------------------
AFTER EACH ROW IS
------------------
BEGIN
  --saves the PK in variable
  v_accn_no     := NVL(:NEW.accn_no , :OLD.accn_no); 
  v_frozen_fy   := NVL(:NEW.frozen_fy , :OLD.frozen_fy); 
  v_frz_fy_type := NVL(:NEW.frz_fy_type, :OLD.frz_fy_type); 
END AFTER EACH ROW; 


------------------
AFTER STATEMENT IS
------------------

e_check_constraint_violated EXCEPTION;
PRAGMA EXCEPTION_INIT(e_check_constraint_violated, -2290); -- Associate with ORA-02290
BEGIN

    --Delete the temporary data for this PK.
    delete from MYTABLE_SUM
    WHERE accn_no = v_accn_no 
    and frozen_fy = v_frozen_fy 
    and frz_fy_type = v_frz_fy_type;

    --Try to insert the sum in a table that has a CONSTRAINT... 
    --If the sum is not 100, it will give an error.

  insert into MYTABLE_SUM
  ( accn_no
  , frozen_fy
  , frz_fy_type
  , sum_percent) 
  (
  select 
    accn_no
  , frozen_fy
  , frz_fy_type
  , sum(stp_percent) sum_percent
  from MYTABLE
  where accn_no     = v_accn_no 
    and frozen_fy   = v_frozen_fy 
    and frz_fy_type = v_frz_fy_type
  group by 
    accn_no
  , frozen_fy
  , frz_fy_type);

exception WHEN e_check_constraint_violated THEN

  apex_error.add_error(
    p_message => 'The sum of all percentage records must be exactly 100.' 
  , p_display_location => apex_error.c_inline_in_notification
  ); 

END AFTER STATEMENT;

END;
/

PROBLEM:
* I cannot show the correct error in apex, telling the user that the SUM should be 100.
* I always receive this error in APEX:
Error Message
1 error has occurred
* Error processing request."

* If I try to use RAISE APPLICATION ERROR instead of APEX_ERROR, it's the same.
* If I go to the DEBUG, I can see the error:

See: “ORA-02290: check constraint (USDA.CHK_TOTAL_100) violated ”

Error: Error processing request. 
- Additional info: Contact your application administrator. 
- Display location: ON_ERROR_PAGE 
- Association type: 
- Item name: 
- Region id: 
- Column alias: 
- Row: 
- Model instance id: 
- Model record id: 
- Internal error: true 
- Common runtime error: false 
- APEX error code: APEX.UNHANDLED_ERROR 
- SQL code: -2091 
- SQL error: ORA-02091: transaction rolled back 
ORA-02290: check constraint (MYOWNER.CHK_TOTAL_100) violated 
ORA-06512: at "APEX_240200.WWV_FLOW_SECURITY", line 2217 
ORA-20876: Stop APEX Engine

I think the problem is because the error is being generated in the STATEMENT…Maybe apex cannot handle it properly.
Do you guys have any idea on how to “capture” that error from the trigger?

Thanks in advance!
Thomas Glufke