Unique constraint WWV_FLOW_WORKSHEET_RPTS_UK violated

If your APEX application import log shows something like this:

...PAGE 73: Transaction Lines Report
declare
*
ERROR at line 1:
ORA-00001: unique constraint (APEX_040200.WWV_FLOW_WORKSHEET_RPTS_UK)
violated
ORA-06512: at "APEX_040200.WWV_FLOW_API", line 16271
ORA-06512: at line 6

(this is on an Apex 4.2.4 instance)

This is due to a Saved Report on an Interactive Report that was included in the export, which conflicts with a different Saved Report in the target instance. The log will, conveniently, tell you which page the IR is on.

The solution for this problem is simple – either:

(a) Export the application with Export Public Interactive Reports and Export Private Interactive Reports set to No;
OR
(b) Delete the Saved Report(s) from the instance you’re exporting from.

You can find all Saved Reports in an instance by running a query like this:

select workspace
      ,application_id
      ,application_name
      ,page_id
      ,application_user
      ,report_name
      ,report_alias
      ,status
from APEX_APPLICATION_PAGE_IR_RPT
where application_user not in ('APXWS_DEFAULT'
                              ,'APXWS_ALTERNATIVE');

You can delete Saved Reports from the Application Builder by going to the page with the Interactive Report, right-click on the IR and choose Edit Saved Reports, then select the report(s) and click Delete Checked.


Declarative Tabular Form dynamic totals

A common APEX project is to take a customer’s existing spreadsheet-based solution and convert it more-or-less as is into APEX. I’ve got one going at the moment, a budgeting solution where users need to enter their budget requests. They currently enter their requests into an XLS template file which generates subtotals and totals for them.

To do this in APEX I’m going to use a tabular form, and to do the subtotals I’ll use jQuery in a way not too dissimilar to that I described earlier.

Here is a mockup of the screen so far:

apex-grid-sheet

There are column totals that need to be added up and updated dynamically (indicated by the green arrows) as well as subtotals within each row (indicated by the red arrows).

I started by looking at the generated items, getting their ids (e.g. “f09_0001” etc) and writing the jQuery code to detect changes, add them up, and put the totals in the relevant items. I then started repeating this code for each column, and thought “hmmm”.

There were two problems with this approach that I could foresee:

  1. The generated ids in a tabular form can change if the structure of the query changes  – e.g. what was f08 + f09 => f10 might change to f09 + f10 => f11
  2. I was aware of another form that I would need to build, with a similar structure except that there will be two sets of “Jan-Jun” + “Jul-Dec” columns, each with their own subtotal.

I wanted a more declarative solution, so that the heavy lifting will be done in one set of generic javascript functions, and I simply need to put attributes in the relevant columns to activate them. This is how I’ve approached this:

  • Create the tabular form as usual (mine is based on an APEX Collection) and remove the standard DML processes, replaced with my own that calls APEX_COLLECTION instead.
  • Create a standard report that generates the total items by calling APEX_ITEM.text, with p_attributes=>'data-total="x"' (with a different “x” for each column, e.g. year1).
  • Set the Static ID on the tabular form region (e.g. tabularform).
  • Set Element Attributes on the Jan-Jun column to data-cell="year1" data-col="year1_jan_jun", similarly for the Jul_Dec column.
  • Set Element Attributes on all the Year columns in the tabular form to data-col="yearx", where x is 1..5.
  • Set Element Attributes on the total for the first year to data-subtotal="year1".

The following is the query for the totals report region:

select APEX_ITEM.text(1, TO_CHAR(SUM(year1_jan_jun),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year1_jan_jun"') as year1_jan_jun
      ,APEX_ITEM.text(2, TO_CHAR(SUM(year1_jul_dec),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year1_jul_dec"') as year1_jul_dec
      ,APEX_ITEM.text(3, TO_CHAR(SUM(year1_total),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year1"') as year1_total
      ,APEX_ITEM.text(4, TO_CHAR(SUM(year2_total),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year2"') as year2_total
      ,APEX_ITEM.text(5, TO_CHAR(SUM(year3_total),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year3"') as year3_total
      ,APEX_ITEM.text(6, TO_CHAR(SUM(year4_total),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year4"') as year4_total
      ,APEX_ITEM.text(7, TO_CHAR(SUM(year5_total),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year5"') as year5_total
from budget_collection_vw

So, to summarise: all the data-cell items get totalled to the data-subtotal item in the same row; and all the data-col items get totalled to the data-total item below the tabular form.

To do all the hard work, I’ve added the following code to my page’s Function and Global Variable Declaration:

function getSum (qry) {
  //get the sum over all items matching the given jQuery search criterion
  var t = 0;
  $(qry).each(function() {
    t += parseFloat($(this).val().replace(/,/g,''))||0;
  });
  return t;
}

function updateSubTotal (item) {
  // update a row-level subtotal
  // the items to add up are identified by data-cell="x"
  // the item to show the total is identified by data-subtotal="x"
  var cell = $(item).data("cell") //get the data-cell attribute
     ,rn = $(item).prop("id").split("_")[1]
     ,t = getSum("input[data-cell='"+cell+"'][id$='_"+rn+"']");

  // we need to temporarily enable then disable the subtotal
  // item in order for the change event to fire
  $("input[data-subtotal="+cell+"][id$='_"+rn+"']")
    .val(t.formatMoney())
    .prop("disabled",false)
    .trigger("change")
    .prop("disabled",true);
}

function updateTotal (item) {
  // update a column total
  var col = $(item).data("col") //get the data-col attribute
     ,t = getSum("input[data-col='"+col+"']");

  $("input[data-total="+col+"]")
    .val(t.formatMoney())
    .trigger("change");
}

In case you’re wondering, I’m re-using the formatMoney function here.

There’s a number of things happening here. On page load, we add a listener for changes to any input item that has a data-cell attribute; this calls updateSubTotal, which detects the row number for the triggering item, adds up all the values for any input item that has the same data-cell value; and puts the total in the input item with a matching data-subtotal attribute.

We also have a listener for changes to any item with a data-col class; when these are changed, updateTotal adds up any item with the same attribute, and puts the total in an item with attribute data-total.

The jQuery selector [id$='_"+rn+"'] makes sure that the row-level code only finds items ending with the given row number (i.e. '*_0001').

The benefit of this declarative approach is that it is much easier to re-use and adapt.

EDIT: fixed the change trigger so that I don’t need to call updateTotal from updateSubTotal.


BIG checkboxes

Getting older, it’s getting harder to see and click those tiny checkboxes…

checkboxestoosmall

csscheckboxes

input[type=checkbox] {
/* Double-sized Checkboxes */
-ms-transform: scale(2); /* IE */
-moz-transform: scale(2); /* FF */
-webkit-transform: scale(2); /* Safari and Chrome */
-o-transform: scale(2); /* Opera */
}

checkboxesbig

CAN YOU SEE THEM NOW? Ah, good. That’s all right then.

Brought to you by dept-of-coding-by-copy-and-paste.


APEX API for Tabular Forms

grid-edit
Ever since I started exploring the idea of using a TAPI approach with APEX, something I was never quite satisfied with was Tabular Forms.

They can be a bit finicky to work with, and if you’re not careful you can break them to the point where it’s easier to recreate them from scratch rather than try to fix them (although if you understand the underlying mechanics you can fix them [there was an article about this I read recently but I can’t find it now]).

I wanted to use the stock-standard APEX tabular form, rather than something like Martin D’Souza’s approach – although I have used that a number of times with good results.

In the last week or so while making numerous improvements to my TAPI generator, and creating the new APEX API generator, I tackled again the issue of tabular forms. I had a form that was still using the built-in APEX ApplyMRU and ApplyMRD processes (which, of course, bypass my TAPI). I found that if I deleted both of these processes, and replaced them with a single process that loops over the APEX_APPLICATION.g_f0x arrays, I lose a number of Tabular Form features such as detecting which records were changed.

Instead, what ended up working (while retaining all the benefits of a standard APEX tabular form) was to create a row-level process instead. Here’s some example code that I put in this APEX process that interfaces with my APEX API:

VENUES$APEX.apply_mr (rv =>
  VENUES$TAPI.rv
    (venue_id   => :VENUE_ID
    ,name       => :NAME
    ,version_id => :VERSION_ID
    ));

The process has Execution Scope set to For Created and Modified Rows. It first calls my TAPI.rv function to convert the individual columns from the row into an rvtype record, which it then passes to the APEX API apply_mr procedure. The downside to this approach is that each record is processed separately – no bulk updates; however, tabular forms are rarely used to insert or update significant volumes of data anyway so I doubt this would be of practical concern. The advantage of using the rv function is that it means I don’t need to repeat all the column parameters for all my API procedures, making maintenance easier.

The other change that I had to make was ensure that any Hidden columns referred to in my Apply process must be set to Hidden Column (saves state) – in this case, the VERSION_ID column.

Here’s the generated APEX API apply_mr procedure:

PROCEDURE apply_mr (rv IN VENUES$TAPI.rvtype) IS
  r VENUES$TAPI.rowtype;
BEGIN
  log_start('apply_mr');

  UTIL.check_authorization('Operator');

  IF APEX_APPLICATION.g_request = 'MULTI_ROW_DELETE' THEN

    IF v('APEX$ROW_SELECTOR') = 'X' THEN
      VENUES$TAPI.del (rv => rv);
    END IF;

  ELSE

    CASE v('APEX$ROW_STATUS')
    WHEN 'C' THEN

      r := VENUES$TAPI.ins (rv => rv);

    WHEN 'U' THEN

      r := VENUES$TAPI.upd (rv => rv);

    ELSE
      NULL;
    END CASE;

  END IF;

  log_end;
EXCEPTION
  WHEN UTIL.application_error THEN
    log_end('application_error');
    RAISE;
  WHEN OTHERS THEN
    UTIL.log_sqlerrm;
    RAISE;
END apply_mr;

The code uses APEX$ROW_STATUS to determine whether to insert or update each record. If the Delete button was pressed, it checks APEX$ROW_SELECTOR to check that the record had been selected for delete – although it could skip that check since APEX seems to call the procedure for only the selected records anyway. The debug logs show APEX skipping the records that weren’t selected.

Now, before we run off gleefully inserting and updating records we should really think about validating them and reporting any errors to the user in a nice way. The TAPI ins and upd functions do run the validation routine, but they don’t set up UTIL with the mappings so that the APEX errors are registered as we need them to. So, we add a per-record validation in the APEX page that runs this:

VENUES$APEX.val_row (rv =>
  VENUES$TAPI.rv
    (venue_id   => :VENUE_ID
    ,name       => :NAME
    ,version_id => :VERSION_ID
    )
  ,region_static_id => 'venues');
RETURN null;

As for the single-record page, this validation step is of type PL/SQL Function (returning Error Text). Its Execution Scope is the same as for the apply_mr process – For Created and Modified Rows.

Note that we need to set a static ID on the tabular form region (the generator assumes it is the table name in lowercase – e.g. venues – but this can be changed if desired).

The val_row procedure is as follows:

PROCEDURE val_row
  (rv               IN VENUES$TAPI.rvtype
  ,region_static_id IN VARCHAR2
  ) IS
  dummy            VARCHAR2(32767);
  column_alias_map UTIL.str_map;
BEGIN
  log_start('val_row');

  UTIL.pre_val_row
    (label_map        => VENUES$TAPI.label_map
    ,region_static_id => region_static_id
    ,column_alias_map => column_alias_map);

  dummy := VENUES$TAPI.val (rv => rv);

  UTIL.post_val;

  log_end;
EXCEPTION
  WHEN UTIL.application_error THEN
    log_end('application_error');
    RAISE;
  WHEN OTHERS THEN
    UTIL.log_sqlerrm;
    RAISE;
END val_row;

The pre_val_row procedure tells all the validation handlers how to register any error message with APEX_ERROR. In this case, column_alias_map is empty, which causes them to assume that each column name in the tabular form is named the same as the column name on the database. If this default mapping is not correct for a particular column, we can declare the mapping, e.g. column_alias_map('DB_COLUMN_NAME') := 'TABULAR_FORM_COLUMN_NAME';. This way, when the errors are registered with APEX_ERROR they will be shown correctly on the APEX page.

Things got a little complicated when I tried using this approach for a table that didn’t have any surrogate key, where my TAPI uses ROWID instead to uniquely identify a row for update. In this case, I had to change the generated query to include the ROWID, e.g.:

SELECT t.event_type
      ,t.name
      ,t.calendar_css
      ,t.start_date
      ,t.end_date
      ,t.last_updated_dt
      ,t.version_id
      ,t.ROWID AS p_rowid
FROM   event_types t

I found if I didn’t give a different alias for ROWID, the tabular form would not be rendered at runtime as it conflicted with APEX trying to get its own version of ROWID from the query. Note that the P_ROWID must also be set to Hidden Column (saves state). I found it strange that APEX would worry about it because when I removed* the ApplyMRU and ApplyMRD processes, it stopped emitting the ROWID in the frowid_000n hidden items. Anyway, giving it the alias meant that it all worked fine in the end.

* CORRECTION (7/11/2016): Don’t remove the ApplyMRU process, instead mark it with a Condition of “Never” – otherwise APEX will be unable to map errors to the right rows in the tabular form.

The Add Rows button works; also, the Save button correctly calls my TAPI only for inserted and updated records, and shows error messages correctly. I can use APEX’s builtin Tabular Form feature, integrated neatly with my TAPI instead of manipulating the table directly. Mission accomplished.

Source code/download: http://bitbucket.org/jk64/jk64-sample-apex-tapi


APEX API – call a package for all your DML

If you create an APEX form based on a table, APEX automatically creates processes of type Automatic Row Fetch and Automatic Row Processing (DML) as well as one item for each column in the table, each bound to the database column via its Source Type. This design is excellent as it’s fully declarative and is very quick and easy to build a data entry page for all your tables.

The downside to this approach is that if you want to use a Table API (TAPI) to encapsulate all DML activity on your tables, you need to write a whole lot of code to replace the processes that Apex created for you. In order to mitigate this as much as possible, I’ve augmented my code generator with an “APEX API” generator. This generates a second package for each table which can be called from APEX, which in turn calls the TAPI to run the actual DML. In addition, the validations that are performed by the TAPI are translated back into APEX Errors so that they are rendered in much the same way as built-in APEX validations.

Probably the best way to explain this is to show an example. Here’s my EMPS table (same as from my last article):

CREATE TABLE emps
  (emp_id       NUMBER NOT NULL
  ,name         VARCHAR2(100 CHAR) NOT NULL
  ,emp_type     VARCHAR2(20 CHAR) DEFAULT 'SALARIED' NOT NULL
  ,start_date   DATE NOT NULL
  ,end_date     DATE
  ,dummy_ts     TIMESTAMP(6)
  ,dummy_tsz    TIMESTAMP(6) WITH TIME ZONE
  ,life_history CLOB
  ,CONSTRAINT emps_pk PRIMARY KEY ( emp_id )
  ,CONSTRAINT emps_name_uk UNIQUE ( name )
  ,CONSTRAINT emp_type_ck
     CHECK ( emp_type IN ('SALARIED','CONTRACTOR')
  );
CREATE SEQUENCE emp_id_seq;

By the way, my table creation script calls DEPLOY.create_table to do this, which automatically adds my standard audit columns to the table – CREATED_BY, CREATED_DT, LAST_UPDATED_BY, LAST_UPDATED_DT, and VERSION_ID. My script also calls GENERATE.journal for the table which creates a journal table (EMPS$JN) and a trigger (EMPS$TRG) to log all DML activity against the table.

I then call GENERATE.tapi which creates the Table API (EMPS$TAPI) which has routines for validating, inserting, updating and deleting rows (or arrays of rows using bulk binds) of the EMPS table.

Finally, I call GENERATE.apexapi which creates the APEX API (EMPS$APEX) which looks like this:

Package Spec: EMPS$APEX

create or replace PACKAGE EMPS$APEX AS
/**************************************************
 Apex API for emps
 10-FEB-2016 - Generated by SAMPLE
**************************************************/

-- page load process
PROCEDURE load;

-- single-record page validation
PROCEDURE val;

-- page submit process
PROCEDURE process;

END EMPS$APEX;

Notice that these routines require no parameters; the API gets all the data it needs directly from APEX.

Package Body: EMPS$APEX

create or replace PACKAGE BODY EMPS$APEX AS
/*******************************************************************************
Table API for emps
10-FEB-2016 - Generated by SAMPLE
*******************************************************************************/

PROCEDURE apex_set (r IN EMPS$TAPI.rowtype) IS
  p VARCHAR2(10) := 'P' || UTIL.apex_page_id || '_';
BEGIN
  log_start('apex_set');

  sv(p||'EMP_ID',          r.emp_id);
  sv(p||'NAME',            r.name);
  sv(p||'EMP_TYPE',        r.emp_type);
  sd(p||'START_DATE',      r.start_date);
  sd(p||'END_DATE',        r.end_date);
  st(p||'BLA_TSZ',         r.bla_tsz);
  st(p||'DUMMY_TS',        r.dummy_ts);
  sv(p||'CREATED_BY',      r.created_by);
  sd(p||'CREATED_DT',      r.created_dt);
  sv(p||'LAST_UPDATED_BY', r.last_updated_by);
  sd(p||'LAST_UPDATED_DT', r.last_updated_dt);
  sv(p||'VERSION_ID',      r.version_id);

  log_end;
EXCEPTION
  WHEN UTIL.application_error THEN
    log_end('application_error');
    RAISE;
  WHEN OTHERS THEN
    UTIL.log_sqlerrm;
    RAISE;
END apex_set;

FUNCTION apex_get RETURN EMPS$TAPI.rvtype IS
  p  VARCHAR2(10) := 'P' || UTIL.apex_page_id || '_';
  rv EMPS$TAPI.rvtype;
BEGIN
  log_start('apex_get');
  
  rv.emp_id     := nv(p||'EMP_ID');
  rv.name       := v(p||'NAME');
  rv.emp_type   := v(p||'EMP_TYPE');
  rv.start_date := v(p||'START_DATE');
  rv.end_date   := v(p||'END_DATE');
  rv.bla_tsz    := v(p||'BLA_TSZ');
  rv.dummy_ts   := v(p||'DUMMY_TS');
  rv.version_id := nv(p||'VERSION_ID');
    
  log_end;
  RETURN rv;
EXCEPTION
  WHEN UTIL.application_error THEN
    log_end('application_error');
    RAISE;
  WHEN OTHERS THEN
    UTIL.log_sqlerrm;
    RAISE;
END apex_get;

FUNCTION apex_get_pk RETURN EMPS$TAPI.rvtype IS
  p  VARCHAR2(10) := 'P' || UTIL.apex_page_id || '_';
  
  rv EMPS$TAPI.rvtype;
BEGIN
  log_start('apex_get_pk');

  IF APEX_APPLICATION.g_request = 'COPY' THEN

    rv.emp_id := v(p||'COPY_EMP_ID');

  ELSE

    rv.emp_id     := nv(p||'EMP_ID');
    rv.version_id := nv(p||'VERSION_ID');
    
  END IF;

  log_end;
  RETURN rv;
EXCEPTION
  WHEN UTIL.application_error THEN
    log_end('application_error');
    RAISE;
  WHEN OTHERS THEN
    UTIL.log_sqlerrm;
    RAISE;
END apex_get_pk;

/*******************************************************************************
                               PUBLIC INTERFACE
*******************************************************************************/

PROCEDURE load IS
  p  VARCHAR2(10) := 'P' || UTIL.apex_page_id || '_';
  rv EMPS$TAPI.rvtype;
  r  EMPS$TAPI.rowtype;
BEGIN
  log_start('load');

  UTIL.check_authorization('Reporting');

  rv := apex_get_pk;
  r := EMPS$TAPI.get (emp_id => rv.emp_id);

  IF APEX_APPLICATION.g_request = 'COPY' THEN

    r := EMPS$TAPI.copy(r);

  END IF;

  apex_set (r => r);

  log_end;
EXCEPTION
  WHEN UTIL.application_error THEN
    log_end('application_error');
    RAISE;
  WHEN OTHERS THEN
    UTIL.log_sqlerrm;
    RAISE;
END load;

PROCEDURE val IS
  p             VARCHAR2(10) := 'P' || UTIL.apex_page_id || '_';
  rv            EMPS$TAPI.rvtype;
  dummy         VARCHAR2(32767);
  item_name_map UTIL.str_map;
BEGIN
  log_start('val');

  IF APEX_APPLICATION.g_request = 'CREATE'
  OR APEX_APPLICATION.g_request LIKE 'SAVE%' THEN

    rv := apex_get;

    UTIL.pre_val
      (label_map     => EMPS$TAPI.label_map
      ,item_name_map => item_name_map);

    dummy := EMPS$TAPI.val (rv => rv);
    
    UTIL.post_val;

  END IF;

  log_end;
EXCEPTION
  WHEN UTIL.application_error THEN
    log_end('application_error');
    RAISE;
  WHEN OTHERS THEN
    UTIL.log_sqlerrm;
    RAISE;
END val;

PROCEDURE process IS
  p  VARCHAR2(10) := 'P' || UTIL.apex_page_id || '_';
  rv EMPS$TAPI.rvtype;
  r  EMPS$TAPI.rowtype;
BEGIN
  log_start('process');
  
  UTIL.check_authorization('Operator');

  CASE
  WHEN APEX_APPLICATION.g_request = 'CREATE' THEN

    rv := apex_get;
    
    r := EMPS$TAPI.ins (rv => rv);

    apex_set (r => r);

    UTIL.success('Emp created.');

  WHEN APEX_APPLICATION.g_request LIKE 'SAVE%' THEN

    rv := apex_get;

    r := EMPS$TAPI.upd (rv => rv);

    apex_set (r => r);
    UTIL.success('Emp updated.'
      || CASE WHEN APEX_APPLICATION.g_request = 'SAVE_COPY'
         THEN ' Ready to create new emp.'
         END);

  WHEN APEX_APPLICATION.g_request = 'DELETE' THEN

    rv := apex_get_pk;

    EMPS$TAPI.del (rv => rv);

    UTIL.clear_page_cache;

    UTIL.success('Emp deleted.');

  END CASE;

  log_end;
EXCEPTION
  WHEN UTIL.application_error THEN
    log_end('application_error');
    RAISE;
  WHEN OTHERS THEN
    UTIL.log_sqlerrm;
    RAISE;
END process;

END EMPS$APEX;

Now, given the above package, we can create an APEX page that allows users to view, create, update, copy and delete a record from the EMPS table, using all the features provided by our TAPI.

  1. Create Page, select Form, select Form on a Table or view, select the table EMPS.
  2. Accept the defaults, or change them to taste, and click Next, Next.
  3. On the Primary Key wizard step, change type to Select Primary Key Column(s) and it should pick up the EMP_ID column automatically. Click Next.*
  4. For Source Type, leave the default (Existing trigger).** Click Next, Next, Next.
  5. For Branching, enter page numbers as required. Click Next, then Create.

* the APEX API and Table API generator also handles tables with no surrogate key by using ROWID instead; in this case, you would leave the default option selected (Managed by Database (ROWID)) here.
** note however that our TAPI will handle the sequence generation, not a trigger.

The page should look something like this:

Notice that it has created a Fetch Row from EMPS process for when the page is loaded, as well as the Process Row of EMPS and reset page processes for when the page is submitted. It has also created a few validations.

Notice also that all the items are named consistently with the column names; this is important as my APEX API package generator relies on this one-to-one mapping. You can, of course, add additional non-database items to the page – they won’t be affected by the generator unless the table is altered with columns that match.

Now, this page will work fine, except that it bypasses our TAPI. To change the page so that it uses our TAPI instead, edit the page as follows:

  1. Delete all the Fetch Row from EMPS, Process Row of EMPS and reset page processes.
  2. Delete all the validations.
  3. For all the page items, set Source Type to Null. In Apex 5 this is easy – just Ctrl+Click each item, then make the change to all of them in one step!
  4. Make the audit column items (CREATED_BY, CREATED_DT, LAST_UPDATED_BY, LAST_UPDATED_DT) Display Only.
  5. Make the VERSION_ID item Hidden.
  6. Under Pre-Rendering, add an After Header process that calls EMPS$APEX.load;.
  7. In the Page Processing tab, under Validating, add a validation with Type = PL/SQL Function (returning Error Text).
  8. Set the PL/SQL Function Body Returning Error Text to EMPS$APEX.val; RETURN null;.
  9. Set Error Message to “bla” (this is a mandatory field but is never used – I think this is a small bug in Apex 5).
  10. Under Processing, add a process that calls EMPS$APEX.process;.
  11. Set Error Message to #SQLERRM_TEXT#.

Run the page – you should find that it works just as well as before, with all the TAPI goodness working behind the scenes. Even the validations work, and they will point at the right items on the page.

But that’s not all! You can easily add a useful “Copy” function that your users will thank you for because (depending on the use case) it can reduce the amount of typing they have to do.

  1. Add a button to the region, named SAVE_COPY (this name is important) with the label Copy. Tip: if you want an icon set the Icon CSS Classes to fa-copy.
  2. Add a hidden item named after the PK item prefixed with “COPY_”, e.g. P14_COPY_EMP_ID.
  3. Under After Processing, add a Branch that goes to this same page (e.g. 14, in this example).
  4. On the branch, set Request (under Advanced) to COPY and assign &P14_EMP_ID. to the item P14_COPY_EMP_ID.
  5. Set When Button Pressed to SAVE_COPY.
  6. Change the order of the branches so that the Copy branch is evaluated before the other branches (see below)

Now, when they click Copy, the page will first save any changes they had made to the record, then go back to the same page with a copy of all the details from the original record. The user can then edit the new record and Create it if they so desire, or Cancel.

An advantage of this design is that, if you want to add a validation that applies whether someone is updating the table from APEX or from some other UI or interface, you can add it in one place – the TAPI (specifically, you would add it to the TAPI template). If you add a column, just add an item to the APEX page and regenerate the TAPI and Apex API. It’s a nice DRY-compliant solution.

Addendum: you may be wondering why we need a P14_COPY_EMP_ID item, instead of simply reusing the P14_EMP_ID item that’s already there. The reason for this is that after saving a copied record, in some cases we may want to copy some or all the child records from the original record to the copy, or do some other operation that needs both the old and the new ID.

Source code/download: http://bitbucket.org/jk64/jk64-sample-apex-tapi


Detect Empty List

You have a Select List item on your page driven from a dynamic query, e.g. one that only shows valid values. One day, users notice that the list appears empty and raise a defect note.

emptylist.PNG

You check the query behind the list and verify that indeed, the list should be empty because there are no valid values to show. It’s an optional item so the user is free to save the record if they wish.

There are a number of ways we could make this more user-friendly: depending on the specifics of the situation, we might just hide the item, or we might want to show an alternative item or a warning message. We can do any of these things quite easily using either a computation on page load (if the list doesn’t change while the page is open) or a dynamic action.

In the case of my client, they wanted the item to remain on screen, but to show an orange warning message to let them know that there are no gateways currently available; this is only a warning because there are subsequent processes that can handle the missing gateway (e.g. a higher-privileged user can assign a “hidden” gateway to the record if they deem it suitable).

To do this we create a display item (e.g. “P1_NO_GATEWAY_WARNING” which shows the warning message) and a dynamic action with the following attributes:

  • Event = Page Load
  • Condition = JavaScript expression
  • Value = listIsEmpty("P1_GATEWAY_ID")
  • True Action = Set Value
  • Set Type = Static Assignment
  • Value = Warning: no gateways currently available
  • Selection Type = Item(s)
  • Item(s) = P1_NO_GATEWAY_WARNING

In the page’s Function and Global Variable Declaration, or (even better) in the application’s global javascript file, we add the following:

function listIsEmpty(itemName) {
  return $("#" + itemName + " option:enabled").filter(
    function(){return this.text;}
    ).length==0;
}

This was adapted from some solutions here. It looks for all <option>s under the item, filters the list for options which are not disabled and have a label, and returns true if the remaining set is empty. I added the this.text bit because the empty lists generated by Apex include a single empty option for the “NULL” value. This is because I have set the list item’s Null Display Value to blank (null).

emptylistwarning


Auto-convert field to uppercase

This is just a quick note for my future reference. I needed all items with the class “uppercase” to be converted to uppercase, and I thought it would work with just some CSS:

.uppercase { text-transform:uppercase; }

This makes the items appear uppercase, but when the page is posted it actually sends the values exactly as the user typed. They’d type in “lower“, it looks like “LOWER” on screen, but gets posted as “lower“.

In many cases I could just convert the value in my PL/SQL code, but in cases where I was using Apex tabular forms, I don’t know a simple way to intercept the values before the insert occurs.

To solve this I added this to the page’s Execute when Page Loads:

//the item looks uppercase but the internal value
//is still lowercase
$(document).on('change','.uppercase',function(){
  var i = "#" + $(this).attr("id");
  $(i).val( $(i).val().toUpperCase() );
});

Or, even better, add this to the application’s global javascript file:

$(document).ready(function() {
  $(document).on('change','.uppercase',function(){
    var i = "#" + $(this).attr("id");
    $(i).val( $(i).val().toUpperCase() );
  });
});

Disable IE Compatibility Mode

Most places I’ve worked at allow employees to use any of the major browsers to do their work, but mandate an “SOE” that only supports IE, presumably because that generates the most amount of work for us developers. I’d conservatively estimate that 99% of the rendering bugs I’ve had to deal with are only reproducible in IE. (cue one of the thousands of IE joke images… nah, just do a Google Image search, there’s plenty!)

Anyway, we had a number of these rendering issues in Apex on IE8, IE9 and IE10, mainly in edge cases involving some custom CSS or plugins. In some cases I was never able to reproduce the issue until we noticed that the user had inadvertently switched “IE Compatility Mode” on:

iecompatibility1

We told them to make sure the icon was grey, like this:

iecompatibility2

– and most of the issues went away.

Since there’s nothing in our Apex application that requires compatibility mode, we would rather the option not be available at all. To this end, we simply add this code to all the Page templates in the application:

<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">

This is added just after the <head> tag, like this:

iecompatibility3

Now, the compatibility button doesn’t appear at all – one less choice for users and less bug reports to deal with:

iecompatibility5

For more information, see this stackoverflow question and read all the answers. Note that it may be better to add this as a header in the response generated by your web server. In our case it was simpler to just add it into the html.


Deploying APEX: showing an “Under Maintenance” web page

systemdown

I’ve added this script to our toolbelt for future upgrades. We have a friendly “System is under maintenance, sorry for any convenience” web page that we want to show to users while we run upgrades, and we want it to be shown even if we’re just doing some database schema changes.

So I took the script from here and adapted it slightly, here’s our version:

declare PRAGMA AUTONOMOUS_TRANSACTION;
  v_workspace CONSTANT VARCHAR2(100) := 'MYSCHEMA';
  v_workspace_id NUMBER;
begin
  select workspace_id into v_workspace_id
  from apex_workspaces where workspace = v_workspace;
  apex_application_install.set_workspace_id (v_workspace_id);
  apex_util.set_security_group_id
    (p_security_group_id => apex_application_install.get_workspace_id);
  wwv_flow_api.set_flow_status
    (p_flow_id             => 100
    ,p_flow_status         => 'UNAVAILABLE_URL'
    ,p_flow_status_message => 'http://www.example.com/system_unavailable.html'
    );
  commit;
end;
/

It uses an autonomous transaction because we want the system to be unavailable immediately for all users while the deployment is running.

Warning: WWV_FLOW_API is an undocumented package so this is not supported.

The opposite script to make the application available again is:

declare PRAGMA AUTONOMOUS_TRANSACTION;
  v_workspace CONSTANT VARCHAR2(100) := 'MYSCHEMA';
  v_workspace_id NUMBER;
begin
  select workspace_id into v_workspace_id
  from apex_workspaces where workspace = v_workspace;
  apex_application_install.set_workspace_id (v_workspace_id);
  apex_util.set_security_group_id
    (p_security_group_id => apex_application_install.get_workspace_id);
  wwv_flow_api.set_flow_status
    (p_flow_id             => 100
    ,p_flow_status         => 'AVAILABLE'
    );
  commit;
end;
/

However, if we run the f100.sql script to deploy a new version of the application, we don’t need to run the “set available” script since the redeployment of the application (which would have been exported in an “available” state already) will effectively make it available straight away.


Tabular Form – set default values with jQuery

tabular-form-default-jquery4

I have a simple tabular form with a numeric “sort order” column. I want the value of this column to be defaulted automatically, based on the maximum value of the rest of the records on the screen. Unfortunately the builtin Apex default type for columns in a tabular form can only be based on an Item, or a PL/SQL expression or function. I didn’t want to make a database call to get the maximum value because the user may have added multiple records which have not yet been saved to the database.

I tried basing the default on a hidden page item which I kept updated based on the values entered, but it seems the tabular form only gets the item’s value on page load and doesn’t re-examine the item’s value when you click the “Add” button. Instead, I had to turn to javascript and jQuery to get the job done.

1. Add a class to the tabular form column by setting the Element CSS Classes column attribute (I’ve used “sortorder” as the class name).

tabular-form-default-jquery2

2. Add a javascript function to the page that searches for all the “sortorder” items, calculates the maximum value and adds 10, and assigns it to the last sortorder item on the page.

function setNextSortOrder() {
  var highest=0;
  $(".sortorder").each(function(){
    if (this.value != "") {
      highest=Math.max(highest,parseFloat(this.value));
    }
  });
  $(".sortorder").last().val(highest+10);
}

tabular-form-default-jquery1

3. Modify the javascript for the “Add” button to call the setNextSortOrder function immediately after adding a row.

javascript:{apex.widget.tabular.addRow();setNextSortOrder();}

tabular-form-default-jquery3

It’s a bit hackish and might not work correctly in some cases but it’ll do for now.