APEX Instrumentation and the SQL Developer

In general I do instrument all my code, but usually I have plsql code that calls some framework like logger. Because of that I rarely add extra APEX instrumentation code. Any trace or debug information is already written into the logger table.

Sometimes it makes sense to add calls to apex_debug too. Especially if this part of the logic is checked frequently using the APEX built-in debug functionality.

APEX 19.1 developer toolbar

Debugging in APEX has a huge advantage. As a developer it is very easy to do and to access the output.

The apex_debug package is available at least since APEX 5. Among others it includes a procedure enter to store parameters. It is recommended to call this at the beginning of your modules and add the combination of parameter name and value to the procedure.

Recently I added apex_debug.enter to several of my modules. Here are a few tricks I’d like to share.

Tipp 1: Debug level 5

Apex_debug has several levels. The default level is 4 (info).

If you want to see the information stored with apex_debug.enter, you need to show at least level 5.

The level can be set in the url. Instead of YES, set it to LEVEL5.

f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:itemValues:PrinterFriendly

In APEX I use the enter procedure to store page item values that are used inside plsql blocks but also to see what is going on at the database level. Here is an example

before header plsql process

The process first calls apex_debug.enter. Then the procedure pk_setze_defaults.prepare_topbar calls apex_debug.enter a second time and stores the supplied parameter values.

And this is how the view debug output can look like

Show debug

Useful to see the state of page items at time of processing. And even more interesting is to see what procedures where called and which parameters had been used.

Tipp 2: avoid hardcoding the module name

The first parameter of the enter procedure is p_routine_name.

Instead of hardcoding the name we can call utl_call_stack to return the name of the module. You need to have at least database version 12c to use it.

utl_call_stack.concatenate_subprogram( 
    utl_call_stack.subprogram(1))

Utl_call_stack.subprogram gives us access to the module names inside the call stack. Number 1 in the stack is always the current module. Subprogram returns a collection which holds the package name and the submodule name. Sometimes multiple submodule names. The concatenate_subprogram function translates this collection into a readable string (divided by dots).

Example: Instead of hardcoding the module name ‘post_authenticate’

apex_debug.enter(p_routine_name=>'post_authenticate'
                ,p_name01 =>'p_user', p_value01 => p_user);


I use utl_call_stack to have the database fetch the module name at runtime

apex_debug.enter(p_routine_name=>utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(1))
                ,p_name01 =>'p_user', p_value01 => p_user);

The result:

PK_APEX_UTIL.post_authenticate p_user=>Sven

The first part is the module name (incuding package name), the second part is a list of parameter=>value combinations.

There are some side effects to it. The name is fetched at runtime, instead of decided at compile time.1 In some cirumstances (module inlining) a procedure can be rewritten by the plsql optimizer, so that the name of the module disappears. Utl_call_Stack would then return the name of the module, where the code was inlined into.

The runtime call is also slightly slower than the literal value.

Inside a plsql process in APEX the name will be __anonymous_block . Which is correct. APEX executes those blocks using dbms_sql. The name of the process is not known inside the block. But it can be seen in the APEX debug view output one line before the anonymous block.

So the advantage of not hardcoding the module name must be weighted against the possible side effects.

If you want the procedure name, but not the package name, then the following code will help. It returns only the name of the current (innermost) submodule:

utl_call_stack.subprogram(1)(utl_call_stack.lexical_depth(1)+1)

Tipp 3: use the newest SQL Developer version (18.4)

SQL Developer 18.4 has the built-in ability to grey out instrumentation code. I like this feature a lot. It allows the trained developers eye to quickly scan plsql code – without resting too much on the less important bits and pieces.

What surprised me is that this also includes apex_debug.

Here is an example screenshot. Notice how the whole call to apex_debug is greyed out.

SQL Developer 18.4 – PL/SQL code colors

Other packages that are greyed out are dbms_output, log and logger.

And we can add our own instrumentation framework to it. Which leads me to tipp 4.

Tipp 4: configure SQL Developer – add your instrumentation framework

It is a bit hard to find, but the color rule PlSqlCustom2 is where we can add our own package. Search for color in the preferences to find the entry. In one of my projects the instrumentation package is called pk_logging. So I add it like the screenshot shows.


Tools / Preferences => Code Editor / PL/SQL Syntax colors

And this is how the sample result looks like.

SQL Developer 18.4 – PL/SQL code colors – enhanced

Tipp 5: use snippets

Snippets are a nice little feature in SQL Developer. And you can add your own useful code snippets to it. I added a snippet for apex_debug.enter

SQL Developer – snippets view

Once implemented I simply double click the snippet and it adds the following code block.

    apex_debug.enter(p_routine_name => utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(1))
                    ,p_name01=>'XXX',p_value01=>xxx
                    ,p_name02=>'YYY',p_value02=>yyy
                    ,p_name03=>'ZZZ',p_value03=>zzz
                    );

If you like you can download this snippet from the SQL Developer exchange plattform (https://apex.oracle.com/pls/apex/f?p=43135:16:0::NO:RP,16:P16_ID:1141)

But it is easier just to create your own snippet and copy the code from here.

Conclusion

Make use of the developer tools you have. Use apex_debug, use SQLDeveloper. Think about your instrumentation framework. Make it as easy to use as possible.

Further readings

Footnotes

1. In APEX compile time and runtime of a PLSQL process are almost identical. The PLSQL process is dynamically compiled at runtime.
Advertisements
RDS as tabs

APEX quickie: set region display selector dynamically

A region display selector (RDS) can be configured so that it remembers the last selected region or tab. However sometimes we might want to navigate from another page directly to a specific tab no matter what the last remembered session state was. 

Here is how to do that.

My example uses a region display selector set to “View single region” and three color coded regions. The template options are choosen in such a way, that the regions are displayed directly below the selector, with no additional margin.

The region display selector (RDS) uses the session storage of the browser to remember the active tab. This can be investigated using the browsers developer tools.
Find web-storage, navigate to the Session Storage and look at the keys.

The Key here is using a prefix that consists of three parts. The application id (in my case 87230) the page number (2) and the region static id (DEMO). The name of the key is “activeTab”.  So the full name of the key is .87230.2.DEMO.activeTab

We can read and set the local storage and the session storage using APEX javascript apis. Link to the docs

The following code can read the session storage for the current page and will set a key to a specific value.

let sesStorage = apex.storage.getScopedSessionStorage({
       useAppId:true, 
       usePageId:true});
sesStorage.setItem( "DEMO.activeTab", "#REGION3" );

Typically we want to set the target tab from inside a different page. Set the target display selector, then navigate to that target page. This is the more logical thing to do, otherwise we could simply click on the link or sent this click event to the appropriate item. 

// choose target region via region display selector
// static id = "REGION3"
$('#REGION3_tab a').trigger('click');
If we are currently on a different page, we need to construct the prefix for the key by ourself. That is why the following code sets usePageId to false. It would be possible to read the session state for the whole application, but I prefere to read only the needed parts. So we construct the key prefix by ourself and this includes the appId.

let sesStorage = apex.storage.getScopedSessionStorage({
       prefix:"."+&APP_ID.+".2", 
       useAppId:false, 
       usePageId:false});
sesStorage.setItem( "DEMO.activeTab", "#REGION3" );
I tested this useing a static select list with a dynamic action that essentially run this code. And the result is as expected. The 3rd region is selected. Also the developer tools show that the value was changed. rds_result Fairly easy, once we know what to do. In the future I might use this session storage for other things too.

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!

is {JSON}, or not is {JSON}, that is the question

Whether ’tis nobler in the mind to suffer
The [] and “” of outrageous json,
Or to take Arms against a Sea of troubles,
And by opposing {} end them: to select, to browse
No more; and by a browse, to say we end
the json array, and the thousand json objects
that Flesh is heir to? ‘Tis a consummation
devoutly to be wished. To select, to browse…

Intro

So I have this logging table where sometimes the message can be a json document. Most of the times it is an error message or some tracing information. But sometimes I want to show what data currently is inside a plsql collection. I wrote a small conversion function, that returns a clob which should contain JSON. This json ends up as a message in my logging table.

Inside an apex application I added the possibility to show this JSON as a modal page. I use the JSONView Plugin, however most browsers now have a default JSON display capability.

I detect if the message contains json data by using the 12.1 IS JSON operator.

In case it is json, I provide a link to the modal page and print the data there.

case 
  when LOG_MESSAGE is json 
  then
    '<a href="'||
    apex_util.prepare_url('f?p='||v('APP_ID')||':123:'||v('APP_SESSION')
    ||'::::P123_LOG_ID:'||to_char(LOG_ID))
    ||'" title="show JSON data">{JSON}</a>'
else 
  '<pre>'||LOG_MESSAGE||'</pre>'
end as log_message_enhanced

 
The modal page uses the application/json mime type.

The result looks similar to this:

 
modal_json_ok
 

How build such an apex page or how to do a conversion from a plsql collection to json is not part of this blog post. If your are interested in that, please leave a comment and I might consider to publish how to do so.

Problem

For certain documents the browser was not able to show the json. Instead it returned an error message and the json in text format. Here is an example.
modal_json_error

Sorry for the German Message, but I was not able to switch my Firefox to english mode easily. The error essentially says: “We could not parse the json, it seems the document does not comply to the specifications.”

So the database says it is json, but the browser says it is not!

What is going on? That is the question.
 

Solution

The reason for the behaviour is that Oracles IS JSON check uses the lax json syntax (by default). LAX json allows several things, among others it allows to have a list of objects with a trailing comma at the end. Exactly my issue.

Here is a basic SQL demonstration. Note the comma after the “Larry” inside the json object step.

select * from dual
where '[{"index":1,"name":"Larry",}]' 
      is json;
DUMMY
-----
X

So it is JSON. But only lax json.

Fortunatly we can also do a check for the more strict json interpretation.

select * from dual
where '[{"index":1,"name":"Larry",}]' 
      is json (STRICT);
No rows selected.

Can you spot the difference? The STRICT keyword including parenthesis tells the database that the document needs to confirm to the more strict specification.

This is of cause documented: About Strict and Lax JSON Syntax

The main differences are

  • STRICT: each JSON field and each string value must be enclosed in double quotation marks (“).
    LAX: An object literal can also be enclosed in single quotation marks (‘).
  • LAX: Case variations for keywords true, false, and null (for example, TRUE, True, TrUe, fALSe, NulL).
  • LAX: An extra comma (,) after the last element of an array or the last member of an object (for example, [a, b, c,], {a:b, c:d,}).
  • LAX: Numerals
    • with one or more leading zeros (for example, 0042.3).
    • Fractional numerals that lack 0 before the decimal point (for example, .14 instead of 0.14).
    • Numerals with no fractional part after the decimal point (for example, 342. or 1.e27).
    • A plus sign (+) preceding a numeral, meaning that the number is non-negative (for example, +1.3).

But there are more differences.

Using the (STRICT) keyword, solved my problem. Only those json documents where linked, that could be shown in the browser. The others were rendered as normal text.

Conclusion

 
Be aware that there are slightly different JSON specifications available.

If you want to show json inside a browser, then make sure the json document confirms to the strict json specification.

If you want to use json inside javascript, then probably the lax version is better suited.
 

 

Apex 5 – show Y/N columns as font awesome icons

apex5_YN_icons

The database column value is Y or N (J or N for German databases). In the past I rendered such columns using images that had a name, where the column value could be appended to, for example IconCheckbox_Y.png. In Apex 5 the same is possible using icon classes.

1) add an alias for Font-Awesome Icons

This little css will create two aliases for the fa-check and the fa-remove icons. And it will give them a default color.

.syn-check-J:before,.syn-check-Y:before {
  content: "\f00c";
  color: green;
}
.syn-check-N:before {
  content: "\f00d";
  color: #ff3a30; /* color: red; */
}

If you want to know the content code for a different icon you can search in the fontAwesome.min.css file or have a look at some “cheatsheet” pages like this one here: http://astronautweb.co/snippet/font-awesome/

If we would add another attribute:

  font-family:fontAwesome;

then we could use the icon even without the “leading” fa class.
I do not recommend this! Remember in Apex 5.1 we have two icon fonts to choose from – FontAwesome OR Font-Apex. Adding the font-family would prevent switching the icons.

2) html expression to set the class

And then use a normal text field with a html expression.
Html expressions are available for classic reports, but also for interactive reports and interactive grids.

<span class="fa fa-lg syn-check-#COL1#" alt="#COL1#"></span>

Our new classes still can be combined with other font-awesome styles. Here I enlarged them a little bit by adding the “fa-lg” class.

The good thing about an html expression is, that the field value still is the original value. So we can use the interactive report column filters on this value and an export to csv will not add any html tags. This solution is SOC conform. The database query will not bother with the representation of the values.

apex5_YN_icons_filter

Quick. Easy. Useful.

How does it work in 5.1 with Interactive grids?

IGs are slightly different. Especially if they are editable. We can not simply add the icon to a text column. Instead we have a new column type HTML EXPRESSION. In that we can reference a different column.

Here is an example from the Interactive Grid Sample Application (Page 30, Base Edit). The emp table has a column ONLEAVE and holds Y and N values. apex5_YN_icons_IG_column2

The substitution syntax for IG columns does not use # anymore. Instead the expression will look like below. The column value from ONLEAVE is substituted.

<span class="fa syn-check-&ONLEAVE.">&ONLEAVE.</span>

We now face an additional obstacle. The ONLEAVE column is a switch. The switch component will translate Y into Yes and N into No. Our new second column recieves that translated value. We could either add an additional hidden column to the select and referece that one or we can extend our css aliases. Because the switch component look pretty useful, it seems better to include its outcome to the css.

.syn-check-J:before,.syn-check-Y:before,.syn-check-Yes:before {
  content: "\f00c";
  color: green;
}
.syn-check-N:before,.syn-check-No:before {
  content: "\f00d";
  color: #ff3a30; /* color: red; */
}

And here is how the result looks like:
Apex5_YNIcon_IG_column

As you can see the icons look more delicate than before. This is because the sample application uses Font Apex and not Font Awesome anymore.

Example using images

And sometimes we still want an image. Maybe because there is no fitting icon in our font or maybe because the users insist on keeping the old images. This can also be done with css classes. However some more attributes are needed.

Here is an example without further explaination.


.syn-checkImg-J,.syn-checkImg-Y {
  background-image:url("https://upload.wikimedia.org/wikipedia/commons/thumb/0/03/Green_check.svg/240px-Green_check.svg.png");
  background-repeat: no-repeat;
  background-origin: padding-box;
  background-size: 20px 20px;
  padding:0 20px;
}
.syn-checkImg-N {
  background-image:url("https://upload.wikimedia.org/wikipedia/commons/thumb/a/a2/X_mark.svg/210px-X_mark.svg.png");
  background-repeat: no-repeat;
  background-origin: padding-box;
  background-size: 20px 20px;
  padding:0 20px;
}

It works with DIVs and SPAN tags.
And I created a jsfiddle where you can test it out for yourself.
https://jsfiddle.net/1n6s4y3j/

Apex 5.1 session cloning

Introduction

With Apex 5.1 we got a nifty new feature. It is now possible to clone an apex session, so that we can have two (or more) independent APEX sessions running in the same browser.

It only took me 2h to implement this from scratch. So it is really easy to do.

Joel Kallmann describes how to do it in this blog post http://joelkallman.blogspot.de/2016/07/apex-session-isolation-across-multiple.html

There are a few additional tweaks I would like to mention here.

Step 1) Enable session cloning on instance level

logon as sys (or any user with apex_administrator_role).

I first granted my normal DBA account the apex_administrator_role

grant apex_administrator_role to myDBA;

and then as DBA:

begin
apex_instance_admin.set_parameter(
p_parameter => 'CLONE_SESSION_ENABLED',
p_value     => 'Y');
end;
/

 

If you are in a CDB/PDB environment, with Apex installed in the PDB (recommended) then  make sure to run this on the matching PDB (especially when working with sys).

e.g.

alter session set container = PDBAPEX

Joels article also explains how to enable this for a single workspace. But I got an error message when trying to do so.

"ORA-20987: APEX - Instance parameter not found - Contact your application administrator."

Step 2) Add a new navigation bar entry

Of cause you are free to add this functionality everywhere you want. But you will need a link, that the user has to click on to get a new session. My preference was the navigation bar.

The url for the link is simple. Just add APEX_CLONE_SESSION as the request parameter.

f?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.:APEX_CLONE_SESSION

create a navigation bar list entry with this link

  • Go to “Shared Components/Navigation Bar Lists/navigation bar” (the name of my list).
  • Choose any icon (fa-clone) and label text (“Session klonen”) that you want.
  • The target page needs to be “&APP_PAGE_ID.”. This will use the current page and add it into the link.
  • And most importantly the request needs to be APEX_CLONE_SESSION.

apex51_clone_session_navbar_entry

The entry is now ready and working. However it will replace the session in the current browser tab. But rightclick and “open in new tab” gives us two tabs with a different apex session.

If that is not enough then this next step is wanted too.

Step 3) Open the link in a new page

The goal is to add a TARGET attribute to our link, so that a new tab is always opened when we clone the session. Unfortunatly the navbar template has no option to include link atributes. We could do a kind of injection trick with the url, but I prefere a cleaner way.

To do so we need to modify three small parts.

First copy the navigation bar template as a new template “Navbar with Attributes”

And there we add the user defined property #A03# to our link. This needs to be done in all areas where we find an <a..> tag.At least for the “list template current” and “list template non-current”. I also added it for the sub list entries, even if my navbar doesn’t use sub lists.

apex51_clonesession_template

Don’t forget to add a description “Link Attributes” for the newly added attribute value in the “attribute description” section (scroll down a bit to see it).

Then enhance our existing navbar entry with target=”_blank”

apex51_clonesession_linkattributes

There is a tiny difference between using target=”_blank” or target=someothername.

target=”_blank” will always create a new tab.

target=”_someothername” will open a new tab on the first click. Consecutive clicks however will always reuse this same tab. This can be useful if you want to prevent your users to constantly cloning session after session after session.

 

And finally make sure that our application uses the new template for showing the navbar list

Shared Components/User Interface Attributes/Desktop/Navigation bar/List Template

apex51_clonesession_userinterface

 

Result

The navigation bar then could look similar to this here:

apex51_clonesession_navbar_de

Clicking on the “clone session” link will open a new tab in the same browser. In the url we will see that a different session id was created.

The new session will have all the same item values (page items, application items, etc.) as the privious session had. But from this point on, the two sessions will start to differ.

Both sessions will use the same browser cookie. For that reason if one session logs out, then the other session will be logged out too.

 

How to upgrade from Apex 5.0 to 5.1

Preparation

As a preparation I recommend several steps and checks to prepare the upgrade.

  • Make sure you have a working database backup and just in case a DBA at hand who would be able to restore your database, tablespace or schemas.
  • Workspace clean up – delete applications that are not needed anymore. Especially copies of others application, that were created to test a specific feature or do a proof of concept. Be careful whether you also want to run any included deinstallation scripts. For an Apex update in general you don’t want to delete the connected tables.
  • Software Download – Apex 5.1 can be downloaded from the OTN download page: www.oracle.com/technetwork/developer-tools/apex/downloads/index.html
  • Backup Application – Export the application including all private saved reports
  • Export supporting objects like Theme, Static Application files
  • Backup Image folder – if your image folder is /i/ then make a copy and rename the copy to /i50/
  • Check the Apex 5.1 known issues page:  http://www.oracle.com/technetwork/developer-tools/apex/downloads/apex-51-known-issues-3408261.html

Apart from downloding the new Apex version, none of these steps is really required. But it gives a nice and cozy fealing to be sure to be able to go back.

In Apex 5.1 some features are deprecated and some options did change. It is possible to prepare your application to anticipate the effects of the upgrade. I will cover this in a separate blog post. More importantly read through the “changed behaviour”, “deprecated” and “desupported” sections of the installation manual (https://docs.oracle.com/database/apex-5.1/HTMRN/toc.htm#HTMRN-GUID-8BDFB50B-4EC6-4051-A9B6-7D5805E00C4E ).

Here are some things to consider already in Apex 5.0.

  • Apex.Server plugin
    • does not return an jqXHR object anymore
    • async option deprecated
    • htmldb_getobject desupported => replace with apex.server
  • old Apex themes deprecated
  • check for CANCEL or BACK or PREVIOUS buttons (page redirect) with execute validations = YES. These will do client side validation in Apex 5.1. If that is not wanted, change it to NO.
  • jsTRee plugin deprecated
  • classic reports
    • hidden column type => hidden column
    • no enterable fields!
  • file browse storage => switch from WWV_FLOW_FILES to APEX_APPLICATION_TEMP_FILES
  • desupported attributes or types
    • page: body header, include standard javascript and css
    • region: svg charts, simple chart, classic tree
    • button: pre text, post text
    • item: start and stop grid layout, file browse storage type
  • Conditions deprecated: text= value, text != value, text is (not) contained in item
  • No more: Save state before branching
  • apex_plsql_job package desupported
  • check if you reference the internal hidden fields  (renamed in Apex 5.1): pPageChecksum => pPageItemsProtected, p_md5_checksum=>pPageItemsRowVersion
  • date picker (classic) deprecated
  • several updated javascript libraries

Decide about the upgrade path

Now consider whether you want to do a traditional upgrade (all steps in one go) or if you want to minimize the application downtime (several steps, not for CDB$ROOT installations). Or as oracle calls it: “maximize application uptime”.

To minimize downtime read this chapter in the documentation: https://docs.oracle.com/database/apex-5.1/HTMRN/toc.htm#HTMRN-GUID-411DE0D8-59E1-4267-8751-FD56305ABE4E

Upgrade in one go

The only step needed to do:

Create database schemas and database objects (tables, packages) and do the application migrations.

@apexins.sql tablespace_apex tablespace_files tablespace_temp images

Upgrade with maximum Uptime

Instead of running a single script, we can do the upgrade in several steps. Only during the 3rd step, the end users need to be disconnected. This third step took only 1.01 seconds on my system.

The upgrade of an Application Express instance runs in four phases:

  1. Create database schemas and database objects (tables, packages).
    This essentially creates the Apex_051000 schema.
    -> no influence on running sessions
  2. Migrate application metadata.
    This copies the repository application data from Apex_050000 into Apex_051000.
    To help with that some upgrade triggers were previously installed.
    ->  developers can’t work anymore
  3. Migrate data that runtime applications modify and switch to the new version.
    -> downtime for all (developers and end users)
  4. Migrate additional log and summary data (this step starts automatically)
    -> no influence on running sessions

But we need zero downtime – is it possible?

I’m convinced it is possible to reach a downtimeless application upgrade using the EBR  (edition based redefinition) feature of the oracle database. I have extensive knowledge using EBR even inside Apex. However so far I didn’t have the time to do a proof of concept (POC) for the upgrade. Also this would be an unsupported action (currently). The change would include tweaking serveral non editioned objects (public synonyms, session contexts, registry data) in such a way that they show up differently when used inside an edition.

If any Germany based customer or the Oracle Apex Team itself is interested how to do this and is willing to pay for the time I need to invest in this, then please contact me.

do the upgrade

Unzip the Apex_5.1.zip file into an appropriate folder. And navigate to the apex_5.1/apex folder

If you decide for the “Maximum Uptime” upgrade path, then three scripts need to run. And ORDS needs to be stopped for script 3. To run the scripts we need to know the tablespace names and the image path.

Find the tablespace

The documentation gives examples useing the SYSAUX tablespace. I do not recommend that. Apex should have its own tablespace.

select username, default_tablespace, temporary_tablespace, profile, oracle_maintained
from dba_users
where regexp_like(username,'^(APEX_|ORDS_)');

This shows only the default setting. We can reuse the same tablespace. But it is also possible to install apex 5.1 into a new tablespace. If you want to do that, then this new tablespace needs to be created first.

Sometimes we want to see if the data is really in this default tablespace. Here is a select that will show the data distribution and also how much space is used.

select owner as schema, tablespace_name as data_tbs, nvl(segment_type,' - total -') segment_type, round(sum(bytes)/1024/1024,2) size_in_MB
from dba_extents
where regexp_like(owner,'^(APEX_|ORDS_)')
group by owner, tablespace_name, rollup(segment_type)
;

Example result

SCHEMA        DATA_TBS SEGMENT_TYPE SIZE_IN_MB
APEX_050000   APEX     INDEX        239,69
APEX_050000   APEX     TABLE        267,19
APEX_050000   APEX     LOBINDEX     12,75
APEX_050000   APEX     LOBSEGMENT   240,31
APEX_050000   APEX     - total -    759,94
ORDS_METADATA ORDS     INDEX        4,5
ORDS_METADATA ORDS     TABLE        1,63
ORDS_METADATA ORDS     LOBINDEX     0,19
ORDS_METADATA ORDS     LOBSEGMENT   0,38
ORDS_METADATA ORDS     - total -    6,69

This is a example of one of my apex environments. As you can see there is only one Apex tablespace “APEX” used. Approximatly the same amount of data is in tables, in indexes and in LOBs. LOBSEGMENT indicates that there had been some wwv_flow_files activities going on.

Run the scripts

The scripts are located in the apex subfolder. For example D:/product/apex/apex_5.1/apex.

Navigate to that folder and start an sqlplus session with sys as sysdba. If you are in a CDB/PDB environment connect to the PDB not to CDB$ROOT. To connect to the PDB, the service name needs to be provided. Because of that also a running listener and a matching tnsnames.ora file is needed.

Assuming the following settings:

  • tablespace for apex and apex files: APEX
  • temp tablespace: TEMP
  • image directory: /i/

Then those 3 scripts need to run.

@apexins1.sql APEX APEX TEMP /i/
@apexins2.sql APEX APEX TEMP /i/

stop ORDS

@apexins3.sql APEX APEX TEMP /i/

restart ORDS

Phase 4 is automatically started by running a dbms_scheduler job.

Check privs and synonyms

Sometimes we give extra access from the Apex Schema to our own custom schema. For example in one Application I extended the Apex Feedback functionality and did use the view APEX_TEAM_FEEDBACK. Such changes need to be moved to the new APEX_050100 schema.

ACLs

Here is just how to check if the Apex 5.0 schema has any network ACLs set.

SELECT *
FROM DBA_HOST_ACES xe
where principal = 'APEX_050000';

I created a script to duplicate all ACEs that exists for APEX_050000 to APEX_050100.

The script is shown and explained in a separate blog post.

 

Grants

Here is just how to check if the Apex 5.0 schema has any objects granted to other schemas.


select *
from all_tab_privs
where grantor like 'APEX\_%' escape '\'
and grantee not in ('PUBLIC','SYS','SYSTEM');

It will show objects like tables, views and packages that have privileges granted directly.

To see only the missing grants you can run the following statement. If it returns no rows, then you are fine.


select GRANTEE , TABLE_SCHEMA , TABLE_NAME , PRIVILEGE , GRANTABLE ,HIERARCHY
from all_tab_privs
where grantor in ('APEX_050000','APEX_050100')
and not regexp_like (grantee,'^(APEX_|ORDS_|SYSTEM$|PUBLIC$|SYS$)')
group by GRANTEE , TABLE_SCHEMA , TABLE_NAME , PRIVILEGE , GRANTABLE ,HIERARCHY
having count(*) = 1 and min(grantor) = 'APEX_050000';

Those grants probably need to be copied to the new apex_50100 schema.

Search Source code

You should search the complete application, if there are any references to APEX_050000. This should be done after the migration.

downgrade to 5.0

The section how to downgrade an application back to 5.0 is currently missing from the documentation. Here is a official blog post how to do it: http://jastraub.blogspot.de/2017/01/ooops-i-did-it-again.html

This is the sql script that Jason Straub published to do the downgrade:

alter session set current_schema = SYS;

@wwv_flow_val.sql
@wwv_flow_val.plb 

begin
 dbms_utility.compile_schema('APEX_050000');
end;
/ 

set define '^'
@validate_apex x x APEX_050000

begin
 for i in ( select owner, trigger_name
 from sys.dba_triggers
 where owner = 'APEX_050000'
 and trigger_name like 'WWV_FLOW_UPGRADE_%'
 order by 1 )
 loop
 sys.dbms_output.put_line('Dropping trigger '||i.owner||'.'||i.trigger_name);
 execute immediate 'drop trigger '||i.owner||'.'||i.trigger_name;
 end loop;
end;
/

ALTER SESSION SET CURRENT_SCHEMA = APEX_050000;
exec apex_050000.wwv_flow_upgrade.switch_schemas('APEX_050100','APEX_050000');

ALTER SESSION SET CURRENT_SCHEMA = SYS;
drop context sys.APEX$SESSION;
create context sys.APEX$SESSION using APEX_050000.WWV_FLOW_SESSION_CONTEXT;
declare
 l_apex_version varchar2(30);
begin
 l_apex_version := apex_050000.wwv_flows_release;
 dbms_registry.downgrading('APEX','Oracle Application Express','validate_apex','APEX_050000');
 dbms_registry.downgraded('APEX',l_apex_version);
 validate_apex;
end;
/

select * from dba_tablespaces ;

select username, default_tablespace, temporary_tablespace, profile, oracle_maintained
from dba_users
where regexp_like(username,'^(APEX_|ORDS_)');

select owner as schema, tablespace_name as data_tablespace, nvl(segment_type,' - total -') segment_type, round(sum(bytes)/1024/1024,2) size_in_MB
from dba_extents
where regexp_like(owner,'^(APEX_|ORDS_)')
group by owner, tablespace_name, rollup(segment_type)
;

copy ACLs during Upgrade to Apex 5.1

The following works in 12c only. In previous database versions the package to set ACLs had other modules. Those are now deprecated. The script does not call any deprecated functions.

You can see the ACLs/ACEs by checking the data dictionary.

select * from DBA_HOST_ACES where principal like 'APEX_%';

This little script will check the network ACLs for the APEX5.0 schema and copies it to the Apex 5.1 scheme. It will not delete any ACLs. But use it at your own risk. It automatically commits.


declare
/* Author: Sven Weller
Company: syntegris information solutions GmbH
Purpose: Transfer Network ACLs from APEX_050000 to APEX_050100 schema
Created: 11.01.2017
*/
v_source_schema  varchar2(30) := 'APEX_050000';
v_target_schema  varchar2(30) := 'APEX_050100';

v_ace xs$ace_type;
v_host DBA_HOST_ACES.host%type;
v_lower_port DBA_HOST_ACES.lower_port%type;
v_upper_port DBA_HOST_ACES.upper_port%type;

BEGIN
for apex50acls in (SELECT xe.*
,row_number() over (partition by host, principal, lower_port, upper_port, start_date, end_date, grant_type,inverted_principal, principal_type order by ace_order, privilege) privlist#
,dense_rank() over (order by host, principal, lower_port, upper_port, start_date, end_date, grant_type,inverted_principal, principal_type) group#
FROM DBA_HOST_ACES xe
where principal = v_source_schema
and (xe.host, xe.lower_port, xe.upper_port,xe.start_date, xe.end_date, xe.grant_type,xe.inverted_principal,xe.principal_type)
not in (select t.host, t.lower_port, t.upper_port, t.start_date, t.end_date, t.grant_type,t.inverted_principal, t.principal_type
from DBA_HOST_ACES t
where t.principal = v_target_schema)
order by group# ,privlist#
) loop

if apex50acls.group#>1 and apex50acls.privlist#=1 then
-- store the old acl
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host        => v_host,
lower_port  => v_lower_port,
upper_port  => v_upper_port,
ace         => v_ace);
end if;

if apex50acls.privlist#=1 then -- first time
-- prepare the new acl
v_ace := xs$ace_type(
privilege_list => xs$name_list(apex50acls.privilege),
principal_name => v_target_schema,
principal_type => case apex50acls.principal_type
when 'APPLICATION' then xs_acl.ptype_xs
when 'DATABASE' then xs_acl.ptype_db
when 'EXTERNAL' then xs_acl.ptype_external
end ,
granted   => apex50acls.grant_type = 'GRANT',
inverted  => apex50acls.inverted_principal = 'YES',
start_date => case when apex50acls.start_date < systimestamp then systimestamp
when apex50acls.start_date > systimestamp then apex50acls.start_date
end,
end_date => apex50acls.end_date
);
v_host:= apex50acls.host;
v_lower_port := apex50acls.lower_port;
v_upper_port := apex50acls.upper_port;
else
-- add a new privilege
v_ace.privilege_list.extend;
v_ace.privilege_list(apex50acls.privlist#):= apex50acls.privilege;
end if;

end loop;
if v_host is not null then
-- store final ace
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host        => v_host,
lower_port  => v_lower_port,
upper_port  => v_upper_port,
ace         => v_ace);
end if;
END;
/