APEX OOW 2015 Meetup

Each year the Oracle APEX community gets together at Oracle Open World (OOW) to have a few beers and catch up. This is a great informal event and its your chance to network with a lot of people in the APEX community and meet some of the gurus and members of the core APEX development team.

We'll be having the annual APEX OOW Meetup at Johnny Foleys on Tuesday, October 27th at 7:30 onwards. We've been their a few times now and it is a really fun night since they have duelling pianos later on in the evening. Don't show up too late or it may be tough to get in.

243 O'Farrell Street
San Francisco, CA 94102

ODTUG will also be giving away some cool APEX swag!

Looking forward to seeing everyone there!

Martin

Custom Code for Tabular Forms (Part 2)

The previous article covered how to create a Tabular Form with manual code rather than automatic row processing. This article will demonstrate how to change the Tabular Form to modify data from multiple tables using the same Tabular Form.

Modify Tabular Form

Using the example from the previous article, modify the Tabular Form and change the query to:

select
  e.empno,
  e.empno empno_display,
  e.ename,
  e.sal,
  d.dname
from emp e, dept d
where 1=1
  and e.deptno = d.deptno

Edit DNAME

Edit the newly added DNAME column with the following changes:

Modify Page process

Change the page process to use the code snippet below. Note: This isn't the best example, as the below code will update the department name for each modified employee record. It does highlight is that you can reference and modify data from multiple tables.

if :empno is null then
  -- code to insert emp
  null;
else
  update emp
  set
    ename = :ename,
    sal = :sal
  where empno = :empno;

  -- Update dept name
  update dept d
  set dname = :dname
  where 1=1
    and d.deptno = (
      select e.deptno
      from emp e
      where e.empno = :empno)
end if;

If you've ever developed a true manual tabular form using collections, the above approach covered in this article may be a better alternative to manage and maintain.

Custom Code for Tabular Forms (Part 1)

Over a year ago I wrote an article covering how to create a tabular form and then use custom PL/SQL to process the data rather than the automatic Apply MRU and Apply MRD processes. The demo showed how to do this in APEX 4.2. This article will re-introduce the topic but use APEX 5.0 instead.

Create new Tabular Form

• Create page > Page type: Form > Tabular Form
• Select the following options: 

• Note: For simplicity/demo purposes, limiting to just the SAL and ENAME columns.
• Primary Key: Select Primary Key Column(s) > Primary Key Column 1 > EMPNO
• Run through the rest of the wizard.

Remove Automatic DML

Since custom code will be used to process the page, delete the automatic row processing process as shown below.

Create Process

Create a new process with the following settings (the Source is included below the image).

if :empno is null then
  -- code to insert emp
  null;
else
  update emp
  set
    ename = :ename,
    sal = :sal
  where empno = :empno;
end if;

Using the above technique you can now use a tabular form to call custom PL/SQL code rather than the automatic row processing.

The next article will cover how to modify data from multiple tables in the same Tabular Form.

Report with Checkboxes (an update)

Supposed you have a report with checkboxes. Once the user selects all the rows, they can submit the page and the application would process the rows. Sounds pretty simple and straight forward however they're some additional requirements:

- The report is an Interactive Report
- There may be up to 10,000 records in the report
- When the user "scrolls" through the report (i.e. uses pagination), if they checked off a box it should remain checked the entire time (i.e. if they check an row in the 1st 15 rows, then view rows 16~30, then go back to rows 1~15 it should remain checked). Same applies if a user uses the IR filters.

If you follow this blog and the above sounds familiar, that's because it is. I wrote about this problem a very long time ago. A lot has changed since 2009 and an update on the original post is long overdue. Here's the updated (and simplified) solution.

Create Item to Hold List of IDs

Create a hidden APEX item which will contain a comma delimited list of all the IDs that are to be checked off (in this example it will be P1_EMPNO_LIST). Be sure to modify the Value Protected attribute to No. This is critical as this item will be updated via AJAX and can not have any hashing/security applied to it.

If you are loading this from a cross reference table you can use the following query in a Before Header process. This query will load all the employees from the Accounting department.

select listagg(e.empno, ',') within group (order by e.empno)
from dept d, emp e
where 1=1
  and e.deptno = d.deptno
  and d.dname = 'ACCOUNTING'

Create IR Report with Checkboxes

Create an IR with the query below. Note the p_attributes value. This is critical as we need to identify the checkboxes that should be monitored.

select 
  apex_item.checkbox2(
    p_idx => 1,
    p_value => e.empno ,
    p_attributes => 'class="empno"',
    p_checked_values => :p1_empno_list,
    p_checked_values_delimiter => ',') checkbox,
  e.ename, 
  e.job
from emp e

In the report attributes set the Page Items to Submit to P1_EMPNO_LIST. Each time the report is refreshed (pagination, filters, sorting, etc) the active list of selected values will be submitted.

Create Dynamic Action (DA)

Create a new DA with the attributes in the below image. This DA will append/remove the comma delimited list of IDs in P1_EMPNO_LIST. Note the jQuery Selector value must match what was used in the IR query above.

Configure a True action as shown below (JS code follows).

var
  //Checkbox that was changed
  $checkBox = $(this.triggeringElement),
  //DOM object for APEX Item that holds list.
  apexItemIDList = apex.item(this.affectedElements.get(0)),
  //Convert comma list into an array or blank array
  //Note: Not sure about the "?" syntax see: http://www.talkapex.com/2009/07/javascript-if-else.html
  ids = apexItemIDList.getValue().length === 0 ? [] : apexItemIDList.getValue().split(','),
  //Index of current ID. If it's not in array, value will be -1
  idIndex = ids.indexOf($checkBox.val())
;

//If box is checked and it doesn't already exist in list
if ($checkBox.is(':checked') && idIndex < 0) {
  ids.push($checkBox.val());
}
//If box is unchecked and it exists in list
else if (!$checkBox.is(':checked') && idIndex >= 0){
  ids.splice(idIndex, 1);
}

//Convert array back to comma delimited list
apexItemIDList.setValue(ids.join(','));

Demo

That's all that's required. Now each time a checkbox is checked/unchecked P1_EMPNO_LIST will be updated to reflect these changes. The checkboxes will persist each time the report is refreshed. You can see the checkbox implantation in this demo.

Considerations

This solution is fairly simple to create an manage however it does have one small caveat. If the list of checked items is very large (more than 4000 characters) you may run into some varchar2 issues. In most cases this shouldn't be an issue but if it is you should test first.

To process the list of comma delimited list you can use the apex_util.string_to_table function and loop over the table values. If you want to use the comma delimited list in a query the following example should work (again it does have varchar2 size limitations).

select regexp_substr(:p1_empno_list,'[^,]+', 1, level) empno
from dual
connect by regexp_substr(:p1_empno_list, '[^,]+', 1, level) is not null