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:
Then a function to check this validation:
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. :-).
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. :-).