Skip to main content

Transform ORA-xxxx messages into something more user friendly in APEX

When you delete a record that is referenced using a foreign key constraint you get a rather ugly error like this:
Of course you can prevent such a thing from happening by creating a Page process that checks if your delete is "Ok". But that's double coding...and dangerous when you have to maintain your database structure: You'll probably forget to update the processes as well. Wouldn't it be nice if you can 'catch' that error an present it to the end user in a more friendly way (and with a somewhat more understandable text)? I guess your users will be more enthusiastic about your application (and you) when you show them something like this:
So how do we achieve that?

1. Edit the "Error Page Template Control" region of your Application Default Page Template, because that's the one that is used for presenting errors. Set it to :

<script type="text/javascript">$(document).ready(function(){raiseErrorHandler();});</script>
2. Create the raiseErrorHandler function (I'll use some jQuery UI stuff to show the nice dialog box):

function raiseErrorHandler(){
vError = $(".ErrorPageMessage");
vError.hide();
var get = new htmldb_Get( null, $v('pFlowId')
,'APPLICATION_PROCESS=GetErrorMessage'
,$v('pFlowStepId'));
get.addParam( 'x01', vError.html());
gReturn = get.get();
get = null;
var errArray = gReturn.split("#",2);
showError( vError, errArray[0], errArray[1]);
}

function showError(pThis, pTitle, pText){
vText = '
'+'
'+pText+'
'
$(pThis).append(vText);
$("#alert").dialog({
bgiframe: true,
modal: true,
minHeight : 200,
width : 600,
closeOnEscape : false,
close : function(){window.history.go(-1)},
buttons: {
Ok: function() {
$(this).dialog('close');
$("#alert").remove();
window.history.go(-1);
}}
});
}
3. That Javascript function calls an Application Process GetErrorMessage:
DECLARE
p_error_title varchar2(32767);
p_error_text varchar2(32767);
p_ora_error varchar2(32767);
BEGIN
p_ora_error := wwv_flow.g_x01;
-- Get "translated" error message
get_error_message( p_ora_error, p_error_title, p_error_text );
htp.p( p_error_title||'#'|| p_error_text );
END;
4. The procedure get_error_message is defined in the database. This procedure queries a table that contains the contraint name (the 'ORA Error') and a more user friendly description.

create or replace PROCEDURE GET_ERROR_MESSAGE
( p_ora_error IN VARCHAR2
, p_error_title OUT VARCHAR2
, p_error_text OUT VARCHAR2
) AS
CURSOR c_mps IS
SELECT code, description
FROM afk_message_properties
WHERE INSTR( p_ora_error, constraint_name ) > 0;
BEGIN
OPEN c_mps;
FETCH c_mps INTO p_error_title, p_error_text;
CLOSE c_mps;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_error_title := 'No translation found for this error';
p_error_text := p_ora_error;
END GET_ERROR_MESSAGE;
The Application Process spits out a concatenated text, that will be segregated again in the Javascript function. The result is presented to the user in a way that would make him happy....
Even if there is no error definition found in the table, the error will be shown in the dialog box - only the translation to a normal language is missing.

Kinda cool I think!
You can see a public demo when you click here.

Comments

Anonymous said…
This seems like a lot of effort. Why not have a package with error codes/messages, handle the exception and use raise_application_error in your code?

exception
when others then
log_error;
raise_application_error(error_pkg.some_others_error, error_pkg.some_others_error_msg);
Mark Lancaster said…
The point Anonymous is raising is OK if you have explicitly defined an exception block.

However, what we are really talking about is the untrapped exceptions.

Roels example provides a generic mechanism to log these untrapped errors, and over time review and build more user friendly messages, or better still fix the problem.

His example is a jQuery variation of mine from a few months ago.
You also have the opportunity of capturing the session state values also, as you can see in my post on Apex friendly exceptions.
Roel said…
You're absolutely right Mark. You could catch all the (Foreign, Check, Unique) database constraints in APEX before submit, but that takes lots of coding - and a maintenance risk. User defined errors (raised using triggers) could be transformed to something more meaningful to the end user. But where talking about (mostly) constraint validations.
And I missed your post, so thanks for bringing it up again! Also a very nice solution.
Seems everyone runs into the same problem sometime...
Tobias Arnhold said…
Hi Roel,

really nice solution for the error handling. When I get some time I will try your example.

Tobias
Anonymous said…
Roel

I have implemented your idea, that work great!! But can you tell me the contents of the afk_error_messages table. I will be greatfull if you give an example of the content in the table.

Thanks
Roel said…
@Anonymous:
The contents of the Messages Table is something like this:
Code : FAC-0001
Description (the 'translated' error): You really shouldn't try to delete this fine Department. There are people working there! They're doing a great job and make lots of money for the company. So closing this office is not a good idea.
Constraint_Name (an unique part of the original error message) : ROEL.SYS_C002315334 (or something like "AFK_ROLES_APPLICATIONS_FK" )

HTH
Roel
Pavan said…
Hi Roel

I have tried to work out your idea but could not get it to work in my workspace. I have created all the javascripts,process and uploaded required jQuery files.I am worried about the procedure.Can you please add the procedure and make it work in my application. My credentials are
wkp: OITORACLE
UNAME: PAVAN9138@YAHOO.CO.IN
PWD: $uguna123
app : 9237(company) page:4

Thank you very much
Pavan
Roel said…
Pavan,

I fixed it in your app. In my example I didn't use an Updateable Report (like you did). And that doesn't seem to use the Custom Error Template...but does use the ErrorPageMessage DIV. So I added this function to the Page Header:
$(function(){
vError = $(".ErrorPageMessage");
if (vError.html())
{ raiseErrorHandler();
}
});
So if the ErrorPageMessage contains something the 'translator' is called.

And you missed some stuff
- I uploaded a jquery-ui...css file
- created a table AFK_MESSAGES
- created the procedure GET_ERROR_MESSAGE
- removed the last 's' from the Application Process GetErrorMessage(s).

But now it seems to work (I added a message to test it).

Have fun
Roel
vnz said…
Hi Roel,

really cool stuff.

a small error however: you should modify your GET_ERROR_MESSAGE procedure since the cursor fetch will not raise a NO_DATA_FOUND if there is no data. Either check c_mps%notfound or use the SELECT INTO synthax.

Cheers,
Anonymous said…
Hi Roel, first of all, thank you for the great tutorial!

I have problems making it work correctly, my modal window is not showing at all, and my browser is reporting "Object doesn't support this property or method." when it tries to call method $("#alert").dialog() .

Do you know what might be the problem?

I tested the jQuery loading, and it is loaded correctly.


Relja
Roel said…
Relja,
You said jQuery is loaded, but are you sure jQuery UI is also loaded? Maybe you can set up an example on apex.oracle.com, so I can help you out...

Roel
OracleDev said…
I am sorry I am new to jquery and utilizing it within apex.

I am on the Error Page Template Control page of my template and does this code look correct? I tried to mirror your process but not sure if I placed the function in the correct place.

Does the function go in the same error page template control box below the .ready(function(){raiseErrorHandler?
OracleDev said…
Sorry the comment system would not let me post the code but essentially what I did was put all of your code (script and function) from the first two code boxes directly into the Error Page Template Control box.

is this correct?
Roel said…
@oracleDev : only the code at point 1. goes in the error template.The code at point 2. (!the function) should be in your page header (on a particular page, page 0 or in the page template).
OracleDev said…
Thank you so much. Just one other question lets say for instance I place the (function) into the header of Page 1. Would I enclose the function with script HTML tags?
Roel said…
Yes, the javascript function needs to be embedded in script tags : <script> etc
OracleDev said…
Finally, got it to work was just an issue with an extra space in the code when I pasted it into Apex.

I am getting the window popup and currently my table is empty for the translated error. Just have to populate that table. Even though the table in my database is empty I though that the script would pass the untranslated error, however I am not seeing that right now.
OracleDev said…
Please disregard my previous messages just got it working perfectly! Cannot thank you enough.
Roel said…
Glad you've got it working...in the end!
Unknown said…
Hello Roel,

It's been long time since the post. But I hope you still can give me some help.

I have tried to deployed your solution. Instead of getting the defined error message from database, I want to display the hardcoded error message from page processes (I think it should work, the only difference is the source of the error message). What I did were:

1) Edit Error Page Template via Shared Components -> Templates -> One Level Tabs.
2) Edit page template and add the javascript functions in HTML Header.
3) Add page process GetErrorMessage.

There's another page process which is triggered by a button submit and will return an customized error message.

Please give me some help and you can have a look at the application at http://apex.oracle.com/pls/apex

The credentials are:
Work Space: dianti
Username: wangsc66@gmail.com
Password: wangsc198453

Thank you in advance!
Roel said…
1. There was an unintended line break in the javascript of your HTML Header. You could have seen that, when you used Firefox and Firebug.

2. GetErrorMessage should be an Application Process (On Demand) and not a Page Process.

I made the changes and now it's running fine...apart from the fact that there is an image referenced in the Javascript that you don't have. Change it with one you like...

Good luck!

BTW You might want to change your password now, because your credentials now appear in my blog ;-)
Frank said…
Very cool code,

Additional, When using headstart you can use the stack to display all error messages together by using procedures: cq$errors.get_error_messages (p_message_rectype_tbl => l_message_rectype_tbl, p_message_count => l_message_count
,p_raise_error => l_raise_error);
and cg$errors.get_display_string(
p_msg_code => l_message_rectype_tbl(i).msg_code
,p_msg_text => l_message_rectype_tbl(i).msg_text
,p_msg_type => l_message_rectype_tbl(i).severity)


Greetings, Frank Bohlken
Kasman said…
great tips ,Thanks!

By the way, to correct Vincent issue. You can trap the no_data_found exception like:
..
NO_DATA_FOUND EXCEPTION;
begin
..
IF p_error_title is NULL THEN
RAISE NO_DATA_FOUND;
END IF;

works for me :-)

Kasom
Perurena said…
Hi!
It's been long time since the post and I know there is available a new stuff in 4.1 version to handle this kind of exception but I am not authorised to upgrade the version so I have been trying to implement your codes and I have a problem with the last step:

I have created the table AFK_MESSAGE_PROPERTIES with two columns: CODE and DESCRIPTION but creating the new procedure I have this error:

Error at line 7: PL/SQL: SQL Statement ignored

5. ) AS
6. CURSOR c_mps IS
7. SELECT code, description
8. FROM afk_message_properties
9. WHERE INSTR( p_ora_error, constraint_name ) > 0;


I am newbie with APEX and SQL/PL so I don't know how to fix it!

Thanks for everything!
Roel said…
The table should also contain a column called ' constraint_name '
Jesus said…
Hi everyone!
I'm trying to follow this guide but it doesn't work for me. I'm not sure if I've written those codes at the correct places. So I whould thanks any help.

First I've written the step 1's code at the Error Page Template Control.

Then I've copied the step 2's code at the HTML header of the initial page. Should I put the < script > tag there?

After that I've introduced the step 3's code at the process text.

Finally i created a table with 3 columns: CODE, DESCRIPTION and CONSTRAINT_NAME.

I created a new row with this data:

CODE: 2292

DESCRIPTION: ERROR

CONSTRAINT_NAME: ORA-02292

When I run the app and generate that error it doesn't work.

Thanks for the help.
Roel said…
Yes, step 2 needs to be surrounded with script tags. Check you console (Firefox with firebug or Chrome) for any errors.
M.jovaini said…
Hi Roel Hartman
your solution dosen't work in my application folowing my step please help

step 1 : copy in my error template page .

step 2 : copy text in my header template and create js file use in header

And


Step 3 : Create Ondemand Process in application

DECLARE
p_error_title varchar2(32767);
p_error_text varchar2(32767);
p_ora_error varchar2(32767);
BEGIN
p_ora_error := wwv_flow.g_x01;
-- Get "translated" error message
get_error_message( p_ora_error, p_error_title, p_error_text );
htp.p( p_error_title||'#'|| p_error_text );
END;

Step 4 : creat PROCEDURE In my schema

create or replace PROCEDURE GET_ERROR_MESSAGE
( p_ora_error IN VARCHAR2
, p_error_title OUT VARCHAR2
, p_error_text OUT VARCHAR2
) AS
CURSOR c_mps IS
SELECT code, description
FROM afk_message_properties
WHERE INSTR( p_ora_error, constraint_name ) > 0;
BEGIN
OPEN c_mps;
FETCH c_mps INTO p_error_title, p_error_text;
CLOSE c_mps;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_error_title := 'No translation found for this error';
p_error_text := p_ora_error;
END GET_ERROR_MESSAGE;
Roel said…
Can you set up your case on apex.oracle.com? You're probably missing out something small...
Perurena said…
Hi!

I have been trying and trying to implement your solution but still doesn't working and I don't know why!

It would be nice if you could have a look because this is frustating!

By the way, the html header is in the page 1.

Workspace: ERROR_TEST
Username: errorhandler
Password: errortest

Thanks for everything!
M.jovaini said…
My problem was solved this way

step 1 : create table CONSTRAINT_MSG
(
CONSTRAINT_NAME VARCHAR2(30) not null,
MESSAGE VARCHAR2(4000) not null
);

Step 2 : insert all constraint in table For Example

insert into constraint_msg (CONSTRAINT_NAME, MESSAGE)
values ('JP_PROADCOND_FK', 'you cant delete JP_PROADCOND ');

Step 3 : create or replace function apex_error_handling_msg (
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.is_internal_error then

if p_error.apex_error_code <> 'APEX.AUTHORIZATION.ACCESS_DENIED' then

l_result.message := 'An unexpected internal application error has occurred. '||
'Please get in contact with XXX and provide '||
'reference# '||to_char(l_reference_id, '999G999G999G990')||
' for further investigation.';
l_result.additional_info := null;
end if;
else

l_result.display_location := case
when l_result.display_location = apex_error.c_on_error_page then apex_error.c_inline_in_notification
else l_result.display_location
end;


if p_error.ora_sqlcode in (-1, -2091, -2290, -2291, -2292) then
l_constraint_name := apex_error.extract_constraint_name (
p_error => p_error );

begin
select message
into l_result.message
from constraint_msg
where constraint_name = l_constraint_name;
exception when no_data_found then null; -- not every constraint has to be in our lookup table
end;
end if;


if p_error.ora_sqlcode is not null and l_result.message = p_error.message then
l_result.message := apex_error.get_first_ora_error_text (
p_error => p_error );
end if;


if l_result.page_item_name is null and l_result.column_alias is null then
apex_error.auto_set_associated_item (
p_error => p_error,
p_error_result => l_result );
end if;
end if;

return l_result;
end apex_error_handling_msg;
/
Step 4 : Set Function in your application
in error handeling region
Alejandro said…
Hi Roel Hartman, I've followed all the steps but it doesn't work. I think that the error is the jQuery. Does Apex 4.02 support the jQuery defaultly or do i have to install it?
If so how can I install it?

Thanks for the help.
Roel said…
I thought so (but not sure now). A step-by-step install guide can be found on http://oracleapplicationexpress.com/tutorials/66-integrating-jquery-into-apex
Roel said…
As the template is changed in 4.1, the call to $(".ErrorPageMessage") in the function returns nothing...so the rest won't work either. When you are on 4.1, please use the regular, new, error handling feature.
(BTW I moved your code to Page 0 - so it's included in all pages, as it makes no sense on Page 1 - which is a report that will never raise an error).
Alejandro said…
Hi everyone!
I followed all the steps but the jQuery doesn't work. Should I put the jQuery and the jQueryUI or just the jQuery. Where should i put the code?

thanks for the support.
Tom said…
Hi!

First of all, congratulations for your blog!

I have been reading about jQuery and I figured out that
"vError = $(".ErrorPageMessage");
vError.hide();"
dollar symbol is from jQuery but my doubt is, what is these "ErrorPageMessage" where is in the app?

Thank you in advance!
Roel said…
ErrorPageMessage is referring to a DIV with a class of ErrorPageMessage. In the latest version (4.x) that class isn't used anymore - or maybe only using specific templates. So this example will only work in 3.2 (not sure about 4.0).
But when you are on 4.1, please use the new Error Handling feature that's built in!
Roel said…
If you can enter something like $('table') in the javascript console of your browser and it does return something (a list of table nodes of your html document), jQuery is available.
And you can put code like <script type="text/javascript" src="#WORKSPACE_IMAGES#jquery-1.3.1.min.js"></script> in either your Page HTML Header (only for that page) or in your Page Template (for all pages).

Popular posts from this blog

apex_application.g_f0x array processing in Oracle 12

If you created your own "updatable reports" or your custom version of tabular forms in Oracle Application Express, you'll end up with a query that looks similar to this one: then you disable the " Escape special characters " property and the result is an updatable multirecord form. That was easy, right? But now we need to process the changes in the Ename column when the form is submitted, but only if the checkbox is checked. All the columns are submitted as separated arrays, named apex_application.g_f0x - where the "x" is the value of the "p_idx" parameter you specified in the apex_item calls. So we have apex_application.g_f01, g_f02 and g_f03. But then you discover APEX has the oddity that the "checkbox" array only contains values for the checked rows. Thus if you just check "Jones", the length of g_f02 is 1 and it contains only the empno of Jones - while the other two arrays will contain all (14) rows. So for

Filtering in the APEX Interactive Grid

Remember Oracle Forms? One of the nice features of Forms was the use of GLOBAL items. More or less comparable to Application Items in APEX. These GLOBALS where often used to pre-query data. For example you queried Employee 200 in Form A, then opened Form B and on opening that Form the Employee field is filled with that (GLOBAL) value of 200 and the query was executed. So without additional keys strokes or entering data, when switching to another Form a user would immediately see the data in the same context. And they loved that. In APEX you can create a similar experience using Application Items (or an Item on the Global Page) for Classic Reports (by setting a Default Value to a Search Item) and Interactive Reports (using the  APEX_IR.ADD_FILTER  procedure). But what about the Interactive Grid? There is no APEX_IG package ... so the first thing we have to figure out is how can we set a filter programmatically? Start with creating an Interactive Grid based upon the good old Employ

Stop using validations for checking constraints !

 If you run your APEX application - like a Form based on the EMP table - and test if you can change the value of Department to something else then the standard values of 10, 20, 30 or 40, you'll get a nice error message like this: But it isn't really nice, is it? So what do a lot of developers do? They create a validation (just) in order to show a nicer, better worded, error message like "This is not a valid department".  And what you then just did is writing code twice : Once in the database as a (foreign key) check constraint and once as a sql statement in your validation. And we all know : writing code twice is usually not a good idea - and executing the same query twice is not enhancing your performance! So how can we transform that ugly error message into something nice? By combining two APEX features: the Error Handling Function and the Text Messages! Start with copying the example of an Error Handling Function from the APEX documentation. Create this function