One more time: Do we still need Data Modeling?

Yes, we need still need to do data modeling, and yes, data modeling is a skill that developers need to learn and practice.

Kent’s points are important:
— To get business value out of the data, you need to understand the data
— Even if you don’t need a model to store the data, you DO need a model to understand the data and use it properly
— Know your data!

I see fewer and fewer data modeling session at conferences these days – dull and boring perhaps, but essential for efficient development and data retrieval based on your data.  I see data modeling as a fundamental skill for all IT professionals – at least those working with databases.

The Data Warrior

More specifically do we still need to worry about data modeling in the NoSQL, Hadoop, Big Data, Data Lake, world?

This keeps coming up. Today it was via email after a presentation I gave last week. This time the query was about the place of data modeling tools in this new world order.

Bottom line: YES, YES, YES! We still need to do data modeling and therefore need good data modeling tools and skills.

Snowflake with RI A picture can say so much!

In order to get any business value out of the data, regardless of where or how it is stored, you have to understand the data, right?

That means you have to understand the model of the data. Even if the model (or schema) is not needed upfront to store the data (schema-on-write), you must discern the model in order to use it (schema-on-read).

It…

View original post 231 more words

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.

Retire_Img2

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:
Hi,
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.
Thanks

(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!

Karen

Editor Emeritus, ODTUG Technical Journal

Relax.

Off to the Islands, where all retired Editors go

Announcing the ODTUG Innovation Award

What Oracle development project in the past year made you say WOW!

What project, collaborative or individual, demonstrates exceptionally innovative and creative use of Oracle technology?

What stands out as an above-and-beyond example of Oracle technology applied to real world problems? 

To honor such creativity and innovation, ODTUG announces a more modern mode of honoring excellence within ODTUG’s supported communities (ADF, APEX, BI, Database, and EPM), the ODTUG Innovation Award.

The ODTUG Innovation Award honors excellence in creative, effective, innovative use of Oracle development tools. The goal is to honor developers – individuals or teams – whose passion and creativity shines through in their application of Oracle technology to address real world problems in our ODTUG communities.

communites

We are looking for innovative, exceptional ahead-of-the-curve applications of Oracle development tools that stand out as examples of “the next big thing” in Oracle technology, or as unusually creative applications of new and emerging Oracle tools in our ODTUG communities.

To make a nomination for the award, you must be an ODTUG member (Associate or Full). If you are not a member, join ODTUG now.

Who/What is Eligible:

  • Nominees may be individuals or teams that are non-Oracle employees.
  • Nominees need not be ODTUG members, but they must work with a tool that supports at least one of our communities (ADF, APEX, BI, Database, and EPM).
  • Open Source and / or collaborate efforts are eligible.

Nominations:

  • Nominations Open: Now!
  • Nominations Close: May 31st

More information:

What we are looking for are the things that make us go Wow! when we first see them.  Here are a few historical examples:

  • Integration of Oracle Forms and Java ADF (years ago)
  • HTMLDB, when it first came out – and the first practical applications of HTMLDB (years ago)
  • Oracle-based applications on an iPhone
  • Oracle applications of Google Glasses
  • Our first look at Node.js integrated with (extending) an APEX application. (a few years back)
  • Peter Koletzke and Paul Dorsey’s work deciphering and promoting JDeveloper when it first launched

We know sure there are examples from every Oracle technology community. ~ let us know about them!

Judging:

The 2016 ODTUG Innovation Award winner will be decided by a team of ODTUG experts who will review all of the nominations and discuss and rate them according to these general topics:

Innovation – The Wow factor: Acknowledges the exceptional and innovative
Quality – Not only does it have to be Wow, it has to work.
Applicability – Evaluates subject matter importance, completeness, effectiveness, and accuracy
Topic Merit – Treatment of a cutting-edge topic, technical complexity, or a new and unique approach to a common problem.

For this first year, the  ODTUG Innovation Award will be awarded decided by a select panel of experts from across all ODTUG communities. In future years, nominations will be open all year long, and all ODTUG members will be able to vote for their favorite.

Watch for more information on the  ODTUG Innovation Award on all the usual channels: ODTUG emails, Twitter, the ODTUG website,

Our ODTUG Innovation Award honors innovation, excellence and amazing accomplishment in the realm of Oracle Development Tools adoption and integration.   The possibilities are endless ~ What makes you, as a developer, say Wow! ~ Nominate your favorite examples now!

 

APEX 5 Reset IR w Multiple IR on Page

With APEX 5 Interactive Reports (IRs) we have the luxury of having multiple IRs on the same APEX page.

That is wonderful, until it comes time to Reset a particular IR. The usual RIR and CIR syntax operates on ALL IRs on the page. Probably not what you want.

To refresh a single IR, use the standard APEX_IR API REFRESH_REPORT procedure.

Briefly:

  • Add a Static ID to your IR. As best practice, do this for all your IRs.  The Static Id attribute is under the Advanced attribute section.
  • Create a button to trigger the IR refresh. Set the Action to Defined by Dynamic Action.
  • Create a dynamic action, on Click of your new  button.
  • Add two True events, one Execute PL/SQL for the APEX_IR.REFRESH_REPORT call, one Refresh to refresh the IR region.
  • The PL/SQL calls APEX_IR.RESET_REPORT:
DECLARE
    v_region_id apex_application_page_regions.region_id%type;
BEGIN
 -- get the IR region id
 SELECT region_id 
   INTO v_region_id
   FROM apex_application_page_regions
  WHERE application_id = :APP_ID
    AND  page_id = :APP_PAGE_ID
    AND  static_id = 'my-region-id'; -- use the Static Id set in the IR Advanced attribute section
    
 APEX_IR.RESET_REPORT(
   p_page_id => :APP_PAGE_ID,
   p_region_id => v_region_id,
   p_report_id => NULL );   -- resets the last-used report
END; 

If you need to reset a particular, saved report, you will need to query for that report_id and enter it instead of NULL in the APEX_IR.RESET_REPORT call.

The Refresh True action is a simple Refresh on your IR Region.

Be sure to uncheck the Fire on Page Load option for both True actions.

That’s it!

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:

ODTUG Board of Directors Election ~ Vote!

Vote for the ODTUG Board

The ODTUG Board of Directors election is underway now.  ODTUG memebers, this is your chance to choose 5 new board members for the 2016-2017 term.

Again this year there is a stellar selection of candidates.

Why do I run?  I have served ODTUG in many capacities behinds the scenes and as Editor of the Technical Journal, and want to :

  • Bring more quality learning material in all formats, online and offlne, to all Oracle developers:
    • more online material in more formats, and
    • more meetups to bring the message local.
  • Encourage women in technical fields:
    • to strive for higher positions in those careers, and
    • to have greater confidence in what they bring to the table technically and personally.
  • Strengthen the ODTUG Communities so that material and answers from ODTUG experts shows up in every search. We cannot keep our knowledge behind (fire)walls!

ODTUG should be the Oracle Developer go-to resource for learning, online and locally.  As a board member I will work to increase ODTUG’s online presence and increase ODTUG’s regional and local presence via co-hosted meetups.

There are many qualified candidates – regardless of who you vote for, VOTE!

Read all the campaign statements and biographies of the candidates.

ODTUG is the best source of learning for all Oracle development tools ~ your vote ensures we not only stay that way but grow as fast or faster as the tools we use.

To vote, please visit https://www.associationvoting.com/odtug and enter your email and Voter ID exactly as shown in the email you received from ODTUG (actually from announcement@associationvotiing.com) on or about 10/06/2015.

Not a member? Join!  You are missing out on the best Oracle training in the world, at minimal investment of a year’s membership fee. Betteryet, VOLUNTEER and quadruple the learning advantage.

As always, Happy Coding!

BTW ~ Accepting Viedos, Blog Posts, papers, podcasts – send us your excellence for publication in the ODTUG Technical Journal.

Karen

ODTUG Technical Journal Corner

ODTUG Technical Journal – submit papers now!

APEX 5 Interactive Reports, Part II

All APEX developers should be aware of the APEX 5 new features in order to pass these features on to their users and to leverage new development efficiencies. All developers who have customized interactive report appearance, altered Actions, written IR dynamic actions or otherwise enhanced an IR in any earlier APEX version (most of us?) need to know the behind-the-scenes details of APEX 5.0 IRs. The changes are significant, and unless the APEX standard APIs have been used the customization may not upgrade smoothly.

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. All of these are covered in Part 1 of this series. 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 the APEX 5 IR structural changes in detail. The Know Your Users section applies to all developers, and is repeated here as a reminder that all configuration and customization should have one goal, to serve user requirements. The Changes section addresses the CSS and JavaScript changes such developers need to know to plan their upgrade to APEX 5.

Know Your Users

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. If customizations beyond standard IR features are required, they should first be implemented using standard APEX Pis, and only as a last resort achieved through custom coding.

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.

Changes

Turn and face the strange ch ch ch changes …

D. Bowie

APEX IRs have been reengineered to accommodate multiple interactive reports on one page, and in keeping with the APEX5 overall style, usability and accessibility improvements. The underlying architecture is quite different than previous versions.  It is important the developers understand these changes, particularly when adding dynamic actions, plugins or any other customizations.

CSS Changes

The main CSS changes are in a different pattern of class and id nomenclature.  The old apexir_<element> id constructs are gone, replaced by a series of a-IRR-<element> classes and revised id names. Figure 1 shows the pre-APEX 5 IR HTML and CSS structure. Note the apexir_<element>  id and class names.  Figure 2 shows the APEX IR structure.  Note the new [STATIC_ID]_<element> id name, and the a-irr-<element> class names.

Fig1_Pre5_apexirdIds

Figure 1 – Pre-APEX 5 apexir_ Ids

Fig2_Axp5IrIds

Figure 2 – APEX 5 STATIC_ID_ Ids and a-IRR- Classes

The APEX 5 pattern is readily visible:  apexir_<element> ids are now renamed to STATIC_ID_ <element>, where STATIC_ID is the static id of the IR region, whether it was declared by the developer or assigned by APEX, in which case it has the format R123456789012345.  It is much easier to understand the underlying ID structure, especially when there are multiple IRs on the page, when declared static ids that make sense are used. There is a Static Id attribute on every IR – in earlier versions it was most often left blank. With APEX 5, the Static Id is important in passing filters to specific IRs, and in the structure of the IR itself.

JavaScript Changes

Pre-APEX 5, the APEX IR JavaScript is contained in the file widget.interactiveReport.js. Post APEX-5, the APEX IR JavaScript is contained in the file widget.interactiveReport.js.  The similarity ends there…. well, almost.  Reviewing the two files, one recognizes the same functions – actually widget methods – that correspond to the action menu actions, but the construction of the functions – the definition of the widget methods – is different, as is their implementation.

The Supported Way

The SUPPORTED way to influence APEX IRs is to “use the standard APIs”.  That includes apexrefresh, and the APEX_IR API. Let’s see what we can do with these.

apexrefresh is the documented, supported way to refresh APEX components from JavaScript. That includes refreshing APEX IRs. The syntax is simple:

apex.event.trigger(‘#myIRRegionStaticID”, “apexrefresh”);

For more specific IR settings, use the supported, documented APEX_IR API (https://docs.oracle.com/database/121/AEAPI/apex_ir.htm for APEX 5). To user most calls of APEX_IR, one needs to get the region id of your interactive report.   As simple example, the code sequence to programmatically reset an IR to its default configuration uses the APEX_IR.RESET_REPORT procedure: is:

DECLARE
  v_region_id APEX_APPLICATION_PAGE_REGIONS.REGION_ID%TYPE;
BEGIN
  SELECT region_d INTO v_region_id
    FROM APEX_APPLICATION_PAGE_REGIONS
   WHERE application_id = :APP_ID
    AND page_id = :APP_PAGE_I
    AND static_ic = ‘MY_IR_STATIC_ID’;

  APEX_IR.RESET_REPORT(
   P_page_id => :APP_PAGE_ID,
   P_region_id => v_region_id,
   P_report_id => NULL);
END;

Of course such code would be improved before production use by wrapping in a procedure, adding error catching and validations as needed for your situation.

In addition to the APEX_IR possibilities, there always the declarative RIR and CIR settings, discussed in Part I of this series in the Summer 2015 RMOUG  SQL> UPDATE.

So we can do all the basics using supported, documented means. But what about our more complex situations, where we have already strayed into unsupported territory with IR enhancements in earlier APEX versions?

The UNSupported Way

In APEX 4.2 and earlier, when there is always only one IR on a page, the widget is attached to the gReport element – the IR – and there is always only one gReport element on the page. One can inspect the JavaScript on the page and readily see calls to widget functions.

For example, inspection of the Search Column icon, shown in Figure 21, clearly shows the onclick action is a call to gReport.dialog2(“SEARCH_COLUMN”).

Fig3_apx42gReprt

Figure 3 – APEX 4.2 gReport.dialog2 Call on Search Column Icon

Further inspection of the IR JavaScript and HTML reveals that gReport.dialog2(‘parameter’) is the widget method for opening the ‘parameter’ dialog window, where ‘parameter’ values correspond to the Action Menu options.

Inspection of the APEX 4.2 Go button on the toolbar shows us the gReport.search call, shown in Figure 4.

Fig4_Apx42Go

Figure 4 – APEX 4.2 IR Toolbar Go Button gReport.search Call

With APEX 4.2, because we can clearly see how these two widget methods, gReport.dialog2 and gReport.search are used, we are actually quite confident in using them in our customizations, even when we know their direct use is unsupported. This is jQuery in APEX – we trust that this stuff is solid. 🙂

Well, in APEX 5, the familiar gReport.dialog2(…) and gReport.search(…) functions calls are not there.  In fact, the familiar gReport element is not there at all.  Figure 5 shows the IR Toolbar Go button HTML in APEX 5. No gReport in site – no jQuery is visible here.

Fig5

Figure 5 – APEX 5 IR Toolbar Go Button – No gReport.search!

Note again that all out-of-the-box APEX IRs will automatically use the new jQuery widgets, seamlessly.  The problem is, in APEX 5, all calls to the undocumented gReport stuff do not work. Any customization that makes gReport method calls, or extends the interactive report widget will need to be refactored.

So how do things match up? What replaces gReport? Let’s go back to some jQuery UI widget basics. The APEX team has used the jQuery widget factory to build (rebuild) the interactive report and action menu widgets (and many others within APEX 5).

The best reference I have found on jQuery UI widgets is on jQuery UI, How to Use the Widget Factory, at https://learn.jquery.com/jquery-ui/widget-factory/how-to-use-the-widget-factory/  If you are not familiar with jQuery widgets and the jQuery widget factory, this how-to article will help understand jQuery widgets in general, which will help you understand how the APEX interactive report and action menu widgets work.

Note: If you are not familiar with jQuery and  jQuery UI widgets, then seriously consider NOT making any unsupported customizations to the APEX IR widgets, at least until you learn more and become entirely confident in your ability to support yourself and your code.  This post does not contain sufficient information to make your first customization attempt.

The best reference for explaining how the APEX 5 IR widget works is by John Snyder, at http://hardlikesoftware.com/weblog/2015/05/12/apex-5-0-interactive-report-customization/.

Armed with the information in these two articles, we can figure out our APEX 5 IR widget code.

Widgets are attached to DOM elements.  Widget methods are the functions that define what the widget does – the actions. Methods prefixed with _ are private methods. Every widget has an options method that lists the options (attributes).  To view all options, use the option keyword:

$(selector).widgetName("option");

To view a specific option, simply state the option:

$(selector).widgetName(“option”, "optionname");

Adding a second parameter sets the value of the option:

 $(selector).widgetName(“option”, "optionname",100);

The basic call to a widget method takes the format

$(selector).widgetName("method");

If the widget method has parameters, add the parameters after the method name:

$(selector).widgetName("method", “param_1”, “param_2”);

Now let’s apply that context to our APEX IR.

The key information John gives us is that in APEX 5, the IR widget appends “_ir” to the static id of the IR, and “_actions_menu” to the actions menu widget .  So if my IR has a static id of DEMO_IR, we know the ids for the IR widget and for the IR actions menu.  To view all the options for these widgets, in the Console window use these commands:

$(“#DEMO_IR_ir”).interactiveReport(“option”);

to show all the IR widget options, and

$(“#DEMO_IR_actions_menu”).menu(“option”);

to show all the action menu options.

There are quite a few options for both – to learn more about the IR and action menu widgets, definitely run these jQuery commands in your browser Developer Tools Console window.  View the options, then experiment with calling some of the options. The IR widget options correspond to the IR attributes.  The menu widget options correspond to the IR action menu options, and the iems correspond to the menu actions. These will be familiar to developers who are familiar with IR settings.

The IR widget options include: actionsMenu, afterRefresh, aggregate, chart,columnSearch, compute, controlBreak, fixedHeader, flashback, groupBy, help, highlight, and more. Those familiar with IR action menu settings will recognize these as IR attributes, including action menu settings.

The action menu options include 15 items, with id’s,  like irSaveReport, irSaveDefault, irReset, irDownload and irNotify. (There are more – inspect for yourself!)

Following John’s blog, and experimenting with various options in the Console, I was able to find these interesting things:

$("#DEMO_IR_ir").interactiveReport("option","currentRowsPerPage");

returns the current rows per page setting.

 $("#DEMO_IR_ir").interactiveReport("refresh");

refreshes the IR.

$("#DEMO_IR_actions_menu").menu("find","irDownload");

returns the irDownload object.

[$("#DEMO_IR_actions_menu").menu("find","irDownload").action();

invokes the action method of the irDownload object – it opens the IR Download dialog. Translated, it finds the irDownload object of the DEMO_IR interactive report actions menu and calls its action method.

I leave the rest to your own experimentation. Clearly we are not totally in the dark here, once we know how the widgets work.

Note: The IR static id is optionally declared by the developer. If a static id is not declared, APEX assigns a long ugly one – therefore, it is best to assign a static id when one plans on using referencing it in JavaScript.

It is unclear at this point whether and when a JavaScript API will be provided to standardize and facilitate calls to the IR functions – particularly those for Reset, pagination, and Search options. Talks at KScope15 suggested a future release of an IR API is likely, but no hint of what or when. Until then, remember that customizations that do not use standard APEX APIs are not supported.

As in earlier APEX versions, developers who customize the APEX IR JavaScript widget are in unsupported territory. &amp;nbsp;Which does not mean altering the IR widget or using its calls directly cannot be done, it means that doing so is not supported, and will likely not upgrade smoothly or at all to future versions. (Sound familiar?)

Note: In earlier APEX versions, there was less declarative JavaScript capability and therefore more developer customizations. As APEX advances and incorporates more declarative JavaScript, it is &amp;nbsp;more important to stay within the standard APIs when making customizations, to avoid difficulties when upgrading.

A few code comparisons will illustrate further emphasize the differences between the APEX 4.2 widget and the APEX 5 widgets.

The pre-APEX 5 RESET function is:

/**
* Reset current&amp;nbsp; worksheet report to initial state
* @function
* */
this.reset = function() {
that.action('RESET', false, false, false);
};

The APEX 5 RESET function is a private method:

/**
* Reset current worksheet report to initial state
* @function
* */
_reset: function() {
this._action( "RESET" );
},

Not exactly the same, but close, and note the APEX 5 private method.

The pre-APEX 5 Search function is:

/**
* Runs the basic search functionality of the worksheet.
* @param {String} [pThis] if set to SEARCH check
* @param {Number} [pRows]
*
* */
this.search = function(pThis, pRows) {
var lSearch = that.item.search();
var lSearch_Col = that.item.search_column();
var lReport = $v('apexir_REPORT_ID');
var lTemp;
if (pThis='SEARCH') {
if (pRows) {
that.get.addParam('p_widget_num_return', pRows);
} else {
if ($x('apexir_NUM_ROWS')) {
that.get.addParam('p_widget_num_return', $v('apexir_NUM_ROWS'));
}
}
}
if ( apex.item( lSearch ).isEmpty() ) {
that.get.AddArrayItems2($x_FormItems('apexir_TOOLBAR'),1);
that.pull(lReport);
} else {
if (pThis='SEARCH') {
//lTemp = [$v('apexir_CURRENT_SEARCH_COLUMN'),'contains',$v(lSearch),$v('apexir_NUM_ROWS')];
that.get.AddArrayItems2($x_FormItems('apexir_TOOLBAR'), 1);
pThis = 'QUICK_FILTER';
} else {
lTemp = [this.current_col_id, '=', $v(lSearch)];
pThis = 'FILTER';
that.get.AddArray(lTemp,1);
}
that.action(pThis, 'ADD');
}
$s(lSearch, '');
};

The APEX 5 SEARCH function is a private method:

/**
* Runs the basic search functionality of the worksheet.
* @param {String} [pThis] if set to SEARCH check
* @param {Number} [pRows] Optionally set to control the number of rows displayed, needs to
* be done with the searc because the user could enter a new search, then select the rows
* which would issue the search
*
**/
_search: function( pThis, pRows ) {
   var lData, lFArrays,
   o = this.options,
   lSearch = this._getElement( "search_field" ).val();
   // If pRows passed, this has been changed and the new value used, but only allow if 
   // either actions menu
   // row select, or search bar row select is enabled
   if ( pRows && ( o.rowsPerPage || o.rowsPerPageSelect ) ) {
       o.currentRowsPerPage = pRows * 1;
   }
   lFArrays = this._utilGetFormElAttributes( this._getId( "toolbar_controls" ) );
   lData = {
     f01:    lFArrays.ids,
     f02:    lFArrays.values
   };
   if ( lSearch === "" ) {
     this._pull( null, this.reportId, lData );
   } else {
     this._action( "QUICK_FILTER", lData );
   }
  },

The APEX IR interactiveReport widget _get  function controls all the functions of the interactive report. The complete widget.interactiveReport.js.5.0.0.00.NN can be inspected from its location:

&amp;lt;host server and port&amp;gt;/i/libraries/apex/widget.interactiveReport.js?v=5.0.0.00.NN 

Where the <host server and port> are the http/https and host server name and port of your APEX installation, and NN is the exact version number in your APEX 5 installation’s widget.interactiveReport.js library.

The above code excerpts are not meant to be any sort of how-to – their purpose is only to illustrate that there are differences and any developer who has pre-APEX 5 customizations that rely on the pre-APEX 5 form of widgetinteractiveReport.js will need to review their code carefully and refactor when upgrading to APEX 5.

gReport to Widget Method?

So, what to do with all those unsupported gReport calls?  Refactor to use the APEX 5 widget calls and remain unsupported? I cannot tell you.  Perhaps more will be revealed in APEX 5.1 ?

To Risk to Not to Risk?

A comparison of the main functions of the pre-APEX 5 and APEX 5 interactive report JavaScript is presented in Table 2 of the APEX 5 Upgrade Cheat Sheet, in the Appendix. This comparison alone is not very helpful – it simply illustrates that a similar, but not exact function/widget method exists for each of the APEX IR actions. Developers that have customizations that rely on the old IR JavaScript must review each  function carefully and update their code accordingly.

Again, it is not expected that customized use of the IR JavaScript functions will be supported. Developer beware …

The recommendation is to use dynamic actions that call the APEX_IR API functions and procedures to achieve the desired results.  This method mmay mean more coding, but this approach, using the APEX dynamic actions and the published APIs – will be supported going forward.  Direct use of the IR and action menu widget methods is not supported.  IF you go this way, be prepared to refactor (again) going forward.

Upgrade (refactor) Example

The following simple example demonstrates the changes needed in an existing dynamic action to work successfully in APEX 5.

This simple Execute JavaScript dynamic action changes the background color of a row based on a certain value in the FLAGS column. Yes, this simple highlight could have been done with a pre-set IR Highlight action, however, to not clutter the control panel, to prevent users from editing this affect and for other business reasons this appearance change was done via JavaScript. (This dynamic action was one of several on the IR, each of sufficient complexity that it was not practical to implement all of them as IR actions).

var rows = $('table.<strong>apexir_WORKSHEET_DATA</strong> tbody tr:gt(0)');
rows.each(function(idx)  {
var Flags = $(this).children("td[headers=<strong>'FLAGS</strong>']").text();
if( Flags == 'Outlier')
{
$(this).children("td").css("background-color","#FCF067");
}
});

Simple enough, but this does not upgrade to APEX 5 because the apexir_WORKSHEET_DATA id no longer exists, so the table.apexir_WORKSHEET_DATA  search returns nothing. In fact, in APEX 5, this dynamic action does nothing at all.

The changes needed to make this dynamic action work in APEX 5 are:

  1. Replace the apexir_ component with its corresponding APEX 5 irr- component
  2. Ensure the column name/alias is in fact the APEX 5 column id.

To find the APEX 5 column id, use browser developer tools to inspect the column in question. The column id will be in the format C999999999999 by default, and will be the column alias when there is a clear column name..

The updated code looks like this:

var rows = $('table.<strong>a-IRR-table</strong> tbody tr:gt(0)');
rows.each(function(idx)  {
var Flags = $(this).children("td[headers='<strong>FLAGS</strong>']").text();
if( Flags == 'Outlier')
{
$(this).children("td").css("background-color","#FCF067");
}
});

And in fact the updated code highlights outlier rows, as desired, as shown in Figure 6 (below)..

Fig6_ColorCodedIR

Figure 24 – APEX 5 IR Dynamic Action Updated

The above highlight-row dynamic action upgrade is a very simple example. Your examples may be as simple, or very much more complex. The time to refactor will of course depend on the amount and complexity of the customizations to be upgraded. In this author’s case, the upgrade was straightforward – accomplished in less than two hours including testing, for the series of 6 moderately complex dynamic actions on the page. Your mileage may vary.

APEX 5 IR Upgrade Cheat Sheet

As promised, this post includes a “cheat sheet” for mapping pre-APEX 5 CSS and Ids to their APEX 5 equivalents. The cheat sheet is included in the appendix. Note that a cheat sheet is just a guide – it does not replace doing your homework and learning your subject matter. If you have read the above sections, you have all the information you need to map out your refactoring – you won’t need a cheat sheet.

Every developer should inspect their code and their particular interactive report in APEX 5 and validate their own mappings. The reason is, each interactive report may be configured differently. Different configurations will incur different combinations of IR class and id settings.  This author’s recommendation is to use the enclosed cheat sheet as a guideline in beginning your upgrade work, but always always inspect and double-check the mappings in your specific IR and in your specific APEX 5 environment.

Summary

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. Part 1 of this series covered the APEX 5 new and enhanced features for end users.  For developers, the APEX 5 IR construction changes are more significant as the underlying architecture has completely changed. Developers who have done customizations on IRs in previous APEX version may need to refactor their  code to follow the new APEX 5 IR structure and widget methods. With APEX 5, the overall power of APEX IRs has increased, but when it comes to customizations, some upgrade work may be necessary.

References

Manually Refreshing APEX Components
http://docs.oracle.com/cd/E59726_01/doc.50/e39147/extend_app002.htm#HTMDB30267

APEX 5.0 Interactive Report Customization, John Snyders
http://hardlikesoftware.com/weblog/2015/05/12/apex-5-0-interactive-report-customization/

jQuery UI: How to Use the Widget Factory
https://learn.jquery.com/jquery-ui/widget-factory/how-to-use-the-widget-factory/

The APEX 5 APEX_IR API Documentation

https://docs.oracle.com/database/121/AEAPI/apex_ir.htm

Appendix

The APEX 5 IR Upgrade Cheat Sheet

As I mention above (or was that in Part I?), one really does not need a cheat sheet for this upgrade – the more important thing is to note the pattern of class names and CSS element names in the new APEX IR structure:

apexir_<element> ids are now renamed to STATIC_ID_ <element> ids

But for those who want one, the Cheat Sheet I put together over a year ago now is at then end of the complete document, in the link below. No doubt I missed some details – but go for the pattern, THINK and figure things out. You’ll do fine.  If you get stuck, contact me 🙂

Complete APEX IR Part II, w Appendix

Note: This complete article is in the RMOUG Fall 2015 SQL>UPDATE. The online newsletter can be accessed here.

Honored to be an Oracle Developer Choice Awards – Application Express Finalist

I am honored to be a finalist in the Oracle Database Developer Choice Awards, in the Oracle Application Express category.

I am most impressed with my fellow finalists – amazing devotion to helping the APEX community – and humbled to be included in such a crowd.  Congratulations to all!

Please check out the Oracle Database Developer Choice Awards main page, and check out the finalist in all categories:

Most important – Visit the Developer Choice Awards pages and Vote!

If you have comments to share, there is space to do so here, or on the page for each finalist.

Many thanks to the Developer Choice Awards team ~ I am most grateful and honored by the nomination and finalist status.

My Favorite APEX Applications

I have been fortunate to have clients that allow me to work with Oracle Application Express. Fortunate for them, because the tool enables me to produce production applications faster and lighter-weight (pure HTML, no download, less system-administration overhead, less IT maintenance). Fortunate for me, because I enjoy working with the tool.
In general, I enjoy learning; I enjoy the challenge of a new puzzle, of a new tool to learn the ropes of. I believe APEX is one of those tools where one can do just about whatever one needs with it, by getting a bit clever in telling APEX how to produce a page. The cleverness comes in designing the overall application flow, in designing the APEX page flow, in figuring which components ot use where, and in figuring how best to implement business rules. Yes, in general this is the challenge with any web application written in any tool. With APEX, it’s just a bit more fun.

~ My Favorite Applications

I am happy to have written many APEX applications through the years, but some stand out for various reasons of technical challenge, large or small, and personal interest. The names are modified to protect client interests.

Days at Sea

– A fairly simple APEX app on the surface, this application got DAS calculations correct after several high-visibility failed attempts by other more expensive contractors) and included a flexible, extensible, system to allow the application to declaratively grow and adapt to changing regulations. The PL/SQL behind the scenes and APEX to pull all into simple, maintainable interfaces made for delivery on time. Key factor in this project was the team – 3 persons put their heads together and made it work with APEX. A big win.

Interlab Quality Control

– Consolidation of laboratory statistics into an enterprise database,plus a series of complex quality control statistics, presented in a series of customized reports and charts. Moving this medical corporation’s quality statistics from MS Access/Excel – and awesome app in itself – is a big win for the client. Additional phases in progress and more reports and charts are developed. Hmm. D3 or FOT in the future?
Key fun features: XSL-FO templates for color-coded PDF output that matches the color-coded online interactive reports, some neat data loading challenges, and an automated scatter-plot slide show to replicate (improve on) an in-house quality display.

Proficiency Management

– A system for collection, review, tiered approvals and notification of proficiency management information. Automated data loads, automated notifications and a complex set of business rules made development fun and deployment satisfying.

Critter Information Database

– A true migration from a fully-licensed Discoverer installation to BI in APEX. Still in use today, with another migration of a sister Discoverer installation in progress this year. APEX 5 makes this migration so much easer, and the resulting app will have more BI features at less development cost. Non-confidential and confidential users and authorizations schemes in APEX and in the database structures contorl who sees what. Key fun features: PL.SQL Pivot queries and interface customizations to get a BI “look” to the tool.

Permitting and Landings Applications

– This series of applications, completed over several years, marked migration for Oracle Forms and Reports to APEX, enabling the agencies to retire costly Forms and Reports licenses, streamline deployment (no more Java Applet!) and reduce resource costs for development and maintenace. Now these same agencies are upgrading to mobile application and adding more complex features – a positive example of long-term planning and a successful complete migration to APEX and mobile technologies.

Inspection Database

– A set of two applications, one for automated loading of standards data from various regulating agencies, one for collection, consolidation and edit of inspection reports. Key fun features Online editable “report” to replace MS Word document edits, and XSL-FO templates for PDF version of the final report. Fun in progress on this project!

~ It Takes a Team

It’s not all me ~ every great application has a great team behind it. Along the way I have learned immeasurably from:
– The APEX community – I feel the most open and sharing user community there is,
– The fabulous clients I have worked with (rather than for) through the years, and
– The magnificent managers I have been lucky to have who guided me, challenged me to grow, and had faith in me when I did not have it in myself.