APEX patchset released, fixing bug dataload mechanism

Good news.

"For existing Application Express 4.2.0 installations, please download the Application Express 4.2.1 patch set from My Oracle Support, Patch #: 14732511" 

Check http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-421-patch-set-notes-1885751.html

for the relevant patch-set-notes. I am excited to see that bug #14803538 is adressed. I am just in the process of checking! Somebody is going to be happy!

For completeness sake here is the somewhat more elaborate description of the bug:

I'm having this weird issue with the dataload mechanism, first seen in APEX 4.1 and it seems to persist in APEX 4.2. I believe it was resolved in 4.1.1 ref patch set notes 13656397, somehow it is not;

Bear with me: I'm trying to insert / update the record

9-10-2012            32.110   78.249   10.359   V

(that is: 9 oct 2012, and four more columns)

in my table RIS_PAX_VOORL via the data-load-wizard. This table is defined as follow:

column                                | datatype            | constraint
===========================================
dag                                     | date                   | pk  
aank_vertr_code                 | varchar2(1)       | pk
od_pax                               | number              | not null
transfer_pax                        | number              | not null
tot_pax                               | number              | not null

Note that the primary key is composed of 2 columns, one of which is date type: dag and aank_vertr_code. In the data-load wizard I defined that both dag and aank_vertr_code should be used to determine a record uniquely, and for the moment no other transformations / look-ups are used. When using the functionality, the wizard updates the record 10-sep-2012 V ... ? That's not good.

Setting a date format mask (DD-MM-YYYY) during the `Data mapping' step does not seem to have effect. The wizard is stubborn and "want's" to see september instead of october.

To make this a little more interesting I cleared out the table and inserted manually two records :

truncate table ris_pax_voorl;
--
insert into ris_pax_voorl (dag,
                           aank_vertr_code, od_pax, transfer_pax, tot_pax) 
            values        (to_date('09-10-2012', 'DD-MM-YYYY'),
                           'V', 123, 456,789);
--
insert into ris_pax_voorl (dag, 
                           aank_vertr_code, od_pax, transfer_pax, tot_pax) 
            values        (to_date('10-09-2012', 'DD-MM-YYYY'),
                          'V', 321, 654,987);

To eliminate any static i'm now using the wizard to update the first record, with the following input:

09-OCT-2012;V;9999;9999;9999

Ofcourse we set ';' as seperator character and uncheck the `First row contains...' option. In the data-map we adres the column mapping, not using any format masks. Next step data validation
has detected an UPDATE, so far so good, next step loading the data: updated 1 record, no errors. Excellent! However:

select * from ris_pax_voorl

10-SEP-12          V             321         654         987
09-OCT-12         V             123         456         789

No changes! Ok. Trying again with 09-10-2012;V;9999;9999;9999, format mask DD-MM-YYYY, same result. Now, again, let's start of with a clean sheet:

truncate table ris_pax_voorl; and use 09-10-2012;V;9999;9999;9999 in the loading wizard. This record gets inserted as 10-sep-2012.

again

truncate table ris_pax_voorl; and use 09-10-2012;V;9999;9999;9999 in the loading wizard. This time we set a format mask: DD-MM-YYYY.

This record gets inserted as 10-oct-2012. Excellent! Now let's update this guy: use 09-10-2012;V;123;456;789 format mask DD-MM-YYYY; Ah but now an INSERT is detected;
Performing the insert triggers a unique constraint violation!

This is not good.

Update 15-02-2013
Still not working as expected, but there is a workaround. After adding a transformation rule in the data loading definition based on the DATE column it is now working. The transformation rule is:

 to_date(:DAG,'DD-MM-YYYY')