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') 

A method to execute external programs via APEX

Some time ago, when I was working on an Oracle Forms project, we used the host command to execute commands on the server. For details check this link.

It is a really cool feature, because it allows you to execute external programs from outside the Oracle Forms environment. So for instance, one can start a script or program controlling some device. The command however is not available in SQL *Plus, giving a hint to availability in Oracle APEX.

That given, it raises the question on how to `access' the OS from within APEX. The answer is somewhat unexpected: DBMS_SCHEDULER. Here the CREATE JOB procedure allows for job type `EXTERNAL'. (note that the CREATE EXTERNAL JOB privilege has to be granted.).

To illustrate the above: here is the code I used on my Oracle XE installation (Windows):

-- Execute as SYS user and replace <<user>> by the correct user.
-- external job privilege is required to make scheduler jobs 
-- of type executable.
--
-- grant create external job to <<user>>;

begin
 dbms_scheduler.create_job(   job_name      => 'TST_EXT_001'
                            , job_type      => 'EXECUTABLE'
                            , job_action    => 'c:\script1.bat');
end;    

to execute the job (or schedule it, whatever fancies you):

-- execute job
begin
 dbms_scheduler.enable('TST_EXT_001');
end;

In case nothing happens: make sure that the OracleJobSchedulerXE service is up. Here script1.bat is a somewhat academical piece of code, writing the output of the windows dir command to a textfile. Here is the listing of the batch file:

 dir c:\temp > c:\templist.txt

To make the program more interesting we add some parameters. Here and here you will find an explaination for programming Windows batch files, and Unix bash files respectively. Let's add a parameter to the batch script that allows for a different output file.

@ECHO OFF
REM
REM This program will list the files in c:\temp and
REM write the output to a specified output file.
REM
REM If no outputfile is specified the program will write to
REM default output: c:\output.txt
REM

IF "%1"=="" ( SET OUTPUTFILE=c:\output.txt ) ELSE (  SET OUTPUTFILE=%1)

DIR C:\TEMP > %OUTPUTFILE%

REM Clean up, unsetting used variables

SET OUTPUTFILE=

Using the variable in the job_action variable of create_job, will not give the desired result. Instead, we add the set_job_argument_value procedure from dbms_scheduler, after the statement defining the job:

begin
 dbms_scheduler.create_job(   job_name      => 'TST_EXT_002'
                            , job_type      => 'EXECUTABLE'
                            , job_action    => 'c:\script2.bat'
                            , number_of_arguments => 1
                            , enabled => FALSE);

 dbms_scheduler.set_job_argument_value('TST_EXT_002',1,'c:\result.txt');
end;

Now when we enable the job, we see that on the filesystem a file is created, result.txt containing the directory listing of c:\temp. In case of unexpected result: one can check the outcome of the posted job via the following query:

select *
from   all_scheduler_job_run_details
where  job_name = 'TST_EXT_002'