ODTUG Technical Journal ~ Into the Sunset

It is with mixed emotions that I formally announce the retirement of the ODTUG Technical Journal … and along with it the ODTUG Editor’s Choice Award, since, well, there will be no Editor.


ODTUG Technical Journal, now Retired

Times have changed, significantly, since I first volunteered for the Technical Journal in 2006 – writing book reviews, then as Associate Technical Editor, and finally as Editor.  This is a span of almost 10 yrs.  To put things in perspective, 10 years ago:

  • The top 5 cell phones were NOT smartphones (remember Nokia and Motorola flip phones?)
  • Smartphone options included Blackberry and Palm Treo
  • Twitter was brand new
  • Blogging was just gaining in popularity
  • LinkedIn existed but had not taken off yet (launched in 2003)
  • YouTube was launched, starting video blogging
  • The Kindle did not exist (introduced in 2007)
  • The iPhone did not exist (debuted in 2007)
  • The iPad did not exist (introduced in 2010)
  • The ODTUG listservs were a popular, excellent way of exchanging questions, answers and advice. An actual ODTUG listserv question from September 2005:
We are considering using Oracle's HTML DB to do some web development. Does
anyone who has experience in using this, have time to spare to make some 
comments about it?  I am particularly interested if anyone knows from where 
inside of Oracle this came from and if it is likely to have a long shelf life.

(see http://www.freelists.org/post/oracle-l/HTML-DB-here-to-stay for a trip down memory lane).

Well, we know what happened with all of the above.  Technology has changed – advanced. Rapidly.

Accordingly, how we Oracle Developers work has changed. How we communicate has changed. How we learn has changed. How we find and consume learning and reference materials has changed from hard-copy, book-based to online, blog and video-based.

The ODTUG Technical Journal served its purpose through the years, providing Oracle developer tools white papers, book reviews, volunteer features, conference highlights and more. The Technical Journal transitioned from quarterly paper editions to 100% online bi-weekly releases at the end of 2012.  The online Technical Journal Corner enabled publication of material of all media types – blog, video, audio as well as traditional white paper format.

Quite simply, it is no longer practical to consistently consolidate the best-of-the-best materials on a single journal or website in a manner that is searchable and accessible to all Oracle developers.  Static web pages have been superseded by blog aggregators and twitter streams. We at ODTUG prefer to stay ahead of the curve in serving our members, and thus our final Technical Journal Corner article will be posted this month, and as of May 1st the ODTUG Technical Journal Corner will be retired. All postings there will stay ~ just no new material added.

The last post will be by Nikos Karagiannidis, SQL Tuning for Day-to-Day Data Warehouse Support, posting in the coming week.

I wish to thank and applaud all those who have contributed to the ODTUG Technical Journal through the years.  I have enjoyed working with each and every one of you ~ far too many individual authors to list here ~ but I thank you all for your contributions.

In particular I would like to thank our long-term dedicated columnists who served for multiple years since I have been editor:  Joe Begenwald, who even delivered his Ask the Experts column when he could not see, Neelesh Shah who contributed years worth of book reviews, Lucas Jellema who taught us all much about Oracle Fusion Middleware, Mark Rittman, star of BI Perspectives, Kevin McGinley for stepping on the BI column, Steven Feuerstein for his unique Confessions of a Quick and Dirty Programmer, Ed Roske and Tim Tow for the Look Smarter Than You Are with Hyperion column, and John King for a wealth of Volunteer Spotlights and overall general encouragement.  Many thanks to Donna Richey-Winkelman and Maggie Tompkins, Editors Emeritus, for setting the bar high as previous editors of the ODTUG Technical Journal.  Finally, I would like to thank Peter Koletzke for teaching me by example how to be a better editor. It has been a privilege working with you all.

So what’s next?  Watch the ODTUG Communities: APEX, Business Intelligence, ADF, Database, EPM, and Career Track where you will find blog aggregators, twitter handles, a Calendar of Events more. Sign up for ODTUG Webinars, free learning from ODTUG experts. Better yet, volunteer! ODTUG is a volunteer organization serving Oracle developers around the world. We need you!

At this time we will also be retiring the ODTUG Editor’s Choice award, traditionally awarded at our annual Kscope conference for excellence in communication of a technical topic.

We at ODTUG still wish to honor excellence in the Oracle Tools development world. Accordingly, I am pleased to announce the inaugural ODTUG Oracle Developer Innovation and Excellence Award, designed to honor innovation, excellence and amazing accomplishment in the realm of Oracle Development Tools adoption and integration.  Watch for more information on this new award – complete details will be released in the coming weeks.

As always, happy coding,

and keep your eye out for Awesome Extraordinary, Innovtive Stuff ~ More Soon!


Editor Emeritus, ODTUG Technical Journal


Off to the Islands, where all retired Editors go

APEX 5 Data Load Wizard ~ Slight Updates Mean Changes to Customized Load Pages

Slight changes in the APEX 5 Data Load Wizard meant our customized Data Load pages needed some attention to work properly in APEX 5.

Ever customize the APEX Data Load Wizard pages?  We did so in APEX 4.2,  to

  • Accomodate data change logging requirements. We needed to log every change made to incoming data.  The APEX Data Load Wizard will make those changes for you, but not log them.
  • Allow users to upload XLSX, XLS or CSV files.

So we customized. And all worked well, until we upgraded to APEX 5.

In APEX 5, all our data uploads all broke, giving an abrupt “no data found” error during the file upload.  Geez, it hadn’t even got to all our custom stuff yet!

Well, a little digging into the APEX 5 Data Load Wizard-generated pages and collections gave me the answer. And we are back in business.

This is good reminder that any time you play outside the APEX sandbox – use anything other than the standard wizards and documented APIs –  you are subject to things breaking upon upgrade. We know this and accept it … and we really should PLAN for it. Even point releases.

So what changed?

The Data Load Wizard uses a series of collections behind the scenes to store the data being uploaded and processed:

  • SPREADSHEET_CONTENT – Uploaded (or copied) data
  • PARSE_COL_HEAD – As-uploaded column headings
  • LOAD_CONTENT – Content to be loaded, plus Error and Action information in c047, c048 and c049.
  • NOT_LOAD_CONTENT – Content to NOT load, including the same Error and Action information in c047, c048 and c049.
  • LOAD_COL_HEAD – Mapped column headings; the columns to be loaded.

The collection names pretty much tell you what each collection does.

The APEX 5 DLW now also creates a new collection:

  • SPREADSHEET_SMTP – One member, the uploaded spreadsheet sheet name.  (Not sure what happens if there are multiple sheets … task for another day.)

Was this the problem?  No.  This new collection was getting created in APEX 5 – clearly not the problem.

What was? Behind the scenes, the APEX 5 DLW added a new element as  the last element in the PARSE_COL_HEAD collection, USE_APPLICATION_DATE_FORMAT.  As soon as I added this element to my collections, all worked happy again.

How did I find it?  Plain old triage. I knew from experience that upon upload the SPREADSHEET_CONTENT and PARSE_COL_HEAD columns got populated. Since we did not get beyond the upload- clearly something was happening  – missing – there.  I created some APEX 5 data load wizard pages based on the same table, and step by step examined what was there or not in session state.  Our customization populated PARSE_COL_HEAD (for a variety of reasons re saving the uploaded file, off point for this post).  But it was not adding the USE_APPLICATION_DATE_FORMAT element.  A simple PL/SQL change corrected that, and the no-data-found went away.

Note that this solved our APEX 5 data load issue.  I have seen other cases of the same no-data-found message on other posts in the APEX Community and stackoverflow.com – these may or may not be the same issue I describe here, and the pre-APEX 5 posts are definitely not. Suffice it to say if one customizes any part of the APEX Data Load Wizard, one adopts  the responsibility of keeping up with all of the nuances of the Data Load Wizard for the life of that application.

Is there something else different behind the scenes?  Yes, a few things off topic for this post.  Is there more? Could be, but it is not effecting my customized data load  pages right now.  Will it break on the next upgrade?  Maybe, and we will of course be watching, testing and prepared to adjust as needed.

Lessons reinforced:  Customize only when necessary.  Plan for, allocate time for, test, and adjust as necessary before every upgrade. Common sense stuff that sometimes gets back-seated in the haste to upgrade.

Migrate BI to APEX 5?

Just back from presenting Migrating BI to APEX 5 at RMOUG Training Days 2016.  Great conference!  Great experts on all Oracle technology. If you have to pick one short sweet conference a year, this is it.

Ever thought of replacing your under-utilized, expensive or outdated BI tool with a suite of APEX Interactive Reports?  In *SOME* cases this makes sense:

  • Your Data Display requirements are reasonable – i.e. users are happy with seeing and working with a few thousand rows or less at a time.
  • Your BI feature requirements include APEX IR Actions (select, sort, filter, chart, pivot, group by aggregate, save, download), and
  • Your BI feature requirements do NOT include BI analysis, unlimited drill-downs, automatic or integrated analysis (such as percents, rank, etc), drag and drop report creation/analysis, and/or MS-Excel-like features. IF you want these, you have to build them.
  • You have APEX resources to plan, design, build and maintain the APEX app
  • You have Database Architects/Developers to plan, design, build and manage the data structures in the database required to consolidate and serve your data ( materialized views, indexes, or other rollups as needed as licensed for).
  • You are prepared to design, build and tune all for performance (as you always should anyways!)
  • You do NOT require out-of-the-box PDF Printing for the as-displayed data set.  Those who own and extensively use BI Publisher may not want to give up this printing luxury IF moving to APEX means doing so. There are many many printing uses cases for which there are many solutions – ensure you have a solution that fits (and is reasonable to implement) before you jump.

Given all this, it just might work for you  It has for many customers, successfully.  The caveats are:

  • If you need a feature that APEX does not natively supply, you must build it.
  • Dynamic Actions are great -but need to be implemented wisely. They may not always perform well with volumes of data.
    • ex: Dynamic parameter selection => Good.
    • Dynamic parameter selection AND dynamic refresh of the IR with the new parameters (a new query against the source data structure) may/will not perform so nicely, depending on the volume of data.
  • Some features are just not reasonable to build in APEX:
    • ex: True Excel-Like behavior
    •        Dynamic Aggregate adjustments

So – want to replace that aging Discoverer installation?  Consider APEX 5.

In planning pages, menus and features, consider these tips:

  • Plan and meticulously tune all structures – materialized views, CUBEs or ROLLUPS, or whatever works for your data. Anything slow here is magnified slow in APEX.
  • Give users mandatory parameters.  This forces up-front filtering, to reduce the result set to a reasonable size AND gives you a ready-made drill path.  We want reasonable size for reasonable performance of all interactive features.
  • Try 3 to 5 Parameters – More gets tedious for end users if they have to select every time.
    • A Temporal (Year. Quarter, Month), and Spatial ( Country, State, City ) and one or few others specific to your data.
    • Consider using Page Zero or building  a Plugin for displaying the same parameter set on all or several pages. (Yes, Plug-Ins can be used locally!)
  • Use a flag to control when data displays. This allows users to filter (using your parmeters Plugin) first, then wait for data to display.  Plan so users do not incur a big wait right up front.
  • Consider multiple IRs on one page, each containing different aggregates of you data depending on the parameters.  The parameters chosen control which ONE of thes IRs displays at any given time.
  • Consider multiple IRs on one page, each containing different Action Item features.  Authorizations for authenticated users control which ONE of thes IRs displays at any given time.
  • Plan and build drill paths wisely.  Intelligently build useful drill paths.  A drill on every column is not necessary. Drills on key columns are nice to have.

Know Your Users, and know what they really do with the data.  That helps you to design and build truly useful data sets and features.

Still not convinced? On the Fence ?

Consider waiting for APEX 5.1 Interactive Grid.  Previews of this new region type show some promising features that BI folks may find interesting:

  • Drag and drop headings
  • Lazy loading – an option to display the frame then the rest of the data
  • Loading data in pages, as opposed to one big result set.

Doesn’t seem like much, but these are big useability improvements , especially when we are considering paging through volumes of data.  Were I to be starting a BI migration project now,  I would investigate the APEX 5.1 Interactive Grid previews and plan my project accordingly.

In the meantime, see my Migrating tBI to APEX 5 slides here, and  if you have specific questions, reach out:

APEX Interactive Reports Part I: – New Features and Enhancements

The purpose of this post series is to help readers learn about APEX 5 IRs, in two parts:
• Part 1 – APEX 5 Interactive Report new and enhanced Action Menu and support features.
• Part 2 – APEX 5 Interactive Report CSS and JavaScript changes, and how these changes impact existing APEX Interactive Reports.
• Part 3 – APEX 5 Interactive Report Settings and Performance – Learn how declarative IR settings manifest in SQL query statement changes that may significantly influence performance. This post, inspired by PEter Raganitch’s KScope 2015 presentation, remoinds developers to be aware of the impact of their selections.

Introduced in APEX 3.2, IRs have become the default report option for APEX applications. IRs enable developers to deliver a wealth of end user functionality with a minimum of effort. To build an IR the developer enters a few keystrokes, a base SQL query, some optional declarative settings and voila – the end users gets a fully-operational interactive set of data from which they can apply filters, rearrange columns, highlight, sort, chart, group by and otherwise slice and dice their data.
Note: Most developers and end users are familiar with the search, filter, sort and other interactive features. The paper assumes readers are familiar with pre-APEX 5 IR’s. For those who are not, a full description can be found in this Oracle Technet (OTN) introduction to APEX IRs: http://www.oracle.com/technetwork/testcontent/irrs-083031.html
Every APEX release since 3.2 has added more features, most noticeable in the Action Menu. In addition, many developers have added customizations via CSS to improve appearance, dynamic actions to mimic MS Excel behavior, and otherwise extended standard IR functionality. Developers have made IR customizations through dynamic actions, plugins, JavaScript frameworks and other bits of code to effect the desired changes. In general, we APEX developers can be quite creative in delivering enhanced functionality to our end users!
As usual for a new APEX release, APEX 5 introduces new IR features: new and enhanced Acton Menu features, some cosmetic uplifts and some report management improvements. However, with APEX 5, the major IR changes are behind the scenes – APEX IRs have been rebuilt from the inside out. Both the “inside” changes – the JavaScript engine – and the “outside” changes- CSS classes and Ids – are significant. The important note for developers is that because of this re-architecting, even when developers used the APEX-standard dynamic action or plugin frameworks, if the customization code references the pre-APEX id’s and class elements, the customization code will need to be refactored to upgrade.
Why so many changes, and why such a drastic change? Several reasons. The revised IR code allows for:
• Multiple IRs on one page (perhaps the biggest new feature, to be described in detail in later sections of this paper)
• Modal dialogs
• The Universal Theme and Theme Roller customizations
• APEX overall usability and accessibility
These features, particularly multiple IRs on a single page, were just not possible with the pre-APEX IR architecture. The new code makes sense, when one considers that multiple IRs on a single page was not possible with the legacy IR structure – something had to change. With all of the other IDE and end-user interface changes in APEX 5, the IR changes make sense.
The downside is, developers who have tooled outside of the APEX sandbox must now invest some time in upgrading to the new IR structure and JavaScript. All IR customizations made outside of the standard APIs may not work in APEX 5. Developers who have made such changes will need to refactor their customizations.
The following sections discuss APEX 5 IRs in detail. The KNOW YOUR USERS section applies to all developers. The PAGE DESIGNER section gives an overview of the Page Designer, the new IDE for building APEX apps. The NEW FEATURES section reviews key new features that developers and end users can leverage upon upgrade. In Part 2, the CHANGES section addresses the CSS and JavaScript changes such developers need to know to plan their upgrade to APEX 5.
IRs are very powerful in that they deliver a lot of end user functionality with minimal developer effort. However, it is the developer’s responsibility to maximize IR effectiveness by using the declarative settings to tailor the IR to end user needs. This means the developer needs to be aware of such things as overall security needs, how user use the data set, which Action Menu features should be prohibited or restricted, how much training end users will require, which download options are required, and how users are likely to use Saved reports. It is the developer’s job to prepare and deliver the appropriate IR query and action set to support them.
In short, Know Your Users. Watch what they do, because what they really do is not necessarily what they say the do or need. Deliver the functionality they need, restrict the features they should not have, and ensure the data set they receive is useful to them.
Perhaps the most impressive new feature of APEX 5 is the Page Designer, an alternative to the Component View and Tree View modes of APEX development.
The Page Designer is a totally redesigned development environment for building APEX. It allows the developer to stay in “edit mode” in one window while viewing the results of changes in a separate window. This eliminates opening and closing edit dialogs – the Page Designer is always open. This new APEX IDE drastically changes the flow of how one develops in APEX, and in most cases streamlines the process. Before addressing APEX 5 IR new features, it is important to be a bit familiar the Page Designer. Figure 1 illustrates the APEX 5 Page Designer.

Page Designer in APEX 5

Page Designer, the new building interface for APEX 5

Figure 1 – APEX 5 Page Designer

The key features are:
• Left Pane – The Tree pane. This pane contains four views, Rendering, Dynamic Actions, Processing and Shared Components. Each view contains a tree structure of the components of that category on the page, and allows for selection of components and Attributes of those components. Selections here open the corresponding property edit dialogs in the right-pane Property Editor region.
• Center Pane – The Grid Layout pane. This pane is a visual representation of the relative location of components on the page. One can rearrange components on the page by drag and drop of selected elements in this region. One can add components by dragging from the component Gallery at the bottom of the page and dropping onto the layout grid.
• Right Pane – The Property Editor. This is where one edits properties of the components selected in the left, Rendering pane. The same attributes for each component type exists as in earlier APEX versions, this is simply a new interface for editing them.
For a better description of the Page Designer, see the APEX 5 Application Builder User’s Guide, section This is accessible from the Help menu of the APEX 5 Early Adopter.
It is easiest to understand and learn the Page Designer by diving in and working with it. While the Page Designer is a totally new development interface, this author found the transition quite smooth. The most difficult aspect was finding attribute locations in the Property Editor. To assist in this I developed the habit of using the Close All button to reduce the Property Editor to simple list, from which it is easier to find the section of interest.
The other change is operational – one makes changes in the Page Designer, then clicks Save, then Run. Run opens a version of the page in a separate window (or in a separate tab, depending on one’s browser settings. Thus a developer can have the Page Designer, the edit interface, open in one window and the current version of the app open in a different window.

Fig 2 - Page Designer Interactive Report settings

Page Designer, Interactive Report Settings

Figure 2 – APEX 5 Interactive Report Region and Property Editor in Page Designer

Fig. 3 - Action Menu in Page Designer

Fig. 3 – Action Menu in Page Designer

Figure 3 – Action Menu Attributes in Page Designer

Specific to IRs, one needs to be familiar with how the IR properties appear in Page Designer ,to be able to find where to make the appropriate changes. This author found the transition quite easy, though it took a few minutes to acclimate. To get to IR properties, click to open the IR in the Rendering region in the left, Tree pane, or click on the IR region in the center Grid Layout pane. Figure 2 shows the IR Regions and Property Editor in Page Designer. Figure 3 shows the Action Menu Attributes in the Page Designer which displays in the right, property panel.
The recommendation is to spend a few minutes in the Page Designer to become familiar with its operation. Then spend a few moments specifically on an IR. Locate the Columns and Attributes in the Tree panel, then locate the corresponding Property Editors in the right panel. Don’t forget to Save (which the Page Designer will remind you of) and Run, both buttons located at the top right of the page. Also, become familiar with jumping from the Page Designer to your application is a separate page or window.
Note: Developers used to achieve as similar simultaneous builder-app window-mode by running the APEX Builder in one browser (i.e. Chrome) page, and running the application in a different browser (i.e. Friefox) page. There are other ways to achieve the same with virtual server names. We no longer have to do this. In fact, when we do, we then have two Page Designer windows and two app windows – this author found it gets confusing quickly. We still need to test in multiple browsers, but it is no longer essential to use multiple browsers or other tricks to have simultaneous develop and run windows. A big plus!
APEX 5 introduces a new IR look-and-feel, improved usability and accessibility features, new and enhanced Action Menu features. The following sections detail the most notable new features that developers and end users should be aware of.
The IR user interface has been updated to be “cleaner” and have “more semantic HTML markup”. The cosmetic changes are in line with the new Universal Theme. APEX 5 IRs are responsive, accessible, and easily customized via Template Options and Theme Roller. All popup windows are now modal. All icons are now scalable vector icons. The colors of the icons and popups will follow that of the theme in use.
Column heading dialogs, shown in Figure 4, are now activated by a click. The edit dialogs of the Control Panel are now improved for easier – fewer clicks – edits. All of these cosmetic and usability improvements are apparent and appreciated after a few minutes of working with an APEX 5 IR.

Fig. 4 - Column Headings

Figure 4 – APEX 5 IR Column Heading Menu

At last, APEX IRs have declarative “floating headers”, a feature much requested by Microsoft Excel (MS Excel) users. This new feature allows a developer to declare that column headings stay fixed at the top of the Page the top of the Region, or scroll with the data (None) as the user scrolls down the page. Figure 5 illustrates fixed headers – fixed to the page – in action (as best one can in a static page). The headers for both IRs stick to the top of the page as the user scrolls through the IR rows. Figure 6 shows the Fixed To attribute options in the Property Editor, used to set the Fixed To column heading behavior.
Alas, the corresponding Fixed Column feature, another favorite of MS Excel users, is not part of APEX 5.

Fig. 5 - Floating Headers

Fig. 5 – Floating Headers

Figure 5 – Floating Headers (“Fixed Headers”)

Fig. 6 - Fixed Header Attributes

Fig. 6 – Fixed Header Attributes

Figure 6 – Fixed Header Attributes


The GROUP BY action now allows up to 8 Group By columns. This is 3 more Group By columns that APEX 4.2. The Group By modal dialog is shown in Figure 7. Figure 8 shows access to edit an existing Group By pane and the Group By button to toggle between the Group By pane and other IR views (in this example the main tabular view and a Pivot view).

Fig. 7 - GROUP BY Dialog

Fig. 7 – GROUP BY Dialog

Figure 7 – APEX 5 Group By Dialog

Fig. 8  - GROUP BY Options

Fig. 8 – GROUP BY Options

Figure 8 – Group By Options in APEX 5 IR

As with other optional report views, formatting options on the Group By view are restricted, in this case to Group By and Group By Sort. Figure 9 shows the limited set of format actions for a Pivot report. As always, the Filter (shown), Rows per Page, Flashback, Save Report, Reset, Help and Download options, if declared and authorized, are still available in the Group By report view.

Fig. 9 - Limited Format Options  from GROUP BY View

Fig. 9 – Limited Format Options from GROUP BY View

Figure 9 – Group By Limited Format Options

The PIVOT action is a significant new Action. Anyone who has written the SQL for a pivot query with an indeterminate number of columns knows the value of this new feature – all the heavy lifting of building the Pivot query is done automatically for you. Point, click, Apply, and there is your Pivot report. As a reference, links to some common SQL Pivot query solutions are included in the References session of this paper. The old way of achieving a pivot report will not be discussed here.
Figure 10 illustrates the APEX 5 Pivot dialog. Figure 11 shows a pivot view of an IR. Note the Edit Pivot access to edit the pivot view in the control panel, and that Pivot button for toggle between the Pivot report view and other report views that may be defined.

Fig.10 - Pivot Dialog

Fig.10 – Pivot Dialog

Figure 10 – APEX 5 PIVOT Dialog

Fig. 11 - PIVOT Options

Fig. 11 – PIVOT Options

Figure 11 – APEX 5 IR PIVOT Options

As with other optional report views, formatting options on the Pivot view are restricted, in this case to Pivot and Pivot Sort. Figure 12 shows the limited set of format actions for a Pivot report. As always, the Filter, Rows per Page, Flashback, Save Report, Reset, Help and Download options, if declared and authorized, are still available in the Pivot view.

Fig. 12 - Limited Format Options in PIVOT View

Fig. 12 – Limited Format Options in PIVOT View

Figure 12 – Limited Format actions in Pivot view

Developers may now build “any number” of IRs on one page. This single feature is one of the reasons for the changes in how IR elements are named, – it was essential to do so, so that each IR element is clearly identified. To create multiple IRs on one page, simply create another one using any of the Create Region options. The Create IR wizard used to block this operation – that block has been removed. Create away!
With the improved editing capabilities of the Page Designer, it is easier than ever to create multiple IRs on a single page, as it is very simple to copy (Duplicate) and move regions around on the page.
To copy an IR on the same page, from the Rendering view in the Tree pane, right-click the IR region to copy and then select the Duplicate option. If the new, duplicate region was not created where you want it, select the IR in the Grid Layout pane and start to move it. The objects in the Grid Layout pane will become highlighted in yellow – this indicates you are in the drag-and-drop mode that allows you to drag the selected region to a new location. Drag the selected region around to the desired location and let go.
Hint: You may have to go slow, and be patient with getting the drag-and-drop layout to open up the location you want. Figure 13 shows selection of the Duplicate option from the Rendering pane.

Fig. 13 Duplicate Region Options

Fig. 13 Duplicate Region Options

Figure 13 – Duplicate Region Option from the Rendering Pane

Allowing multiple IRs on one page mandates changes in how filter parameters are passed to an IR in links between pages. The traditional IR filter options of IREQ_<COLUMN_NAME>, etc. shown in Table 1 work for a single IR on a page and will continue to work for the case of a single IR on a page. Applications with links to pages that contain one IR will continue to work upon upgrading to APEX 5. When there are multiple IRs on a page, however, this link syntax changes.

APEX Single IR Link Qualifiers
Single IR Link Qualifier Meaning
IREQ_<column_alias> Equals
IR _<column_alias> Equals
IRLT_<column_alias> <
IRLTE_<column_alias> <=
IRGT_<column_alias> >=
IRGTE_<column_alias> >=
IRLIKE_<column_alias> LIKE
IRN_<column_alias> NULL
IRNN_<column_alias> NOT NULL
IRC_<column_alias> Contains
IRNC_<column_alias> NOT Contains

Note, no BETWEEN, IN and NOT IN options
Table 1 – Single IR Link Filter Syntax

With multiple IRs on a page, the link syntax must distinguish which IR to operate on. To do so, the APEX 5 link syntax for cases of multiple-IRs incorporates the Static Id of the IR, in square brackets, just after the link qualifier:


The full list is the same as for single IRs, as shown in Table 2.

Table 2: APEX Multiple IR Link Qualifiers
Multiple IR Link Qualifier Meaning
IREQ[STATIC_ID]_<column_alias> Equals
IR_[STATIC_ID]<column_alias> Equals
IRLT[STATIC_ID]_<column_alias> <
IRLTE[STATIC_ID]_<column_alias> <=
IRGT[STATIC_ID]_<column_alias> >=
IRGTE[STATIC_ID]_<column_alias> >=
IRLIKE[STATIC_ID]_<column_alias> LIKE
IRN[STATIC_ID]_<column_alias> NULL
IRNN[STATIC_ID]_<column_alias> NOT NULL
IRC[STATIC_ID]_<column_alias> Contains
IRNC[STATIC_ID]_<column_alias> NOT Contains

Note, no BETWEEN, IN and NOT IN options

To facilitate passing parameters, it is recommended that the developer declare a Static Id for the IR. If a Static Id is not declared, one will be generated for you in the format R123456789012345 wher 1234567890125 is th assigned static identifier. For this author, it is much easier to remember and use a declared Static Id (one that I define) than to use Inspect Element to dig out the APEX-supplied region identifier. It is simpler and more meaningful to pass references of the format IREQ[EMPS]_DEPTNO, for example, than write references such as IREQ[R1132354054905]_DEPTNO.
One may ask, what is the value of multiple IRs on the same page? The use cases are as many and varied as there are APEX installations. The fact is, users are accustomed to search and filter operations on the report, they want and even expect those features on all of their reports. It is likely when there are multiple IRs on one page the span of actions allowed in each IR will be limited to those that make sense for the particular data set.
It is the developer’s responsibility to design the page and the IRs on it such that the user has exactly the functionality they need, and is not overwhelmed with options.
Multiple IRs on one page opens up several options not previously possible. Consider building a dashboard of reports, all IRs so that each data set may be configured with chart, group by and perhaps pivot views. Imagine driving the contents of all the IRs with a click on the value of one IR, say, the one in the top left position. Figure 14 illustrates such a scenario.

Fig. 14 - Multiple IRFigure 14 – Multiple IR “Dashboard”

This scenario can be constructed by building multiple IRs on one page, then enabling a link on the Dept column of the top left IR to the DEPTNO columns of the remaining IRs on the page. To do this, edit the Link attribute of the DEPT column of the top left IR, as in Figure 15. The Link attributes region may not readily appear in the column property editor. If it does not, ensure that the column type (at the top of the Attributes panel) is selected as Link. Then the Link attributes region should appear as shown in Figure 15.

Figure 15 - IR Column Link Syntax

Figure 15 – IR Column Link Syntax

Figure 15 – IR Column Link Property Editor

The Link attributes region may not readily appear in the column property editor. If it does not, ensure that the column type (at the top of the Attributes panel) is selected as Link. Then the Link attributes region should appear as shown in Figure 15.

Fig. 6 - Component View from Page Designer

Fig. 6 – Component View from Page Designer

Figure 16 – Component View Icon from Page Designer

An alternative way to set the Link attributes is to revert to the Component Editor, add the column link attributes there, then return to the Page Designer. The column Link attribute section will be there. The Component View can be reached by click on the Component View icon, shown in Figure 16, in the Page Designer top menu.
To return to the Page Designer from Component View use the corresponding Page Designer icon in the top menu bar, as shown in Figure 17.

Fig. 17 - Page Designer from Component View

Fig. 17 – Page Designer from Component View

This same approach of switching to the Component View to perform certain settings, then switching back to Page Designer, is always available and can be helpful during a developer’s transition to to being fully comfortable in Page Designer
As with all links to IRs, it is often desirable or necessary to reset the IR to its original or default settings before passing in the next link. There are two options to do this, passed in the Cache position of the APEX URL:
RIR – Resets the IR to the default settings. This is the most frequently used option.
CIR – Clears all IR settings, regardless of the defaults.
A Reset after a Clear will return the IR to its default settings.
A plain RIR or CIR will reset or clear a single IR on a page.
This same syntax will also reset or clear ALL IRs on a page. For example:
will redirect to page 10 of application 100, no debug, resetting the single IR – or all IRs on the page – to the default settings.
To reset or clear only one IR when there are multiple IRs on a page, the syntax is a bit more complicated. The trick is to specify which IR you focusing on using the format IR[STATIC ID] in the Request portion of the APEX URL, followed by the RIR or CIR directive in the Cache portion of the URL. For example:
Where one IR’s static id is DEPT, and another IRs static id is EMP, this will redirect to page 10 in application 100, reset the IRs with the DEPT and EMP static ids, but will not touch other IRs on the page.
Note that the expected syntax options of [STATIC_ID]RIR and RIR[STATIC_ID] in the Cache portion of the APEX URL do not work. The direction on which IR to operate on must be specified in the Request portion of the APEX URL.
There is always the option to use the APEX_IR API, the CLEAR_REPORT and RESET_REPORT procedures (either signature) in a dynamic action to achieve the desired individual report reset. There is also the option of triggering the APEX-standard apexrefresh event, as described in this article: http://docs.oracle.com/cd/E59726_01/doc.50/e39147/extend_app002.htm#HTMDB30267
Using a command like:
apex.eventtrigger( “#myRegionStaticId”, “apexrefresh”);
All of the Reset and Clear options described in this section are supported.

APEX 5 IRs contain some noteworthy new features for end users and developers. For end users, the most significant of those are the improved look-and-feel, modal dialogs, the enhanced GROUP BY action, the new PIVOT action, and the option for multiple IRs on a single page. For developers, the big new operational change is the Page Designer. This installment covers the highlights of those new and enhanced features.
In Part 2 of this series, we cover the most significant change for developers, the totally revamped APEX 5 IR underlying architecture. Developers who have done customizations on IRs in previous APEX versions may need to refactor their code to follow the new APEX 5 IR structure. With APEX 5, the overall power of APEX IRs has increased, but when it comes to customizations, some upgrade work may be necessary. Stay tuned for Part 2!
The APEX OTN Forum

APEX Interactive Report OBE’s

AMIS Pivot Implementation for pre APEX 5

Manually Refreshing APEX Components

APEX 5.0 Interactive Report Customization, John Snyders

2015 ODTUG Editor’s Choice Award Winner

Congratulations to Peter Koletzke, the 2015  ODTUG Editor’s Choice Award Winner, for his paper Going Mobile: Mobile Application Design Principles for ADF Development.

Peter is a annual contributor for the ODTUG Editor’s Choice Award, the ODTUG Technical Journal and ODTUG in general. I personally have learned a lot about technical writing and editing from Peter – and as excellent his papers are, he is always looking for improvements. Besides excellent writing, Peter includes hands-on exercises to reinforce the concepts.

I would also like to officially thank the 2015 Editor’s Choice Review Team:

  • Opal Alapat
  • Galo Balda-Andrade
  • Danny Bryant
  • Patrick Cimolini
  • Krishna Marur
  • Tim German
  • John King
  • Peter Koletzke
  • Deanna Sunde
  • David Schleis (Extra thanks to David for the T-shirt design!)
  • Donna Richey Winkelman

These unsung heroes – all volunteers –  read and scope all Editor’s Choice submissions – a neat preview of our Kscope conference materials.

Our plan is to publish all 2015 Editor’s Choice submissions in upcoming ODTUG Technical Journal Corner postings. Stay tuned!

Happy Coding,


Thank you to all – Congratulations to Peter – and I look forward to all ODTUG Technical Journals submissions in the coming year.

2015 ODTUG Kscope Editor’s Choice Award – Calling All Minds, Calling All Media

It’s that time of year again – for the ODTUG Editor’s Choice Awards. This year we welcome submissions in all media – even online annotated demos. Shows us your stuff – wow us with your brilliance and clever use of Oracle development tools!

Hello, ODTUG!

Spring is here – finally – and that means ODTUG KScope Editor’s Choice Award submissions are open,

The annual ODTUG Editor’s Choice Award honors excellence in creative, effective use of all modern media options to convey a complete technical topic to our members. Every Kscope presenter is eligible.

2015 is again an all-media year. The popularity of blogs, videos, and podcasts provest hat today’s developers gather and share information in a wide variety of media. ODTUG members consume information online and in various media formats, in addition to formal white papers and printed textbooks. Some say the white paper is dead; others simply save trees and present and consume all their technical content online. We are with you.

ODTUG’s aim is to deliver quality, timely technical content to our members in the most convenient online formats possible – and we want to keep that momentum going. Two years ago, our Technical Journal transitioned to an entirely online format. Accordingly, we have made the same transition with our Kscope conference materials.

We ask our Kscope speakers to submit both their presentation slides (mandatory) and an alternate media technical submission (optional) that covers their presentation topic. The additional, non-PowerPoint submission is eligible for the Editor’s Choice Award. The Editor’s Choice submission may be in any combination of the media types: video, audio file, blog post, online demonstration, white paper or whatever, as long as we can reuse it in our online Technical Journal. Entries should convey a complete technical topic, the topic of the speaker’s presentation.

All conference materials are eventually available to our members. The purpose of our all-media expansion is two-fold. Accepting speaker presentation submissions in reusable media types:

  • Increases the volume and variety of technical material available to our members
  • Expands the ODTUG Editor’s Choice Award to acknowledge creative, effective use of all modern media options to convey a complete technical topic, not just white papers

Our Editor’s Choice Award honors excellence in conveying a complete technical topic to our members. The Editor’s Choice Award winner is decided by a team of ODTUG experts who review all of the conference submissions and grade them according to these general topics:

  • Communication Quality – assesses communication style (written or verbal), overall organization of the media, and grammar
  • Applicability – evaluates subject matter importance, completeness, effectiveness, and accuracy
  • Topic Merit – acknowledges the exceptional and innovative, treatment of a cutting-edge topic, technical complexity, or a new and unique approach to a common problem

Most important for the Editor’s Choice Award is overall excellence in technical communication of complete technical topic for our ODTUG members. Grammar, organization, proper formatting (for the media type), clarity, examples appropriate for the content, and how well the message is conveyed all matter more than the media type. The overall goal is to deliver great content to our members.

By the way – no tweets allowed! Our ODTUG members are smart and eager to learn; they are hungry for more than a tweet. As a technical editor, I believe our members deserve more than 140 characters.

We are also accepting nominations for our Editor’s Choice Review Team. This dedicated team of volunteers reviews each conference submission according to published scoring criteria. The scores are weighted and averaged to arrive at an overall winner. Our review team is comprised of a cross-section of our members – no one developer tool or technical topic is overly weighted or favored. We are always looking for new Editor’s Choice Team members. To join us, or to nominate someone else, please contact me at kcannell@odtug.com. This is an easy way to volunteer for ODTUG and get a pre-conference view of all Kscope conference material. It’s like a conference before the conference.

So, Kscope presenters – WOW US with your stuff! The door is open – show us what you know, in your favorite media. I thank you in advance, from all our ODTUG members.

As always, happy coding,



New ODTUG Board Announced

Congratulations to the new ODTUG Board Members:

  • Tim Tow, Applied OLAP
  • Cameron Lackpour, CL Solve
  • Mike Riley, Hortica Insurance
  • Sarah Zumbrum, InterRel Consulting

These newly elected members join:

  • Monty Latiolais, Insum Solutions
  • Martin D’Souza, ClariFit
  • Natalie Delemar, Ernst & Young
  • David Schleis, Wisconsin State Laboratory of Hygiene
  • Mia Urman, AuraPlayer

I was honored to be running among an amazing class of candidates. THANK YOU to all my supporters – I will try again in coming years.  And many thanks for all who voted – your help is essential in determining the future of ODTUG and ensuring we meet all our members needs.

I will continue as Editor of the ODTUG Technical Journal, and will continue to strive for improved ways to bring more content in all forms to our members.

Please check out the ODTUG Technical Journal Corner for our latest articles.


ODTUG KScope14 Editor’s Choice Award Winner – NoSQL and Big Data for the Oracle Professional

The ODTUG Technical Journal Corner currently spotlights our 2014 Editor’s Choice Award winning article NoSQL and Big Data for the Oracle Professional by Iggy Fernandez.

Find it odd that the ODTUG Editor’s Choice Award Winner focuses on NoSQL?  Not really – NoSQL and relational database can coexist. If you haven’t paid attention to NoSQL yet, this article is an excellent read to learn how NoSQL and the relational model can actually complement each other. In Iggy’s words:

“…I claim that the NoSQL camp derides the relational model because it does not sufficiently understand it. I will go so far as to claim that the NoSQL camp does not fully understand its own innovations; it believes they are incompatible with the relational model and it therefore does not see the opportunity to strengthen the relational model, …”

Strong words? Iggy fully makes his case – he thoroughly supports this assertion in one of the best NoSQL vs Relation Database articles I have seen.   

To access the full article, as all ODTUG Technical Journal Corner articles, you must be at least an Associate ODTUG Member (Free).  And that gives you access to all Technical Journal content, plus all ODTUG Communities content.  Check it out – there is lots there to absorb.

Watch here and the ODTUG Technical Journal Corner for another perspective on NoSQL and Oracle technology from PL/SQL Evangelist Steven Feuerstein, coming soon.  

The ODTUG website in general contains material from all our past conferences, plus years of excellent material from our expert columnists in Business Intelligence, PL/SQL, Oracle Tools, Fusion MIddleware, Hyperion, Essbase and more.  Watch for new authors Kevin McGinley on Business Intelligence topics, and a series of material from the Oracle Tools team.

Never ever suffer from Low T – stop by ODTUG.com and the Technical Journal Corner for a quick T-fix.  For a bigger T boost, submit your article (or article ideas) to the ODTUG Technical Journal Corner – email kcannell@odtug.com for more info.

APEX 5.0 Interactive Reports: The Good, the Bad and the Ugly, Revisited

This presentation covers the Good, the Bad and the Ugly of APEX 5.0 Interactive Reports. In short, the good news is APEX 5.0 brings us multiple IR’s on a single page, plus some other enhancements like improved Group By and Pivot. 

The Bad is, to do this, the APEX Team changed the guts – CSS and JavaScript / jQuery – so anything you may have built previously that does not use the standard APEX APIs will need to be refactored.  Fortunately, I found the mapping between the old and the new quite easy to follow.  While the changes are tedious to make and test, I was fairly quickly able to get my customized IRs back in working order on APEX 5.0.

The Ugly remains the default look and feel, and WYSIWYG printing options.  In APEX 5.0 Theme Option will make applying consistent styles easier  – all told less work to make you IRs match the rest of your application.  As for WYSIWYG PDF output, so far there are no advances here.  The best option I have found is to capture the as-is IR query (different APEX_IR_QUERY solutions for APEX 4.1 vs APEX 4.2, and again for APEX 5.0 – improvements each version) combined with a function that returns a table in the Report Query, combined with a custom Report Layout.  One could use the generic layout, but since this does not allow for varying column widths or highlighting and other conditional formatting, for my case I needed a customized XLS-FO template.  No worries, I found once I  built the first few the process gets easier. I also used a generator for the first pass – a time-saver – then customized. That is a topic for it’s own post on another day.  Any way you slice it, PDF output beyond the default generic for IRs is time-consuming and tedious.

See the full presentation here:

APEX 5 Interactive Reports: The Good, the Bad, and the Ugly:  kcannell.GoodBadAPEXIR.presentation


APEX Data Load Options – How Do I Load Data …

How to Load MS Excel Data into APEX?

The most common APEX data load problem….how to load MS Excel files as XLS, not as CSV.

There are as many ways to load data into an Oracle database as … well, there are a lot of them.  When building an Oracle Application Express application, there are some data load options that are more suitable than others.  This presentation outlines the standard APEX-supplied data load options, explores the APEX Data Load wizard, and discuss how to integrate some more traditional Oracle data load options into your APEX applications.  This is by no means an inclusive view of all the possibilities, but some of these options will give you ideas on how best to approach your data load issues.

Download the presentation here:  APEX Data Loading Options