APEX Presentations ~ Through the Years

I was recently asked to summarize my APEX Presentations. I realized I had them in several places, but not in one consolidated space. So here it is – a summary of my APEX Presentations through the years:

APEX 5 Interactive Reports

Updated for the APEX 5 release, these links contain the presentations from KScope 15, and white papers as published in RMOUG’s SQL>UPDATE magazine.

For APEX 5, Interactive Reports have been reengineered from the inside out. Any developer who uses IRs, has made customizations for appearance or features or performance outside of the standard API’s, even pagination plugins, needs to be aware of the new CSS and JavaScript engine.  APEX 4 and earlier customizations will likely not work in APEX 5 without some refactoring. Be prepared!

Presentations: APEX 5 (Released) Interactive Report Deep Dive, KScope 2015 Presentation
   APEX5 Interactive Reports New Features and Upgrade Cheat Sheet – Presentation RMOUG 2015
Papers:     APEX 5 Interactive Reports Part I: New Features and Enhancements
     APEX 5 Interactive Reports Part II: Structural CSS and JS Widget Code Changes

Migrating Critical Business Application to APEX – Successfully

Presentation: Migrate Business Applications to APEX, Successfully – Presentation 

I had a blast at COLLABORATE14 in Las Vegas, NV, April 6-11, 2014.  I had a chance to catch up on Oracle 12c skills, learn more about ADF Mobile with APEX for hybrid mobile applications, XQuery and XML DB and of course refresh friendships with all those at IOUG.  My Friday presentation was on Migrating Critical Business Applications to APEX, covering tips and tools for migrating any legacy app to APEX.

APEX Data Load Options, or, How Do I Load Data, Let Me Count the Ways …

Presentations: APEX Data Loading Options

There are many many ways to load data into an Oracle application. This presentation discusses data load methods specific to APEX – the data load wizard, external files, custom methods and using the APEX Listener for a true MS Excel file upload. This is practical what works, what doesn’t information to help one decide the best approach for data loading problems.

Meaningful Maps, Gantts and Charts

Presentations: Meaningful Maps, Gantts and Charts

APEX 4.0 introduces improved charts and maps, but how to make sense of them? This presentation covers all the basics for understanding APEX 4.0 Charts, Maps and Gantt Charts.  Plenty of information to get one building meaningful charts and maps in APEX applications.

Tight Tabular Forms

Presentations: Tight Tabular Forms

APEX tabular forms are handy, but kind of loose, if you use them out of the box. This presentation covers the improvements in APEX 4.0 for tabular form validations, plus some tips on adding validations using the APEX_APPLICATION arrays, and building manual tabular forms using the APEX_ITEM API.

Migrate BI to APEX: What Works, What Doesn’t, and Lessons Learned Along the Way

Presentation: Migrate BI to APEX – What Works, What Doesn’t, and Lessons Learned Along the Way

This presentation covers a real migration from a major-vendor BI tool to APEX Interactive Reports for end-user information needs. Covers feature considerations such as drills, pivots, output; dynamic crosstab options and construction, including use of 11g PIVOT and AMIS PIVOT object type solution.  Careful crosstab query construction makes APEX Interactive Reports more powerful than one may think. APEX 4.0 new features address the needs for user-defined grouping, shared reports, scheduled report delivery, multiple views of the same data set and improved charting.  Check it out!

Note:  I am working on an APEX 5 version of this presentation – improvements in APEX 5 interactive reports, including the PIVOT feature, may tip the scales and make a switch from an expensive, underutilized BI tool to APEX a better use of resources.

Oracle Application Express: Tactics to Tackle APEX Bugs

Paper:               Tactics to Tackle_APEX Bugs,  Paper

Presentation: Tactics to Tackle_APEX Bugs, Powerpoint

Know your application, know your tools, have a plan, and always remember that you know some stuff. You can debug Application Express apps and stay sane.

APEX development is a breeze, until something goes wrong and there’s no obvious clue of what happened or how to fix it, and there’s no UNDO. Or is there? This session presents tactics to tackle common APEX problems such as MRU internal error, Browser errors, SQL and PL/SQL issues, HTML, and JavaScript problems. We cover triage, debug tool options, and debug tactics, and then demonstrate applying those strategies to tackle common APEX problems.

This paper and presentation were written for APEX 4.0 (or earilier?) ~ APEX debugging techniques are much improved now! However, the strategies and tactics for addressing bugs: knowing your app, knowing your tool, instrumenting, researching first and using the forums still apply.

Oracle Application Express Interactive Reports: The Good, The Bad, The Ugly

Presentation: APEX Interactive Reports: The Good, The Bad & The Ugly

Paper:               APEX Interactive Reports: The Good, The Bad, The Ugly

I had fun writing this paper, mainly because the movie (old western, Clint, of course) theme song played in my head, and our house is defnitely a Clint-John Wayne-Sam Elliot-Any Old Western neighborhood.

Oracle APEX Interactive Reports have many Good points, but one-size does not fit all, so we developers still have some cleverness to apply to meet end user requirements in all cases. This paper outlines the Good, the Bad, the limitations, and shows some easy CSS edits to address the Ugly, the default appearance. Have fun!

APEX Interactive Reports offer amazing functionality out-of-the box, but this wealth of features comes with limitations. This session presents the Good, the Bad and the Ugly of APEX IR, then details and demonstrates workarounds to address the Bad (the limitations) and the Ugly (the appearance). Learn how to limit Search and Activity features for different user roles, how to eliminate part or all of the Search and Activity Bars and how to customize the look-and-feel to fit your corporate template.

(As with all my papers, I will keep this post up to date with the latest version of the paper.)

APEX Cheat Sheet

Download the APEX Cheat Sheet paper from here: APEX Cheat Sheet

When starting out with Application Express, it can be a bit confusing in terms of what syntax to use where, how to effect the item alignment needed, how to customize the look-and-feel… in general, how to get productive. Tha APEX Cheat Sheet paper is a collection of tips, how-to’s, syntax conventions and tricks to accelerate a developer’s APEX learning curve. The APEX Cheat Sheet paper has grown to be much longer than a “cheat sheet” but it does provide a solid quick-start to APEX development.

Note: While written for erly APEX versions – APEX 3! – some of the basics still apply, like the f?p syntax.  Someday I will do another Cheat Shet- the problem is there are so many new features, I will have to do multipe cheat sheets for each major feature.

APEX Under the Covers

Paper: APEX Under the Covers

In working with Oracle Application Express I have found the best way to learn “how they did that” is to look “under the covers” at the APEX code … The way to do that is to download an application, install it in my builder, and view how the author implemented the features of interest.

The white paper and presentation APEX Under the Covers walks the reader / viewer through the learning process of looking under the covers at some not-so-standard features implemented in APEX. The examples are not rocket science; they are intended only to get the brain thinking along lines where the reader may not yet have ventured, suggesting a few different approaches to achieving an end within APEX.

Note: This presentation was written for APEX 4, but the approach is the same with APEX 5 and beyond – learn from how they wrote APEX. For APEX 5, use and LOOK AT how they wrote the packaged applications. This is free training at its best!

APEX Debug Options

Paper: APEX Debug Options

Debugging Oracle Application Express can be a conundrum.   It’s PL/SQL, but I cannot see it, and I cannot step through it. PL/SQL generates the page, but I do not have access to the APEX page generator. There is JavaScript and CSS and HTML … Where to start?  The truth is there are many avenues for debugging APEX; some APEX-supplied, some external to APEX. The paper and presentation APEX Debug Options describes both APEX-supplied utilities and tools external to APEX for debugging APEX applications.

~ Keeping it Fresh: Cost-Effective Training

Layoffs, economizing, more for less, and oh-by-the-way keep up with the latest in Web development, languages, data modeling, coding techniques, testing, quality, the latest App Servers and find time to relax a bit and network with your peers. The temptation is to postpone or skip all training. I suggest however that this is the time we need training all the more. I maintain the most cost-effective training can be found at our user group conferences. Attend! Volunteer – get involved! IT is the most efficient, cost-effective way to stay current and learn new technologies, and you might have some fun along the way.

READ_XLSX_for CLOBs ~ Extending Anton’s AS_READ_XLSX for CLOBs

I had a need to read a series of MS Excel XLSX files from a file server over a period of time – usually chunks of 20-50 files, several times a year. That’s too many files to make an upload interface practical (without annoying the users), and the files come as is from an external agency so there is no control over the contents. That is, this is NOT a case of “save to CSV format” – the requirement is to read MS Excel .XLSX format files as they come.

I was happy to use Anton Scheffer’s AS_READ_XLSX code .. but wary of the VARCHAR2(4000) column limit. Sure enough, a few files in, I hit that limit.

What to do? Modify – adjust – Anton’s code to work with CLOBS, of course. The CLOB-ready package is here, PACKAGE_READ_XLSX_CLOB.

So now I have an AS_READ_XLSX_CLOB package that will read the contents of an XLSX file column into a CLOB.

Wh so much data in one MS Excel column? Not my concern – all I need is to be able to read it into my Oracle database.

The adjusted package is available here, PACKAGE_READ_XLSX_CLOB.

The change?  Simple – remove the VARCHAR2(4000) limiting piece – the DBMS_XLSPROCESSOR.VALUEOF function call.

With a little research I discovered the DMBS_XSLPROCESSOR.VALUEOF function was returning into a VARCHAR2(4000) variable  Understandable for a function call.

BUT the DMBS_XLSPROCESSOR.VALUEOF procedure does not have that limit – well, it would have  depending on how I have the variables that hold the VARCHAR2 OUT parameter.  Using the VALUEOF procedure made it easier to use a CLOB variable to accept the OUT parameter.  Of course there is implicit conversion from the procedure VARCHAR2 OUT parameter to the CLOB variable.

I used a CLOB, because that is how I need to store my data. And yes, a significant number of those spreadsheet table cells are indeed > 4000 characters, meriting the CLOB datatype for our purposes

The change:

Change the string_val declaration from VARCHAR2(4000) to CLOB

  type tp_one_cell is record
    ( sheet_nr number(2)
    , sheet_name varchar(4000)
    , row_nr number(10)
    , col_nr number(10)
    , cell varchar2(100)
    , cell_type VARCHAR2(1)
    , string_val clob        --KC: was --varchar2(4000)
    , number_val number
    , date_val date
    , formula varchar2(4000)

Replace the function calls:

t_strings( t_c ) := dbms_xslprocessor.valueof( dbms_xmldom.item( t_nl, i ), '.' );

with equivalent procedure calls:

dbms_xslprocessor.valueof( dbms_xmldom.item( t_nl, i ), '.', t_strings( t_c ) );

Then I use the AS_READ_XLSX_CLOB package the same as AS_READ_XLSX … for example:

select * from table( as_read_xlsx.read( as_read_xlsx.file2blob( 'DOC', 'Book1.xlsx' ) ) );

or, for ease of reading data in a procedure:

CREATE VIEW MYSPREADHSEET_V AS select * from table( as_read_xlsx.read( as_read_xlsx.file2blob( 'DOC', 'Book1.xlsx' ) ) );

and then reading data is as simple as:

SELECT string_val
 WHERE row_nr = 1
   AND col_nr = 2;

and its many variations.

Now I am reading in XLSX spreadsheets with ease, no matter what size.

Thank you Anton ~

SQL Developer Dropping Connections – Solved

I work fast, most often across several database at once, multitasking across projects. Recently with one client I noticed SQL Developer dropping connections, repeatedly, but only on certain database connections. Most often I would need to reconnect upon opening a table or view, package, trigger, almost any object from the navigator. After successfully compiling something, need to reconnect. After creating a trigger, reconnect. Forget about an After-Update trigger – that visibly disconnected me – so I got in the habit of creating things outside of SQL Dev.  Wait a minute..that’s just wrong. I knew this had to be something easy to fix.

That’s when I started looking … After a bit of research I found this link :

Brendan Tierney – Oralytics Blog: SQL Developer is dropping connections

which explained my problem – and offered a solution:

The solution (since that’s what you care about):

Add this line:

AddVMOption -Doracle.net.disableOob=true

to the sqldeveloper.conf file.

I actually added mine to the sqldeveloper_nodebug.conf file.

For SQL Developer 4.1, for my install on Windows 7, this falls in the …\sqldeveloper\bin\ directory for the SQL Developer install directory.

The problem (the short version):

The theory is that this is being caused by and Out of Bands (OOB) error in the jdbc driver or on the network.  Whatever the error, jdbc driver or network, the constant need to reconnect was rendering SQL Developer almost useless for those connections for which this occurred.   It is not doing that any more – Yeah!

I do expect that this option will block times when SQL Dev should disconnect – so I am paying attention and watching for side effects.  So far, so good.  Hope this helps someone else,


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.


Vote for the ODTUG Board!


Voting for the ODTUG Board of Directors is now open, and this year brings an amazing list of candidates yours truly included.

I believe ODTUG is and should continue to be THE center of excellence for Oracle Developers – we do that through attracting and maintaining quality members, quality material and expert technical information in all media types, all through the work of our awesome volunteers. Social media and limited budgets bring opportunity and challenges to ensuring all our members have access to all our materials, not just those fortunate enough to attend KScope.  

I have been a member and volunteer for ODTUG since 2007, Editor of the ODTUG Technical Journal since 2008. Prior to that a member and volunteer for IOUG, Contributing Editor for the IOUG Select Journal. I have served on conference review teams, Editor’s Coice Award teams, speaker, ambassador, and in whatever other capacity is needed.  I want ODTUG to always be there when the next Oracle developer needs help and advice. 

Check out all the candidate statements here, and cast your vote to help ensure a strong and growing future for ODTUG.  Votes for Karen Cannell are much appreciated!

You must be an ODTUG member to vote, and if you are not already a member, I encourage you to join. Our user group is truly a fantastic source of technical material and side-by-side networking with the experts across the full suite of Oracle development tools. 

If you are an Oracle developer, ODTUG can help get you and keep you up-to-speed on the latest Oracle technology. And have fun and meet great people along the way.


ODTUG Technical Journal Corner –

Check out the latest ODTUG Technical Journal Corner content!

We are currently featuring:

Kevin McGinley, Red Pill Analytics, our new BI Perspectives author, with Making the Case for Leveraging Agile and Continuous Integration in Oracle BI Development, Part 1 of a three-part series.   Expand your mind a bit and consider – just consider – agile, Continuous Integration as opposed to traditional Waterfall method of integration and deployment.  Watch for the rest of this series in coming weeks. And Welcome Kevin, thank you for taking over our BI Perspectives column!

William Hodges, The Hackett Group, on Slow and Fast Changing Dimensions in Hyperion Planning  This article was a 2014 Editor’s Choice Award finalist. William address changing dimensionalities beyond the capabilities of “varying attributes” in Hyperion Planning.  Read this excellent article to learn more.

A new Book Review by John King, King Training Resources, Oracle PL/SQL Performance Tuning Tips & Techniques by Michael Rosenblum and Dr. Paul Dorsey.   Tuning tips from this pair of authors is always good advice and good reading. See John’s review on the ODTUG Technical Journal Corner, and read the book – we can all use PL/SQL tuning tidbits.

Our latest NoSQL articles, by Steven Feuerstein, Oracle, and Iggy Fernandez, NoCOUG, are still there ready for your learning. If you haven’t read these two perspectives on NoSQL and how it fits on our usually-relational lives, do so now – this is great material from ODTUG respected authors. 

Note that we have made strides in availability of our articles to ODTUG members and beyond.  Starting now, articles by Steven and other Oracle employees will be available to all, in and out of the ODTUG firewall.  This is good news and steps forward as we increase visibility and availability of ODTUG content for all.

Happy reading – and Happy Coding to all, 



Mr. PL/SQL on NoSQL: YesSQL and the Oracle Database

Steven Feuerstein’s latest Confessions of a Dirty Programmer column actually focuses on – surprise – NoSQL. Yes, Mr. PL/SQL is writing about “Not Only SQL”.  In YesSQL and the Oracle Database, Steve addresses the NoSQL movement head on.  Is SQL old and tired?  Is Database a commodity?  Read on a Steven answers these questions, giving valid arguments why NoSQL and SQL (and of course PLSQL) can live together.  Btween Steven’s NoSQL perspectives and Iggy’s NoSQL information, you will be well-armed for NoSQL considerations.

If you have not seen this week’s ODTUG Technical Journal Corner – check it out now!

Next up is one of our ODTUG Editor’s Choice Award finalists, William Hodges on “Slow and Fast Changing Dimensions in Hyperion Planning”.  A fine paper – stay tuned.

After that, watch for our new BI columnist, Kevin McGinley, debuting with Part 1 of the three-part series, “Making the Case for Leveraging Agile and Continuous Integration in Oracle BI”.  Welcome Kevin!


Many thanks to Mark Rittman for bringing us years of BI Perspectives columns, all while maintaining many other writing, speaking and work commitments. BTW, Mark has an astounding T score if 1253!  No Low-T there.  Thank you Mark, and enjoy your “retirement”!