1st Calgary APEX Meetup

We’re having our 1st inaugural APEX meetup on Thursday Nov 12th! I’ll be giving a presentation about APEX 5 and the new apex.world site.
FAQs I’ve received about this meetup:

  • Does it cost anything? No, it’s entirely free
  • What should I bring? Yourself. Some people like to bring their laptop to try things out at the same time.
  • Is this a sales pitch: No, it’s by the APEX community, for the APEX community.
  • Will only APEX items be covered? Primarily we’ll be talking about APEX but may deviate a bit in the future to cover some SQL, PL/SQL, and web technologies. These topics are all relevant for APEX developers.
  • Where can I get one of the cool APEX stickers I see everyone has on Twitter? At the meetup. I’ve got a whole stack to give away.

Location details:
Trident Limited Partnership 1000, 444 - 7 Avenue SW (Barclay building) Calgary, Alberta T2P 0X8
10th floor – Must sign-in at reception desk

Special thanks to Jean Paradis and Trident Limited Partnership for securing the meeting space!

Please RSVP to let us know if you’re coming.

APEX Podcast

If you can’t get enough APEX there’s now an APEX Podcast! This is a great opportunity to know more about the people behind APEX and some key people in the APEX community.

Juergen Schuster and I co-host the show (I only joined on recently) and it has been a lot of fun so far.

You can download each episode on the official site (apex.press/talkshow) or subscribe on iTunes.

Note: In the latest podcast we mentioned that we’re looking for sponsors for the show. If you or your organization is interested in the opportunity please email me.

OOW 15 Presentations

If you’re attending Oracle OpenWorld 2015 this year here are a few links for presentations I’d recommend:

On Tuesday night there will be the APEX meetup. Don’t get their too late since it fills up pretty quickly. We’ll be in the basement.

I’ll also be filming some Periscope interviews for ODTUG. If you can’t attend OOW then you’ll want to follow the ODTUG twitter account. This will be the first time ODTUG has ever done this and we’d appreciate your feedback. If there’s someone you’d like to see interviewed during the conference please let me know below by leaving a comment on this post.

I look forward to seeing everyone there!

ODTUG 2015 Election - Please Vote

The ODTUG Board of Directors election is now officially open. Paid ODTUG members can cast their ballot between Oct 6th - Oct 27th. If you are a paid member you will get an email with the link, username, and voter id.

I am running for the ODTUG Board of Directors and I am looking for your support to continue to help the ODTUG community for another two years.

Here is my official campaign statement:

Over the past four years, I have had the privilege to represent the Oracle APEX Community as a Director for ODTUG. I have made the decision to run again this year for the ODTUG Board and am looking for your support in doing so.

During my four-year tenure, I have been involved in several committees within ODTUG that have helped bring conferences around the world, improved marketing initiatives, and organized developer contests.

I have been able to build strong relationships with other ODTUG Board Members, Oracle employees, and many developers in the APEX community. These relationships have allowed me to be a liaison between all three groups and help relay ideas and opportunities to the appropriate parties. I hope to continue to be able to facilitate open communication over the next two years.

I am looking for your vote to continue as an ODTUG Board Member to represent developers within one of the world’s largest Oracle user groups. Thank you in advance for your support.

Thank you in advance,

Martin

APEX Webinar: From the Community, for the Community

ODTUG will be hosting an APEX webinar on Monday, Oct 19th at 12:00pm EDT. The webinar is about something that several key members of the APEX community have been working on that is “from the community, for the community“. I strongly recommend that everyone attend this webinar.

I can’t say anything more than the above statement and all will be revealed in the webinar. Registration is free, and you can register using this link.

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:

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

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

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.