ORA-01830, Apex and custom date formats

One of the frequent mistakes that one can make in Apex, especially when one is a beginner, is to forget the fact that page items in the session cache are stored as strings.

As a consequence, sometimes you see processes where PL/SQL code does not contain a proper function for converting the values from the displayed format to the required oracle type before storing them into a table or into variable.

Storing numbers and dates as strings brings about some consequences that you must bear in mind when implementing the underlying db procedures, especially if the application is aimed at a multinational public, where numeric and time/date separators can vary with each user.

A classic error message raised in such situations is:

ORA-01830: date format picture ends before converting entire input string

For a generic explanation of this error at the db server level, see my previous posting.

It's easy to explain why it occurs.
Create a page item of type date as a pop-up calendar.
Pick a long date format like "DD-MON-YYYY HH:MI".
Create an after submit page process bound to some button that inserts the page item into a table without explicitly converting the value, something like:

insert into test (date_inserted) values (:P1_DATE);

finally run the page and voilà:

ORA-01830: date format picture ends before converting entire input string

The fact is that Apex is internally using the default NLS_DATE_FORMAT, "DD-MON-RR", whereas the string stored in P1_DATE is in the format specified in the page item attribute.

When you handle P1_DATE as a bind variable you are handling just a string value, not a date value, so if you don't specify an explicit conversion in the insert statement, Oracle is assuming that it is formatted as DD-MON-RR, hence the error message.

However there are situations where Apex is much smarter and there also techniques that allow you to write code that is not bound to a format hard-coded into the page definition, which results in a reduced risk of getting ORA-01830.

Let's examine them.

Forms written using Apex DML processes perform an automatic conversion, basing on the format mask specified in the Tabular Form Element Date Picker Format Mask.



You can spot the underlying NLS_DATE_FORMAT (DD-MON-RR) located in the upper part of this test page.

If you turn off debugging and try to add a row containing a date formatted as DD/MM/YYYY, Apex will perform the required conversion automatically and without writing a line of code.

Note also that as of Apex 3.0, it is possible to define a custom date format mask for a pop-up calendar item and this parameterized value can be set in different ways.

A first way is to define an application substitution string and call it PICK_DATE_FORMAT_MASK then select "Date Picker (use application format mask)" as Date Picker Format Mask. Please note that this applies to both page items and column attributes in reports.

This method is very static and is good for ensuring consistency throughout all the application on all pop-up items defined in the same way. It only need to be defined centrally, in the Shared Components/Application Definition substitution strings.

A more flexibile way is to define an application item called PICK_DATE_FORMAT_MASK.
This will enable a dynamic setting of the date format in contrast with the static value shown earlier. Indeed you can create a page with a LOV based select list where you allow a user to pick the desired format or even write it manually, exactly as it happens in the Application Builder itself. Whatever you decide to with the users, you'll need to initialize the value of the item in some way before using it.

A third way, described rather hastily in the on-line help, is to choose "Date Picker (use item format mask)". This method is the most flexible as the "date picker will take the date format from the Format Mask attribute on the Edit Page Item page".
The first time i read this topic, it sounded rather confusing because it doesn't explain well what's the difference with the method shown earlier, moreover it doesn't mention how to do that in reports.

Let's try to fill out the gap.

First of all the difference lies in the fact that you can use page items to store the date format mask and this means allowing multiple formats at the same time for the same user within the same page (which can sound a rather odd requirement, but it does have sense actually).

Secondly it allows to specify this page item as a substitution string in the Format Mask attribute in the Source section of the Edit Page Item page:



or in the Column Formatting Date Format attribute of the Report Attibutes page:



Please note that in the latter case the attribute Number/Date Format in the Report Attributes page will be initially grayed out. It will become editable as soon as you pick "Date Picker (use item format mask)" in the Date Picker, which is located further down in the page.

Also in the case of the page item, don't forget to specify the default value or initialize the item with a computation.

You can see here the three different methods at work.
In order to achieve a dynamic effect, one could use an AJAX process to update the report after changing the format mask.

As a final note, keep in mind that when defining a default value for a page item of type date picker (like TRUNC(SYSDATE) for instance), which requires also changing the source type from Static Assignment to PL/SQL Expression by the way, requires an explicit conversion because the format mask is applied when picking the value from the pop-up window, not when retrieving the default value. So, instead of writing just TRUNC(SYDATE), you are better off writing TO_CHAR(SYSDATE,:P12_DATE_FORMAT) or whatever is the name of the item holding the date format in your case.