I’ll be hosting another free webinar next week titled Oracle Application Express Plug-ins. The webinar will run on Wednesday, July 28th from 1pm to 2pm EDT. Click here to register.

Abstract

If you use Oracle Application Express then you have probably heard of "plug-ins" by now. But you may still have some unanswered questions, such as:

• What exactly is a plug-in?
• What can plug-ins do for me?
• What plug-ins are available?
• How do I install and use plug-ins?

These questions and more will be answered during this webinar. This will be a high level introduction to plug-ins during which we will demonstrate how plug-ins can save your organization time and money by easing development efforts. Attendees will receive a copy of the next SkillBuilders plug-in – one week before its general release to the public.

Audience

Anyone interested in Oracle Application Express.

Language

English

• Create a page with a report region, ensuring the page item filters are referenced in the SQL.
• Create the page items for filtering.
• Create the dynamic action to fire whenever any of the page item filters change value, to refresh the IRR.
• Define the IRR to save these items values in session state after it is refreshed.

• Create the page
1. Within an application, click the 'Create Page' button.
2. Select 'Report', click 'Next'.
3. Select 'Interactive Report', click 'Next'.
4. For 'Page Attributes', enter 'Employee Report' for 'Page Name' and 'Region Name'. Importantly, you must also ensure that the 'Region Template' is set to some template that contains the #REGION_STATIC_ID# substitution string, otherwise this example will not work (dynamic actions need this ID to be able to perform the refresh). Most of the new themes now default to an appropriate template when creating new IRRs (as defined by the new Theme 'Region Default' for 'Interactive Reports'). However if you're using an old theme then you may need to select an appropriate template (try 'Region without Buttons and Title'). Also make a note of the page number (I'll assume page 1 for these steps) and click 'Next'.
5. For 'Tab Attributes', leave as default and click 'Next'.
6. For 'Enter a SQL SELECT Statement', enter the following query (referencing the page number noted in step 4 when defining the item names) and click 'Next':
   
   
   

   select e.empno,
   e.ename,
   d.dname,
   e.job,
   e.hiredate,
   e.sal,
   e.comm
   from emp e,
   dept d
   where e.deptno = d.deptno
   and e.deptno = nvl(:P1_DEPTNO, e.deptno)
   and e.job    = nvl(:P1_JOB, e.job)

7. Click 'Finish'.
8. For purposes of this example, we're going to switch off the standard 'Search' bar to keep things clean. You can do this by right clicking on the 'Employee Report' in tree view and selecting 'Edit Report Attributes'. Then in the 'Search Bar' region, select 'No' for 'Include Search Bar' and 'Apply Changes'.
If you now click 'Run Page', you'll see the basic report page as been created. We now need to add the page item filters.
• Create the page items
1. Go to edit the page. We'll create these items in a separate 'Quick Filters' region above the IRR. In 'Tree View', right click on the region position where the IRR has been created (by default this is 'Body (3)' and select 'Create'.
2. Select 'HTML' and click 'Next'.
3. Select 'HTML' and click 'Next'.
4. For 'Title', specify 'Quick Filters', for 'Region Template' select 'Report Filter - Single Row' and amend the 'Sequence' to be say 5, so that this appears before the IRR on the page, click 'Next'.
5. For 'Region Source', leave blank and click 'Create Region'. This has created a blank region, that will be the container for our filter items. We'll create 2 filters, to filter the employee report by the job and department columns respectively.
6. In 'Tree View', right click on the 'Quick Filters' region and select 'Create Page Item'.
7. Select 'Select List', click 'Next'.
8. For 'Item Name', enter 'P1_DEPTNO', click 'Next'.
9. For 'Item Attributes', accept defaults and click 'Next'.
10. For 'Settings', accept defaults and click 'Next'.
11. Ensure 'Display Null Value' is 'Yes'.
12. For 'Null Display Value' enter '- Show All -'.
13. Leave 'Null Return Value' leave as blank, this will be treated as a true null by APEX.
14. For 'List of Values Query', enter the following query and click 'Next':
    
    
    

    select dname d,
    deptno r
    from dept
    order by 1

15. For 'Source' accept defaults and click 'Create Item'.
16. Now for the job select list. In 'Tree View', right click on the 'Quick Filters' region and select 'Create Page Item'.
17. Select 'Select List', click 'Next'.
18. For 'Item Name', enter 'P1_JOB', click 'Next'.
19. For 'Item Attributes', accept defaults except for the 'Begin on New Line' attribute. We want to set this to 'No', so that the filters appear on the same line in the 'Quick Filters' region. Click 'Next'.
20. For 'Settings', accept defaults and click 'Next'.
21. Ensure 'Display Null Value' is 'Yes'.
22. For 'Null Display Value' enter '- Show All -'.
23. Leave 'Null Return Value' leave as blank, this will be treated as a true null by APEX.
24. For 'List of Values Query', enter the following query and click 'Next':
    
    
    

    select distinct job d,
    job r
    from emp
    order by 1

25. For 'Source' accept defaults and click 'Create Item'.
If you run the page now, you'll see all the basic page components are created, the regions and the items, but selecting different values from the filters has no effect. We now need to bring these to life.
• Create the dynamic action
1. Go to edit the page. Right click on the 'Dynamic Actions' tree node and select 'Create'.
2. Select 'Advanced' for the 'Implementation'. We need 'Advanced' here because 'Standard' only supports the 'Show', 'Hide', 'Disable' and 'Enable' actions.
3. For 'Name', enter 'QUICK FILTER REFRESH', click 'Next'.
4. For 'When', leave the event as 'Change' and for 'Item(s)', enter 'P1_DEPTNO,P1_JOB'. Leave 'Condition' as 'No Condition'. These selections define the dynamic action will fire whenever either the department or job select list's value changes. Click 'Next'.
5. For 'Action', select 'Refresh' under the 'Component' category in the select list. The 'Refresh' action currently supports IRRs, classic reports, all item types with cascading LOV support and may also support item or region plug-ins, depending on whether the plug-in author has coded the plug-in to support this (the plug-in documentation should state if this is supported). Charts in APEX 4.0 are not yet supported.
6. Ensure the 'Fire on Page Load' checkbox is unchecked, there is no need to refresh the report when the page loads, it's already fresh. Click 'Next'.
7. On the 'Affected Elements' page, we'll define what will be refreshed. Select 'Region' from the 'Selection Type' select list and 'Employee Report' from the 'Region' select list. Click 'Create'.
If you now run the page, you'll notice that this still isn't working as expected. Actually, the dynamic action is firing, the report is being refreshed, but it is not being scoped by the filter selection. The problem is the values for the filter page items are not being saved to session state and are therefore not set when the report's SQL is executed. This is easy to fix.
• Define the IRR to save these items values in session state after it is refreshed
1. Right click on the 'Employee Report' and select 'Report Attributes'.
2. Go the 'Advanced' and for 'Page Items to Submit', enter 'P1_DEPTNO,P1_JOB'. Click 'Apply Changes'.

1. Refresh - Showing an alternative approach for user's to select their 'Saved Reports' in IRRs. Instead of using the default select list in the search region, this has been switched off in favour of a separate report on the left of the page containing the saved reports available to the current user. This report also shows a tooltip for the report description (if defined), when hovering over the report name. The dynamic action is used to refresh this saved report list, so if a user saves a new report or deletes a report, the report list is updated. Again this is all via Ajax and does not require any manual JavaScript coding.
2. Refresh 2 - Showing how report row deletion can be handled via Ajax with dynamic actions. This example does require a couple of places within dynamic actions where a line of JavaScript is required.

Objectivity is in the Eye of the Beholder

Everything one encounters in life is subjective to our particular point of view. Be it social, political, theological, or theoretical; all people tend to see things different from others. Likewise, organizations of people tend to operate under the same dictionary as others with slightly to more different definitions. Take the term ‘Enterprise’, for instance.

When thinking of the idea of Enterprise software or software development most people might assume that the application would be produced using some really complicated development tools like .NET or JAVA. At the time when I encountered Application Express (HTML-DB at the time) I was thinking roughly the same thing. A few years later Oracle published a slide deck that show many things, but the one slide that catches the most attention when I show it is this one : (Slide 11)

This graphic seemed to capture what many developers, including myself, had been saying for a while. APEX is a development platform that is accessible to all levels of business application development. With one exception that many of us felt very strongly that APEX deserved to be shown as penetrating further into the Enterprise development portion of the business.

APEX can run with the big kids, too

Why, or better, why not? I hadn’t truly gotten it until I had a conversation with a few members of the development team a few months ago. The reason for this disparity seems to exist in the difference in dictionary between what is called Enterprise Software Development and what many businesses call Enterprise Systems.

Depending on your definition APEX is, or is capable of producing, an Enterprise system. Many companies I have encountered express this as an application or series of related applications that service multiple business units and/or multiple business locations or sites. Being that it is a multi-target application development toolset APEX fits this definition very well in both the development tools and the applications it can produce.

Where it comes to Enterprise Software Development, I have heard, APEX doesn’t fit the bill entirely. Since this methodology apparently requires multiple developers to be working on multiple units of the whole system in order to complete the final product, with source control and documentation. In the previous post I spoke about methodologies and the fact that given the right amount of control any development toolset can succeed or fail equally. Either way, the newer enhancements to APEX seem to suggest that it fits this definition more today than it did even six months ago.

There is but one other definition of Enterprise that comes to mind and it is a really poignant one. It is that large companies, the ones that Enterprise Software is usually targeted toward, are rarely enterprising anymore. They don’t take the risks that make up this definition. They have resources that make buying a Commercial (“Enterprise”) Solution for everything possible and therefore they are more stagnant, less flexible and less open to change. This is a very good spot to be for the APEX platform. It sports the power and security of an Oracle Database. As a multi-target solution it can produce a suite of business applications for the business that can fit just about every conceivable need and can be supported by a minimum of personnel. It enables the organization that employs it to concentrate resources into common effort that can free up other resources to work forwarding the business rather than supporting excessively diverse IT systems.  While doing so with a lower cost of ownership than other tools.

In the end, I find that APEX fits my definition of being suitable and scalable to any Enterprise. Other opinions may vary.

Cheers! :”)

Welcome Version 4.0

Oracle Application Express (APEX) 4.0 arrived this month with anticipation and fanfare equivalent to a really large family reporting on a new grandchild being born. Having had a bit of time to explore and experiment with the new version I am more than impressed with this new and large step forward in the product. After a few weeks of beating up these new enhancements it has become clear that this software is going to be a force to be reckoned with for some time to come.

This latest release has brought some old conversations back to the surface in the many political, social, and techno-theological aspects of the business and IT organization that employs this software. (or finds that it is being employed ;”)

To RAD or not to RAD

Rapid Application Development (RAD) has been a subject of contention in the business realm for a few years now. It’s not to say that RAD is bad; just that it takes a certain understanding that it can and will get messy, if it is not controlled properly. Not all, in fact most, organizations should not use this methodology of software development because of this fact. (insert ‘popular OS’ joke here)

As a result of personal or organizational experience with RAD, many IT Architects shun any tool from the IT portfolio that has been remotely associated with RAD. The issue here is that RAD is a methodology not a programming toolset. One can, conceivably, use RAD with .NET and JAVA to create the same mess that they can get into with any other application development toolset available.

APEX has fallen prey to this misconception before and likely will again until some of the detractors actually sit down with it and understand that normal, organized, documented, and controlled application development can be done with it to produce functional, secure, scalable, and stable applications with it. APEX is not RAD but it is Radical in that it allows a team to use their own methodologies in the creation of a business application. In my experience APEX has the overwhelming advantage of producing the application quicker than the other options available.

Be it RUP, SDLC, RAD, Agile, or any other methodology, a toolset has to be used to create the application, but that toolset is not the methodology and likewise.

col kapital format 999999990D00
col zinsen format 999999990D00
col tilgung format 999999990D00
col restwert format 999999990D00

with konstanten (datum, kapital, zinssatz, rate, rhythmus) as (
  select 
    trunc(sysdate, 'MONTH') datum,
    100000 kapital,
    5 zinssatz,
    600 rate,
    1 rhythmus
  from dual
), 
tilgungsplan (datum, kapital, zinsen, tilgung, restwert) as (
  select 
    to_char(k.datum) datum,
    k.kapital, 
    k.kapital * k.zinssatz / (100 * (12 / k.rhythmus)) zinsen,
    k.rate - k.kapital * k.zinssatz / (100 * (12 / k.rhythmus)) tilgung,
    k.kapital - ( k.rate - k.kapital * k.zinssatz / (100 * (12 / k.rhythmus))) restwert
  from konstanten k
  union all (
   select 
    to_char(add_months(to_date(s.datum),k.rhythmus)) datum,
    s.restwert kapital,
    s.restwert * k.zinssatz / (100 * (12 / k.rhythmus)) zinsen,
    least(
      k.rate - s.restwert * k.zinssatz / (100 * (12 / k.rhythmus)),
      s.restwert
    ) tilgung,
    s.restwert - ( 
      least(
        k.rate - s.restwert * k.zinssatz / (100 * (12 / k.rhythmus)),
        s.restwert
      )
   ) restwert
   from konstanten k, tilgungsplan s
   where restwert > 0 
  )
)
select * from tilgungsplan
/