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

1
2
3
4
5
6
7
8
9
10
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.

1
2
3
4
5
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.

1
2
3
4
5
6
7
8
9
10
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).


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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. 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).

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

Oracle Developer Choice Awards - 1 Week Left to Nominate

Oracle has launched its inaugural Developer Choice Awards. The deadline to nominate someone has been extended to August 21st so take advantage of this extension and nominate someone you think deserves recognition for the work they do with Oracle!

A description of the competition is below and you can find out more by going to the main site.

The Oracle Database Developer Choice Awards celebrate and recognize technical expertise and contributions in the Oracle Database community. As longtime and new users of Oracle Database move to the Cloud and take advantage of this exciting new architecture, community experts will play a critical role in helping them succeed.

For 2015, awards will be given out in the following technology areas:

  • SQL
  • PL/SQL
  • Oracle REST Data Services
  • Oracle Application Express
  • Database Design

We encourage you to nominate a developer you believe is deserving of this award. We are accepting nominations through August 21. A panel of judges, composed of Oracle ACEs and Oracle employees, will then choose a set of finalists.

But who chooses the winners? You do! The worldwide Oracle technologist community votes on the finalists from September 15 through October 15.

The winners of the Oracle Database Developer Choice Awards will be announced at the YesSQL! Celebration during Oracle OpenWorld 2015.

This is your chance to shine and earn highly deserved recognition for yourself or your respected peers.

APEX Meetup - Montreal this Thursday!

Note: The location for the Meetup has moved. Please check the meetup page for the new location.

For those in Montreal, I’ll be presenting at the local #orclapex meetup this Thursday, August 13, evening from 5-7. All the information can be found on the meetup page.

Top 10 APEX API

APEX has a lot of excellent APIs. These APIs, for both PL/SQL and JavaScript, can help extend your application and improve your development. This presentation will cover some of the most useful APIs (both documented and undocumented) that are available.

The presentation will be in English however I can take questions in both French and English.

I look forward to meeting the Montreal APEX community!

– Martin

APEX and the Order Items are Submitted

This is the last post in a multi-part series on how APEX submits and processes input elements from your browser to the server. The goal is to understand the effects of moving elements around the page. It is important to read these articles in the following order.

The goal of this article is to highlight the effects of moving items and regions around on a page. This article will be broken into four sections, a high level recap from the previous two articles, moving an APEX item, moving an APEX item along with its corresponding p_arg_names element, and finally a conclusion. It will reference and build upon the example from the previous article.

Recap

I can’t stress enough how important it is to read the previous two articles. To recap, when APEX submits the page it’ll use the following values to map the HTML elements to their corresponding APEX items.

APEX Item ID APEX Item Element Name
p_arg_names[1] P1_FIRST_NAME p_t01
p_arg_names[2] P1_LAST_NAME p_t02

Moving APEX items

They’re some times where it is required to move APEX items around the page after it has rendered. This example will highlight what happens when an item (and only the item) is moved.
Using the example from the previous article suppose you move P1_LAST_NAME before P1_FIRST_NAME using jQuery.

$(‘#P1_LAST_NAME’).insertBefore(‘#P1_FIRST_NAME’);

The page will look like:


When the page is submitted, everything still works as expected. P1_FIRST_NAME = Martin and P2_LAST_NAME = D'Souza. In this case, it was safe to the move the item around the page since only the p_t02 element was moved. The order of p_arg_names (which APEX uses to map to apex_application_page_items.item_id an p_txx) was not changed.

### Moving an APEX item along with its corresponding p_arg_names element.
Using the previous example, suppose the items were split up into two regions as shown below. To help out, each region has been assigned a static id of region-one and region-two respectively.


Using the following code, Region Two is moved above Region One:

1
$('#region-two').insertBefore('#region-one');


The screen then looks like:

Referring to the previous article not only has the input element for P1_LAST_NAME moved, but it’s corresponding p_arg_names hidden element was moved and its overall order has changed. Now when the page is submitted the following is sent to the server (note the order):

1
2
3
4
5
p_arg_names[1] = 32629863123858907 - Maps to APEX item P1_LAST_NAME
p_arg_names[2] = 32629789701858906 - Maps to APEX item P1_FIRST_NAME
p_t01 = Martin
p_t02 = D'Souza

Just to recap and highlight what is about to happen:

p_arg_names[1] (P1_LAST_NAME) maps to p_t01 (Martin)
p_arg_names[2] (P1_FIRST_NAME) maps to p_t02 (D'Souza)

You can see the mis-match occur below once the page has been submitted. Martin is stored in P1_LAST_NAME and D'Souza is stored in P1_FIRST_NAME.

Conclusion

Be very careful when moving APEX items around the page. As a guideline, it’s usually safe to move individual items, provided you’re not moving any p_arg_names hidden elements. If you’re moving a region and/or any p_arg_names elements you may get invalid data assignments if the order of p_arg_names is changed.

This issue was first discussed a very long time ago between myself and Dan McGhan on the Oracle Forums: https://community.oracle.com/message/3182532

APEX and the HTML Form

This is the second post in a multi-part series on how APEX submits and processes input elements from your browser to the server. The goal is to understand the effects of moving elements around the page. It is important to read these articles in the following order.

The goal of this article is to highlight how APEX actually processes the data that is sent data from the browser to the server when a submit button is pressed.

Suppose you have a simple page with two elements: P1_FIRST_NAME and P1_LAST_NAME as shown below:

Stripping out a lot code, the underlying HTML code for this page looks like:

1
2
3
4
5
6
7
<form action="wwv_flow.accept" id="wwvFlowForm" method="post" name="wwv_flow">
<input name="p_arg_names" type="hidden" value="32629789701858906" />
<input id="P1_FIRST_NAME" name="p_t01" type="text" value="" />
<input name="p_arg_names" type="hidden" value="32629863123858907" />
<input id="P1_LAST_NAME" name="p_t02" type="text" value="" />
</form>

You’ll notice that despite their being four input elements they’re really only three unique sets of names that are used: p_arg_names, p_t01, and p_t02. When the page is submitted the web server (APEX) will get/processes the following elements:

1
2
3
4
5
p_arg_names[1] = 32629789701858906
p_arg_names[2] = 32629863123858907
p_t01 = Martin
p_t02 = D'Souza

None of the data sent back to the server make reference of P1_FIRST_NAME or P2_LAST_NAME. As well, p_arg_names is stored in a top down order of how they are in the page.

p_arg_names values are actually the IDs for each of the of the page items as highlighted in the following query:

1
2
3
4
5
6
7
8
9
10
select item_id, item_name
from apex_application_page_items
where 1=1
and application_id = 118
and page_id = 1;
ITEM_ID ITEM_NAME
----------------- -------------
32629789701858906 P1_FIRST_NAME
32629863123858907 P1_LAST_NAME

APEX is able to map the data back to their corresponding APEX items by first mapping the values in p_arg_names to the apex_application_page_items view and then using the values in the p_txx to retrieve the data that was submitted for each item.

The order that the values are submitted in for p_arg_names must match the p_txx values for APEX to correctly map the values to their appropriate APEX item. I.e. p_args_names[1] will link to p_t01 and p_arg_names[2] will link to p_t02 etc.

Back to Basics: The HTML Form

This is the first post in a multi-part series on how APEX submits and processes input elements from your browser to the server. The goal is to understand the effects of moving elements around the page. It is important to read these articles in the following order.

As the above note suggests, the next few articles will cover how APEX submits and processes input elements in a page. This article is specifically focused on the basics: HTML forms. It’s important to understand how the HTML form tag works and posts input elements. They’re other articles that cover this in much more detail and this article will only cover the high level concepts.

Any web page that submits data to it has the following structure:

1
2
3
4
5
<form action="submitURL" method="post">
First name: <input id="foo" name="firstname" type="text" />
Last Name: <input id="bar" name="lastname" type="text" />
<input type="submit" />
</form>

This will produce a page that looks like:

When the page is submitted the following is sent to the server:

1
2
firstname = Martin
lastname = D'Souza

Despite what most developers think, the element’s IDs (in this case foo and bar) are not submitted to the server. The server never sees/knows about them. Instead it must use the element’s name attribute.

HTML form behavior has another nice feature that allows for the same name to be used multiple times. Modifying the previous example, the next example will use the name person for both the first and last name elements:

1
2
3
4
5
<form action="submitURL" method="post">
First name: <input id="foo" name="person" type="text" />
Last Name: <input id="bar" name="person" type="text" />
<input type="submit" />
</form>

Now when submitting the page the following is sent to the server:

1
2
person[1] = Martin
person[2] = D'Souza

The server processes the person attribute as an array of data. Using pseudo code, this is how a web server scripting language may process the data:

1
2
3
4
...
firstName = htmlForm.person[1];
lastName = htmlForm.person[2];
...

The two key items to take away from this article are:

  • When elements are submitted, only the name attribute is sent to the web server and is used to reference the element.
  • A form can contain multiple elements with the same name. In this case the web server will view them as an array of data.

A New Chapter

I’m please to announce that starting in July I will be joining Insum Solutions as a Senior Technical Consultant! I’m really excited to be joining their team which specializes in Oracle and APEX solutions.

A bit about Insum:

Insum is a highly creative solution center specialized in Oracle Database, developing innovative applications for clients and partners in North America. Founded in 2002, Insum has been assisting customers realize the full benefits of Oracle Database with Application Express since 2005. In fact, Insum is a pioneer with APEX and is today the largest consultancy specialized in Oracle APEX developments in North America. Privately held with offices in Montreal, Canada and South Burlington, USA, Insum has the most complete and experienced staff to serve the needs for complex application development and deployments in a wide range of industries. Come discover our unique APEX center of excellence

– Martin

APEX Meetups - Calgary

One of the popular things these days in the APEX community is to have localized APEX Meetups. These are usually monthly meetings where one, or a few, people give a presentation about APEX. These are a lot more informal than traditional conferences and help the community grow in a given area.

I recently launched the Calgary APEX Meetup group: ORCLAPEX-YYC. If you live in or around Calgary and are interested in getting together once a month to hear more about APEX join this group! The plan is to host our first Meetup in early June and are currently looking for a venue to host us. If you can acquire some space please leave a comment on the discussion board.

We’re looking to keep these events free and are always looking for sponsors to help fund some of the small costs. If you or your organization is interested please leave a comment on the discussion board.

SQLcl and login.sql

In SQL*Plus you can configure it to automatically run login scripts, typically used to configure your session. These are stored in the files glogin.sql and login.sql. Some examples that you’d find in these files are statements like: set serveroutput on. You can read more about login scripts here.

SQLcl also handles login scripts, however it may require some additional configuration. The easiest way to leverage this is to create a file called login.sql in your current directory, then call SQLcl from that directory.

If you’re like me and launch SQLcl from many directories that approach won’t work. Instead, you can create one login.sql file and have it automatically referenced. To do this create login.sql in a folder (in my case it was Documents/Oracle) and then add the following to ~/.bashprofile: export SQLPATH=~/Documents/Oracle/. For those that use oh-my-zsh modify ~/.zshrc instead.

Now when you run SQLcl anywhere it will reference your login.sql file.

Thanks to Kris Rice and Jeff Smith for helping with this.