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'

AJAX inside a tabular form

Ok, this I found to be a nice APEX puzzle. Consider the following use-case:

An order input form is considered. It is a tabular form containing two functionally related colums. One is a selectlist, listing available articles, the other is showing the default price of the item, as defined in a lookup table.

The goal is, when the user selects an article, the price is updated via an AJAX call, hence without a complete page refresh. Sounds like a fair requirement!


here you'll find a working demo.


The problem is, that one cannot simply define a dynamic action solving the above(that is, I could'nt think of one). To solve it however, it is really a matter of identifying the source element on the page (the article) and the target element (the price) which should be updated. When working on a `static' page this is not too hard, but a tabular form should be considered more dynamic.

In APEX tabular-form input fields have the following format:

<input type="text" name="f04" size="16" 
       maxlength="2000" value="30"  id="f04_0001" autocomplete="off">

One could imagine a tabular form to be like a mathematical matrix, that is an ordered object with rows and colums that can be numbered in a countable fashion. On the DOM the columns are identified by the name attribute, and the rows are numbered in order of appearance. The element of interest is, ofcourse, identified by the value of the id attribute and is situated at the intersection of row and column. In the above example id = f04_0001, hence column 4 row 1. The id attribute is exactly in this format:

 fxx_yyyy, that is column_row

Now that we can identify the source element, we should identify the target element. Remeber that like the source element, the target element is also of the format fxx_yyyyy, where at most the x's differ in value. What we can do is add the html attribute class with value `price' (report attributes > column attributes) to the elements concerned, giving means to determine the column value fxx of the target element.

<input type="text" name="f06" size="16" class="price"
       maxlength="2000" value="30"  id="f06_0001" autocomplete="off">

Next, onchange of the source element, we want to update the target element. This is easy. Go to: Column attributes > element attributes of the atricle field in the tabular form and type:

onchange="updatePrice(this.id)"

So, onchange we call the JavaScript function updatePrice with argument this.id. `this' is the object in context, hence, here the element that is changed. Now we almost have all the ingredients we need. Next is defining an AJAX call, taking the id as input, and returning the price. In APEX we define two objects. The first is application item g_art_id, the second is an application process getPrice.

getPrice has the following listing:

declare 
 l_prijs artikelen.prijs%type;
begin
 select 
       art.prijs into l_prijs 
 from  artikelen art 
 where art.id = :g_art_id;
 --
 sys.htp.p( l_prijs );
exception when no_data_found then
 sys.htp.p( 'Article not found' );
end;

Please observe that the name of the application process is case sensitive and do take care of trailing blanks... The following JavaScript should be placed in the header of the page:

<script type="text/javascript">
//
// updatePrice takes a source_id (article), executes an AJAX call and 
// updates the DOM.
//
function updatePrice(source_id)
{
//
var c_target_class = 'price';
var l_target_id    = getTargetRowId(source_id, c_target_class);
var l_art_id       = getValue(source_id); 
//
setPrice(l_art_id, l_target_id);
//
return null;
};
//
// getTargetRowId determines the target_row_id 
//
function getTargetRowId(source_row_id, target_class){
//
var l_name;
var l_regexp;
var l_targetrowid;
//
// given the class attribute, determine which fxx column to use
// Result of the query below is the name of the inputfield in 
// the target class
//
l_name        = $('.' + target_class).first().attr('name');
//
// source_row_id has the format fxx_yyyyy, where xx refers to the column.
// The target_id is computed by replacing the xx with the relevant value.
// We use a regular expression
//
l_regexp      = /f[0-9]+_/;
l_targetrowid = source_row_id.replace(l_regexp, l_name + '_');
//
// Result is the id of the target input-field
//
return l_targetrowid;
//
};
//
function getValue(id){
return $('#' + id ).val();
};
//
// setPrice takes a primary key value of an article
// and determines the price via an AJAX call.
//
// Second argument is the id on the DOM of the inputfield which should 
// be updated.
//
function setPrice(art_id, target_el_id){
// 
// First initialize the object which set's up the AJAX call
//
var get = new htmldb_Get(null,html_GetElement('pFlowId').value,
                         'APPLICATION_PROCESS=getPrice',0);

//
// Add an attribute and value to the call.
//
get.add('G_ART_ID',art_id);
//
// Use the get method to execute the AJAX call
//
var gReturn = get.get();
//
// Update the DOM. $x is an APEX javascript API.
//
$x(target_el_id).value = gReturn;
//
// clean up...
//
get     = null;
gReturn = null;
//
return null;
};
</script>

Done! Give it a try here . To sum up: it all boils down to identifying a source element, give it a triggering condition, do something, identifying and finallly updating the target element.

Wow, sounds almost like a dynamic action!

How to list the last N years?

Sometimes we are required to facilitate a select list, from which a user can select the last, say 25 years (including the current year). Instead of defining a reference-table `YEARS', we have to maintain, we can define a SQL query using the CONNECT BY function. Here is how:

 
select to_char(sysdate,'YYYY') - level + 1 
from dual 
connect by level <= 25

Ofcourse the 25 in the above example is arbitrary, one could just instead use a package constant or some function.

In Oracle APEX, when defining this in a select list, it is tempting to write the following code:

 
select 
  to_char(sysdate,'YYYY') - level + 1 display
, to_char(sysdate,'YYYY') - level + 1 return
from dual 
connect by level <= apex_constants.g_list_years

From a maintainability perspective one should consider implementing a shared component LOV based on the following query:

 
select 
   years display
 , years return
from (
      select 
       to_char(sysdate,'YYYY') - level + 1 years
      from dual 
      connect by level <= apex_helper_functions.list_number_of_years
     )

The `list_number_of_years' function would preferably make use of a APEX session-state value - when defined -, to give maximal flexibility in a given page.

Once defined in this manner, you will hopefully agree the list is easy to maintain.

[update 26-11-2012]
A carefull reader noticed (thanks JL!) that using the Oracle EXTRACT function would shave off one implicit conversion, resulting in a better performance (EXTRACT returns a NUMBER instead of VARCHAR2, so when doing - LEVEL + 1 Oracle doesn't have to convert to NUMBER). Here is the code:

 
select 
   years display
 , years return
from (
      select 
       extract(year from sysdate) - level + 1 years
      from dual 
      connect by level <= apex_helper_functions.list_number_of_years
     )

Always open for suggestions! Thanks JL!