APEX 5.1 quick tipp: select * considerations for remote views

Many developers don’t like select *. I’m a big fan of it, but there are some edge cases where one needs to be careful. I don’t want to discuss pros and cons here in this thread. This is just to point out a few areas where useing select * gives not the advantage that one could expect.

I like to program select * in multiple scenarios. Usually to express, that I want to access (almost) every column from that table or view. I’m too lazy to write down every single column name.

A typical APEX example would be to edit a table via an Interactive Grid.

Scenario setup

Imagine a remote database with a table MYTAB. We access this remote database via a database link from a local database with APEX installed. In the SQL workshop we have a view that was originally created as

create view myTab_remote as
select * from mytab@dblinkname;

That advantage of that view is, that we create a layer where the name of the DB link is used. Using this layer we don’t need to know the DB link name inside our APEX application. Which makes it easier to switch or change the link.
Also the APEX framework is now able to use the oracle data dictionary for that view. Newer APEX versions have improved capabilities to work with remote tables. But here I’m talking about version 5.1.

Our Interactive Grid uses a fairly simple query.

select * from myTab_remote
where parent_id = :P1_MASTER_ID

Modifications

So what happens when a column is added on the remote database to our table, for example the column price_unit.

alter table myTab
add price_unit varchar2(5);

The APEX application is still working, no issue happend there. It does not automatically has the new column.

In general this is good. We have a robust appliaction that does not break, simply because a new column was added (it would break if a column is dropped however).

Certainly the column was added with some intention in mind. We also want to show and edit this new column in our Interactive Grid.

How to add this new column to our application?

First step is to update the view. One might think this is not necessary, because we created the view using select *. Unfortunatly that is not how Oracle works, especially not via a database link.
When investigating the view via the SQL tab in the SQL workshop, we find that the DDL is different than what one would think.

CREATE OR REPLACE FORCE VIEW "MYTAB_REMOTE" ("ID", "PARENT_ID", "COL_A",  "COL_B",  "PRICE") AS 
   select  "ID", "PARENT_ID", "COL_A",  "COL_B",  "PRICE"  from MYTAB@DBLINKNAME
 /

The column list was expanded into the select statement and also captured as the column list name of the view itself. The new column price_unit is missing.

To add the new column we could again simply create the view. A normal recompile is not enough. Btw. this also happens with views over local tables.

create or replace view myTab_remote as
select * from mytab@dblinkname;

Hint: never use the FORCE keyword for development work. It may hide important error messages.

After that we can select from the view and will see the new column. However it is still missing from the Interactive Grid.

The second step is to push APEX into reevaluating the column list for this view.

Simply validating the SQL query will not be enough. I found the following sequence of steps do work.
First add an alias and a new dummy column to the query.

select m.*, 'x' new_dummy_col
from myTab_remote m
where m.parent_id = :P1_MASTER_ID

Validate it and press OK. This results in two new columns added to the grid column list. Then enter the SQL query again and remove the dummy column.

select m.*
from myTab_remote m
where m.parent_id = :P1_MASTER_ID

Result is we now have the new column in our list.

The third and last step is to add this column to our default reports.

The new column is at the very end of the column list. On an IG it is shown, on an IR it is not shown by default. In any case we want to move this new column via the Actions > Columns menu to the correct position. And then save the public report Actions > Save Report. This should be done for each public report in that Grid.

Conclusion

Using select * in a APEX Interactive Grid scenario did not lower the maintenance needed when adding new columns to the base table. The second step even might be slightly confusing for many developers. So it might be a good idea to use a complete column list there instead of select *.

Site note: Using select * in other scenarios like inside PLSQL indeed does lower the maintenance when done right compared to an explicit full column list.

add JET 5.1/5.2 to APEX via CDN

How to use the newest Oracle JET version in APEX?

While writing this blog post the most recent version of Oracle JET is version 5.1.0. 5.2.0.

5.1 was an interesting version, since charts have been improved to accept an data provider attribute (http://www.oracle.com/webfolder/technetwork/jet/jetCookbook.html?component=chart&demo=dataProvider)

So let’s assume we want to use this version.

Some time ago I published how to integrate JET [2.2.0] into Apex 5.0. It needed quite some setup to do. Some steps probably could have been done in a more elegant way. Additionally see John Snyders recommendations Using Oracle JET from APEX which do eliminate some of the issues that I encountered. For example there is really no need to throw out hammer.js from the theme.

Meanwhile Oracle has made JET available via the Oracle Content Delivery Network (CDN). And this makes it possible to load the necessary JET libraries from this CDN instead of installing it locally to our webserver. Awesome stuff!

I tested this solution with Apex 5.1.1 (on premise) and with Apex 18.1 (on apex.oracle.com).

Which source files are needed?

We need three types of sources.

  • require to load and install additional libraries
  • the Oracle Jet stylesheets
  • various Oracle Jet javascript libraries

Some comments about those files.

Require.js already comes installed in your local Apex installation. It might be a different version, but still is an option to be considered. I only tested using require in the version that matches with Oracle Jet.

Oracle jet now has a file bundled-config.js which adds all of the jet libraries. This might be a bit too much, but it provides a quick and easy way to install JET. In case we only want to use a special visualization, this provides access to way more libraries than needed. I didn’t test if performance improves if I install only the necessary libraries for a special visualization. I hope that because of require, the performance difference would be minimal. Require only loads a file, when it is really needed. Inspecting the network traffic seems to support that notice.

Once those sources are installed to our page, we can copy any example from the Jet Cookbook and use it in our application. Be aware some cookbook examples use additional resources, like JSON data files. You need to copy and reference those too.

New page template

The three source files need to be added to the page. I suggest to create a new page template so that the logic can be reused for multiple pages.

Make a copy of the standard page template. I named it something like “Standard + Jet 5.1”. Then add a few lines to enable Oracle Jet 5.1.0 on this new template.

The two javascript files need to be added to the javascript file section.

https://static.oracle.com/cdn/jet/v5.1.0/3rdparty/require/require.js
https://static.oracle.com/cdn/jet/v5.1.0/default/js/bundles-config.js

page_template_javascript

As an alternative to the bundles-config.js file, we can install the whole set of the require config in the function declaration section:

//-----------------------------JET STUFF -----------------------------
// alternative source locations
//#APP_IMAGES#oraclejet/main.js
//#IMAGE_PREFIX#libraries/oraclejet/js
requirejs.config({
  // Path mappings for the logical module names
  paths: {
    'knockout': 'https://static.oracle.com/cdn/jet/v5.1.0/3rdparty/knockout/knockout-3.4.2',
    'jquery': 'https://static.oracle.com/cdn/jet/v5.1.0/3rdparty/jquery/jquery-3.1.1.min',
    'jqueryui-amd': 'https://static.oracle.com/cdn/jet/v5.1.0/3rdparty/jquery/jqueryui-amd-1.12.0.min',
    'ojs': 'https://static.oracle.com/cdn/jet/v5.1.0/default/js/min',
    'ojL10n': 'https://static.oracle.com/cdn/jet/v5.1.0/default/js/ojL10n',
    'ojtranslations': 'https://static.oracle.com/cdn/jet/v5.1.0/default/js/resources',
    'text': 'https://static.oracle.com/cdn/jet/v5.1.0/3rdparty/require/text',
    'promise': 'https://static.oracle.com/cdn/jet/v5.1.0/3rdparty/es6-promise/es6-promise.min',
    'hammerjs': 'https://static.oracle.com/cdn/jet/v5.1.0/3rdparty/hammer/hammer-2.0.8.min',
    'signals': 'https://static.oracle.com/cdn/jet/v5.1.0/3rdparty/js-signals/signals.min',
    'ojdnd': 'https://static.oracle.com/cdn/jet/v5.1.0/3rdparty/dnd-polyfill/dnd-polyfill-1.0.0.min',
    'css': 'https://static.oracle.com/cdn/jet/v5.1.0/3rdparty/require-css/css.min',
    'customElements': 'https://static.oracle.com/cdn/jet/v5.1.0/3rdparty/webcomponents/custom-elements.min',
    'proj4js': 'https://static.oracle.com/cdn/jet/v5.1.0/3rdparty/proj4js/dist/proj4'
  },
  // Shim configurations for modules that do not expose AMD
  shim: {
    'jquery': {
      exports: ['jQuery', '$']
    }
  }

});

If you want to use this option and Jet 5.2 then I suggest to look into the 5.2.0 bundles-config.js file, and copy the correct paths out of it.

page_template_function_declaration

Remember above code is not needed! It comes preconfigured in the bundle-config.js file. I offer it only as an alternative installation that gives a little more control over the libraries.

Finally we need to add some css. Add the link for the JET style to the CSS file section.

https://static.oracle.com/cdn/jet/v5.1.0/default/css/alta/oj-alta-min.css

And additionally you might want to add a little inline css. See the issues section for an explanation.

page_template_css

And finally use this new page template for your page.

Example result

And this is how using Oracle Jet 5.1/5.2 in Apex can look like.

JET_Dashboard_Sunburst
The source code for that chart can be found at the end of the post. Or go to the Jet Cookbook and try it our there. Did you notice, you can spin the whole wheel around?

Issues

There are some minor problems that I encountered. Especially some css issues because JET overwrites several of the Apex Theme css settings. Which is not good. I made a sample demo to show the issues. I only tested it with the red theme style, but I think the issues persist in all styles.
Theme Bug Demo

I do have css based workarounds for the issues I found so far. The solution is also demonstrated in the demo app. Here is the css code that can be added to the page or the template (css section).

.t-Header .t-Button--header, .t-Header .t-Button--header.is-active, .t-Header-logo, a.t-Header-logo-link{
    color:#fff
}

.t-Alert--horizontal .t-Alert-icon .t-Icon,
.apex-icons-fontapex .t-Alert--defaultIcons.t-Alert--horizontal .t-Alert-icon .t-Icon {
    width: 48px;
    height: 48px;
    line-height: 48px;
}

I think those issue are really bugs. And the Apex team and the Jet team can both be held responsible. The APEX side should have used a better css specificity for the header styles, the JET side should not set styles for things like a:visited for the whole html page.

And I believe there are more side effects. That is also the main reason why I would add JET only to specific pages. And why I would avoid mixing normal apex charts with newer version JET charts. Don’t mistake me, I made a few simple tests and it worked. I could create a region with a normal apex jet chart (apex 5.1) and combine it with a region using a CDN loaded JET chart (Legend + PictoChart). I just didn’t test it thoroughly enough to recommend it.

What is a CDN and is it safe?

https://en.wikipedia.org/wiki/Content_delivery_network

A content delivery network provides ressources, like in the JET case, javascript and css files, in a way that optimizes access times. So if a user accesses our application from Europe, the ressources will be delivered from a European server. If the user sits in America, an American server is choosen to deliver the files. This also frees up some bandwidth for our own webserver. After the first load, the browser usually caches the files.

If different applications all use the same remote ressource via CDN, the browser can reuse the cached files. This is especially interesting for the very common libraries, like JQuery.

But performance is not the only argument. With Oracle JET I find it way more convinient to load the newest JET version directly from Oracle instead of going through the hassle and install it to the local webserver. Often this involves using deployments scripts and documenting what exactly to do, so that in an enterprise environment, the same steps can be later made for the production server.

But is it safe to use? The most secure method is, if you download the js files from a trusted source (Oracle CDN). Then inspect each file, if there is any malicious code in it. Once you are sure it is safe, you install the files onto your own webserver and serve the ressources from there.

One of the most basic security features is to use HTTPS and not HTTP, especially when accessing remote ressources. This lowers the chance for man in the middle attacks essentially.

Especially the DNS lookup to the CDN server might be a performance drain. But in terms of web speed there are many things to consider.
Pros

  • Ease of installation
  • Performance boost for global user base
  • Browsers limit parallel loads from the same source. Loading additional ressources from a second source circumvents that issue.
  • Bandwidth moved from own webserver to CDN

Cons

  • Less control over the logic that is loaded – you need to trust the CDN provider
  • CDN provider could be down
  • For local users CDN will be slower than serving from local webserver

quick conclusion

Oracle JET via Oracle CDN is a good concept especially during a developement and testing phase. For on premise production usage you might want to consider to install Oracle JET to your local webserver and run it from there.

more

Here are a few links that I found interesting and that cover several other topics about using or not using a CDN.

updates and addendum

Oracle JET 5.2.0

Very recently Oracle JET 5.2.0 was released. I test it only very briefly. You just have to change the version number in the source file path.

Here is the official release schedule for the future Jet versions.

Sunburst Example

The following code is a direct copy from the Oracle Jet Cookbook.

Create a static region and add this as the region source.
Unfortunatly I encounter some issues when posting oj-tags here. The code is missing in the final view. Best way is to go directly to the source and copy the code from there. Everything inside the body tag is needed.

<div id="sampleDemo" class="demo-padding demo-container">
  <div id="componentDemoContent" style="width:1px;min-width:100%;">

    <div id='sunburst-container'>
        <div style="padding-left:16px;float:left;">
          Update values
        </div>
        <div style="padding-left:16px;float:left;">
          Update colors
        </div>
        <div style="padding-left:16px;float:left;">
          Add/Remove Node
        </div>
        <br style="clear:left;" />

        
        
    </div>


  </div>
</div>

Or copy the code from this screenshot
JET_Sunburst_html_source

Add this on the page level to the “function and global variable declaration”:


require(['ojs/ojcore', 'knockout', 'jquery', 'ojs/ojknockout',
    'ojs/ojbutton', 'ojs/ojsunburst'],
function(oj, ko, $)
{
    function SunburstModel() {
        var self = this;

        var colorHandler = new oj.ColorAttributeGroupHandler();

        var MA = createNode("Massachusetts", "0", getValue(), getColor());
        var CT = createNode("Connecticut", "1", getValue(), getColor());
        var ME = createNode("Maine", "2", getValue(), getColor());
        var VT = createNode("Vermont", "3", getValue(), getColor());
        var RI = createNode("Rhode Island", "4", getValue(), getColor());

        addChildNodes(MA, [
          createNode("Boston", "00", getValue(), getColor()),
          createNode("Springfield", "01", getValue(), getColor()),
          createNode("Worcester", "02", getValue(), getColor())
        ]);
        addChildNodes(CT, [
          createNode("Hartford", "10", getValue(), getColor()),
          createNode("New Haven", "11", getValue(), getColor())
        ]);
        addChildNodes(ME, [
          createNode("Portland", "20", getValue(), getColor()),
          createNode("Augusta", "21", getValue(), getColor())
        ]);
        addChildNodes(VT, [
          createNode("Burlington", "30", getValue(), getColor())
        ]);
        addChildNodes(RI, [
          createNode("Providence", "40", getValue(), getColor()),
          createNode("Newport", "41", getValue(), getColor())
        ]);
        var nodes = [MA, CT, ME, VT, RI];
        function createNode(label, id, value, color) {
          return {label: label,
                id: id,
                value: value,
                color: color,
                shortDesc: "<b>" + label +
                  "</b>
Value: " + value};
        }

        function addChildNodes(parent, childNodes) {
          parent.nodes = [];
          for (var i = 0; i &lt; childNodes.length; i++) {
            parent.nodes.push(childNodes[i]);
          }
        }

        function getValue() {
            return Math.round(50 + 100 * Math.random());
        }

        function getColor() {
            return colorHandler.getValue(Math.floor(Math.random() * 4));
        }

        self.nodeValues = ko.observableArray(nodes);

        self.valueButtonClick = function(event) {
            for (var i = 0; i &lt; nodes.length; i++) {
                if (Math.random() &lt; 0.4)
                    nodes[i].value = getValue();
                for (var j=0; j &lt; nodes[i].nodes.length; j++) {
                    if (Math.random() &lt; 0.4)
                        nodes[i].nodes[j].value = getValue();
                }
            }
            self.nodeValues(nodes);
            return true;
        }

        self.colorButtonClick = function(event) {
            for (var i = 0; i &lt; nodes.length; i++) {
                if (Math.random() &lt; 0.3)
                    nodes[i].color = getColor();
                for (var j=0; j &lt; nodes[i].nodes.length; j++) {
                    if (Math.random() &lt; 0.3)
                        nodes[i].nodes[j].color = getColor();
                }
            }
            self.nodeValues(nodes);
            return true;
        }

        self.nodeButtonClick = function(event) {
            if (nodes.length &lt;= 5) {
                var newNode = {
                  id: &quot;5&quot;, value: getValue(),
                  color: getColor(), label: &quot;New York&quot;,
                  nodes: [
                     {id: &quot;50&quot;, value: getValue(),
                        color: getColor(), label: &quot;New York City&quot;},
                     {id: &quot;51&quot;, value: getValue(),
                        color: getColor(), label: &quot;Albany&quot;}
                  ]
                };
                nodes.push(newNode);
            }
            else {
                nodes.pop();
            }
            self.nodeValues(nodes);
            return true;
        }
    }

    var sunburstModel = new SunburstModel();

    $(
        function()
        {
            ko.applyBindings(sunburstModel,
                    document.getElementById(&#039;sunburst-container&#039;));
	}
    );
});	

my favorite Apex 5.2 new features

Introduction

Apex 5.2 is still in early adopter phase 1. It looks as if there will be a second early adopter phase. And probably after that Apex 5.2 will be released.

The following statements are highly speculational. There are based upon my observations in the early adopter cloud version (apexea.oracle.com) and based upon various talks from Apex team members.

Remember: It is possible that some of the features that are already there or that are promised, will not make it into the final version.

New features doc/app

Create application features

The wizard to create a new application now has a blueprint functionality. This means during application creation we add standard modules to the application that are frequently requested.

Choose/Create Application Icon

This is part of the blueprint functionality. We can now choose from a very limited set of icons, adjust the color and we will get an svg in static application files (#APP_IMAGES#app-icon.svg) that can be used everywhere.

Apex52_bluepring_dialog3

I blogged about it already and showed a way how to use the created icon also inside the logo header. See Apex 5.0-5.2 Logo Text with Icon

Quick SQL

Quick SQL is a shorthand way for creating a datamodel.

It is not really an Apex 5.2 feature. However it is a packaged application that was created for 5.2 but is made available already now. This is part of the low code movement.

Check out the shortlink to the public quicksql application: https://apex.oracle.com/quicksql/

Interactive Grid (IG) features

As expected IGs still get a lot of love. Some bugfixes but also several enhancements. Here are my favorite new features.

url based filtering

This can not be tested yet. However John Snyders mentioned it here.

With Interactive Reports we can set filters by adding some parameters to the url for that page. The same is absolutly needed for Grids.

copy down functionality

In the future there will be a way to copy data from one cell of an IG to all empty cells that are below it. Many customers asked me already for this when presenting IGs.

apex52_copydown
I’m not sure how easy it will be to use. Currently it doesn#t seem to be finalised yet. I hope the apex development team finds a decent way to implement it (=easy for the end user).

As you can see in the screenshot there is also a copy to clipboard function (ctrl+C)!

components

Oracle JET upgraded to version 4.1

4.1 is a huge step forward. Apex 5.1 included Oracle JET 2.0.2 (if I remember correctly). The Oracle JET team pushes enhancements very rapidly and frequently. So it is good to have a fairly recent version included now. However at the time when Apex 5.2 will be public, Oracle JET might again be several versions in front.

remote databases

Classical reports (and some other components too) can now be based upon a remote database source.

I wasn’t able to test this feature thoroughly in the cloud version. I hope it means we can now use tables over database links (=remote databases) as a source for our wizard. This is a very typical scenario in company environments. Use apex to access a different Oracle database where the business data resides, but where no apex is installed.

general stuff

Spotlight search

The search functionality inside page builder was hugely improved. Get familiar with it!

apex52_spotlight_search

Sticky property filters

The property pane in the page designer can now be searched. And this search can be pinned. So that the same filter is applied for all items that we click upon. This is very useful!

apex52_pin_filter

Dev toolbar enhancements

Page timing info. Looks nice. I’m not sure how useful, but I sometimes refer to the page timings. Having a better graphical presentation is certainly interesting.

apex52_page_timing_infos

Javascript errors on the page are now marked on the developer toolbar.
apex52_devbar_with_JSerror

REST/JSON support

There are major enhancements with regards to rest modules and consuming rest web sources.

I didn’t have time to test them yet. But it is definitly a way into the right direction.

Additional notes

compatibility mode

There is no compatibility mode 5.2. It seems as if this is intended. Somebody already addressed this as an issue, and this was the response:

“Thank you for your feedback. Unbelievable, but APEX 5.2 doesn’t contain any change of behaviour …”

Quo Vadis IR?

IGs have an improved way to do conditional highlighting. Already in Apex 5.1.
We can now highlight a column depending on the value of another column.

This was way overdue.

Unfortunatly this is still not possible for Interactive Reports.

Here is how the highlighting dialog looks for IRs vs. IGs

I have the feeling that IRs are now the step child of the apex team. It is the declared goal to eventually replace IRs with IGs. I support that vision for the future. However we are still far away from that (pivot functionality missing, and some others). During that transition, IR shouldn’t be left out of enhancements.

Charts

There are some new charts included. Especially Gantt Charts might be of some interest. However I don’t particuarly missed them in the past.

Oracle Jet includes a few very unique chart types. I would like to see declarative support for all of them. But my favorite Oracle JET chart types are diagrams (container layout), NBox and the PictoChart.

Conclusion

Some small enhancements that really should have made it into the previous version already (url based IG filtering for example). But also some major steps into the right direction (REST+JSON support).

I like!