Inside Oracle APEX by Patrick Wolf

Have you ever wondered why you have to create extra validations for required items (item is NOT NULL), for date picker items (item is a valid date) or for numeric items where you have already specified a numeric format mask?

If you don’t create this checks, the “Automatic row processing DML” process will do some of this checks, but if they fail the error message isn’t really nice and it will not show as inline field error message. But what happens if you have your own process instead of the “Automatic row processing DML”? Nobody will do the check for you.

Or what happens if you have a validation where you access a page item with TO_NUMBER or TO_DATE? You will get a nasty value error. BTW, you also have to duplicate the format mask for TO_NUMBER/TO_DATE which is already defined in the item in that case…

I think another drawback is also sometimes that APEX will re-render the page in case of an error, that will discard all your changes in a tabular form or password items…

What can we do?

Make your application save and user friendly and create all this additional validations, so that your users get nice error messages. An be careful that the developers always use the same error message and that they change the error message if the label changes… Lot of work, lot of “fun” and don’t forget it if you create a new date picker item… ;-)

Why don’t care about it and let it do automatically? All the information is already in the APEX repository tables, to do this kind of checks. We know what is a date picker- or a numeric item (if the format mask is set). We can also assume if the label template is Required% that the item should be required.

That’s what the new package ApexLib_Item does. It validates all page items based on the APEX repository. And if you don’t want to have this functionality on one of your regions/items, just specify $APEXLIB_IGNORE_VALIDATION$ in the object comment, so that the library ignores your item.

The package ApexLib_Item also offers some getter functions V/NV/DV which you can use to access your page items. One advantage of this methods is that they use the format mask of the item to do the conversion into the PL/SQL data type, so you don’t have to duplicate your format mask. BTW, it also checks if the page item exists, APEX ignores that and just returns NULL…

When you try the example you will also notice that the error isn’t displayed on the error page or that the page is re-rendered, instead the original page is displayed. So you don’t loose passwords or tabular form values! The error messages are still displayed as inline field messages. Maybe you will also notice that the inline field error message is shorter than the error message in the message area. I thought that’s a good idea so that the layout isn’t destroyed by long inline error messages.

Try the example page I have set up. It’s a simple master detail page where I used the wizard generated code and just added

• the date picker where necessary
• added a numeric format mask for numeric items
• set the label template of Salary to “Required with Help” to demonstrate that the check is done without a validation
• added $APEXLIB_REQUIRED$ for the tabular form columns (see Plug & play tabular form handling)
• and added a cascading lov between department and manager (just added a WHERE DEPARTMENT_ID = :P31_DEPARTMENT_ID, see Generic solution for cascading select lists)

That’s all. If you want to use eg the hire date in a validation expression, just use ApexLib_Item.DV(’P31_HIRE_DATE’) and it will return the date in the correct format.

Integration into your application

Follow the steps in my How to integrate posting.Not happy with my default error messages? You can define your own at Shared Components/Messages, the names can be found in the integration posting.

Known restrictions

• If a page item has a condition, no required check (format mask is always checked) will be done, because the library doesn’t know if the condition is true or false.
• If a region has a condition, same as above for all items of that region.
• If the names of the regions on a page are not unique, same as above because the library isn’t sure if there is a region with a condition. This restriction only applies if the library is installed into the application schema, because the APEX repository views don’t provide the unique region_id. You can use the following SQL statement to determine if you have such a duplicate problem on one of your pages.

  SELECT APPLICATION_ID, PAGE_ID, REGION_NAME
    FROM APEX_APPLICATION_PAGE_REGIONS
   GROUP BY APPLICATION_ID, PAGE_ID, REGION_NAME
  HAVING COUNT(*) > 1
  ;

• If the page item has “No Label” or no selected template, the error message can’t be displayed as inline error for that item.

Design Decision

Normally APEX executes all the defined validations without stopping if one fails. I think that’s a good idea. But I think in case of invalid format masks or values missing, do the other checks really make sense? Eg. that you get a value error later on in a validation where you check that hire_date > sysdate?

That’s why my validation will stop processing if there are any format mask or required item errors. But it will show all of them.

Problems or Questions?

My page item isn’t validated by your library!

• Have you set a format mask for the numeric item?
• Have you set a date picker?
• Does the item/region have a condition/authorization?
• Enable debug mode, there you see why the library isn’t validating your item as soon as you press submit

I have tested the library with FireFox 2.0 and IE 6.0, if you have another browser where it doesn’t work, let me know.

If you have any suggestions or comments, just write a posting comment or write me an e-mail!.