Friday, 1 June 2012

Error Handling API

Originally heard about this from Patrick's blog months back. See http://www.inside-oracle-apex.com/apex-4-1-error-handling-improvements-part-1/ and http://www.inside-oracle-apex.com/apex-4-1-error-handling-improvements-part-2/. Then a couple of weeks back I was looking at the new features on the Apex builder guide, where they mention about the enhanced error handling. See http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21674/what_new.htm#CEGIEDHE. So I thought I may as well set up a basic example to see how it is.

The only sections in the documentation that really talk about it are:


Which is basically about setting the error handling (PL/SQL) function. The main source of information is on the API docs for apex_error. This page talks about data types, and available functions/procedures you can use with this package, which will allow customising the displayed error message.

Whilst the API docs do provide a good example, I just wanted to have a play on my own to see it in action. First set up a table with a unique constraint:


CREATE TABLE  "UNIQUE_PEOPLE" 
   ( "ID" NUMBER, 
 "NAME" VARCHAR2(200), 
  CONSTRAINT "UNIQUE_PEOPLE_PK" PRIMARY KEY ("ID") ENABLE, 
  CONSTRAINT "UNIQUE_PEOPLE_UK1" UNIQUE ("NAME") ENABLE
   )
/

CREATE SEQUENCE UNIQUE_PEOPLE_SEQ START WITH 1 INCREMENT BY 1;
/

CREATE OR REPLACE TRIGGER  "BI_UNIQUE_PEOPLE" 
  before insert on "UNIQUE_PEOPLE"               
  for each row  
begin   
  if :NEW."ID" is null then 
    select "UNIQUE_PEOPLE_SEQ".nextval into :NEW."ID" from dual; 
  end if; 
end; 

/
ALTER TRIGGER  "BI_UNIQUE_PEOPLE" ENABLE
/

Then a function to check this validation:


create or replace function unique_person_check(
    p_error in apex_error.t_error )
    return apex_error.t_error_result
is
    l_result          apex_error.t_error_result;
    l_reference_id    number;
    l_constraint_name varchar2(255);
begin
    l_result := apex_error.init_error_result (
                    p_error => p_error );
 
    IF p_error.ora_sqlcode = -1 THEN --unique constraint
    
        if apex_error.extract_constraint_name(p_error) = 'UNIQUE_PEOPLE_UK1' THEN
        
            l_result.message := 'Person already exists. Please choose another';

        END IF;
    
    END IF;
    
    --fallback incase our testcase doesn't match anything
    
    if p_error.ora_sqlcode is not null and l_result.message = p_error.message then --no new message yet assigned. Must mean we haven't met the conditions above
    l_result.message := apex_error.get_first_ora_error_text(
            p_error => p_error);
    
    end if;
 
    return l_result;
end unique_person_check;​
/

Then set up a page with a tabular form for easy testing. Edit the page attributes and specify the error function as: unique_person_check. If this function was more of a global set up, you would just set this up in the application definition.

I set up a demo: http://apex.oracle.com/pls/apex/f?p=45448:19 - try and specify a name that is already in the list, and you should receive the error specified above. The example on the docs also does some logging for internal errors that aren't access denied errors, but I couldn't think of any use cases to test that, so left that alone for now. :-).


4 comments:

  1. Great example. I struggled for quite a while to use a duplicate function to verify a combination of job type and department for students workers and didn't work for my tabular form.
    Then I set up unique constraint but the message when the constraint is violated is ugly. Thanks so much for this nice post!

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. How did you manage to display this error inline? Whenever I have errors coming from Oracle (ORA-nnnnnn) the customized "APEX_ERROR" messages are displayed only on error popup window. Even setting display location to apex_error.c_inline_in_notification does not help, neither does setting error to be displayed inline on page properties.

    ReplyDelete