about IDLE session timers in Apex 5

Introduction

In a recent project I wanted to use the idle session timer. The final goal was to give the end user a possibility to see how soon his session will end. It happens when the user enters a lot of data in a tabular form and work is interupted, that all the effort is in vain because the idle timeout forces a new login.

Martin D’Souza wrote an article about that some time ago: http://www.talkapex.com/2009/09/enhanced-apex-session-timeouts.html. But it is a bit outdated now, and he does not reuse the Apex settings, and simply uses his own timer that accidentically is the same as the Apex timeout. Better would be an approach to use the Apex settings.

Surprisingly it was not so easy to find where the idle session time is set and how to use it progammatically. Here are my findings.

How to set the idle timeout

See also this older blog post from Patrick Wolf about setting the timeout in Apex 3.2. Some of it is still true, however some has change since then. For example we can now set the timeouts also on workspace level.

Is your Oracle APEX session timing out?

There are 3-4 levels where the session idle timeout can be set.
Each specific level overwrites the setting of the more generic level.

Instance Level

Login as instance administrator (INTERNAL workspace).

Manage instance / Security / Session Timeout

The default is 1 hour (=3600 seconds).

apex5_sessiontimer_instanceAdmin

Workspace Level

As a instance administrator (INTERNAL workspace) go to

Manage Workspaces / Existing Workspace / Click on Workspace Name / Session Timeout
 
apex5_sessiontimer_workspacesetting

Application Level

Application / Shared Components / Application Definition Attributes / Security / Session Management

 

apex5_sessiontimer_appsetting

Session Level

This can only be set programmatically.

http://docs.oracle.com/cd/E14373_01/apirefs.32/e13369/apex_util.htm#AEAPI355

BEGIN
APEX_UTIL.SET_SESSION_MAX_IDLE_SECONDS(p_seconds => 1200);
END;

How to read the settings

Here are some commands that help to find out about the current session timeouts/idle timeouts. Note that to get the instance level settings (e.g. the default if nothing else was set), we can not use the official apex apis.
Update! This has been changed with version 5.0.4!


--- Find apex session idle times

-- Application level
select application_id, application_name, maximum_session_idle_Seconds, maximum_session_life_seconds, session_lifetime_exceeded_url, session_idle_time_exceeded_url
from apex_applications
--where application_id = :APP_ID
;

-- Application level - DBA access only
select security_group_id, display_id as application_id, name as application_name, max_session_length_sec, on_max_session_timeout_url, max_session_idle_sec, on_max_idle_timeout_url
from apex_050000.wwv_flows
--where display_id = :APP_ID
;

-- Workspace level
select workspace, workspace_display_name, maximum_session_idle_Seconds, maximum_session_life_seconds
from apex_workspaces;

-- Workspace level - DBA access only
select id, short_name, display_name, source_identifier, max_session_length_sec,max_session_idle_sec
from apex_050000.wwv_flow_companies;

-- Instance level - minimum Apex 5.0.4 needed + APEX_ADMINISTRATOR_READ_ROLE
select name, value
from APEX_INSTANCE_PARAMETERS
where name in ('MAX_SESSION_LENGTH_SEC','MAX_SESSION_IDLE_SEC');

-- Instance level - DBA access only
select name, value
from apex_050000.WWV_FLOW_PLATFORM_PREFS
where name in ('MAX_SESSION_LENGTH_SEC', 'MAX_SESSION_IDLE_SEC');

-- Instance level alternative - DBA access only
select apex_050000.wwv_flow_platform.get_preference('MAX_SESSION_LENGTH_SEC') as MAX_SESSION_LENGTH_SEC
      ,apex_050000.wwv_flow_platform.get_preference('MAX_SESSION_IDLE_SEC') as MAX_SESSION_IDLE_SEC
from dual;

-- Workspace settings including Instance default overwrites - DBA access only
alter session set current_schema = APEX_050000;

set serveroutput on
declare
  v_ws wwv_flow_security.t_workspace;
  v_security_group_id number;
begin
  wwv_flow_security.g_security_group_id := 10; -- Internal
  v_security_group_id := wwv_flow_security.find_security_group_id (p_company => 'MYWORKSPACE');
  v_ws := wwv_flow_security.get_workspace(v_security_group_id);
  dbms_output.put_line('ws max kill timeout='|| v_ws.qos_max_session_kill_timeout );
  dbms_output.put_line('ws max session time in sec='|| v_ws.max_session_length_sec );
  dbms_output.put_line('ws max idle time in sec='|| v_ws.max_session_idle_sec );
end;
/

Please note that since Apex 5.0.4 we are able to read the instance settings from an official Apex view.

Therefore the following little script will give us the session idle time, regardless where it is set.

select coalesce(
   ( -- Application level
    select maximum_session_idle_Seconds
    from apex_applications
    where application_id = v('APP_ID'))
   ,( -- Workspace level
     select maximum_session_idle_Seconds
     from apex_workspaces
     where workspace = v('WORKSPACE_ID'))
   ,(-- Instance level
     select to_number(value)
     from APEX_INSTANCE_PARAMETERS
     where name ='MAX_SESSION_IDLE_SEC')
     ) max_idle_time     
from dual;

Unfortunatly to read from this APEX_INSTANCE_PARAMETERS view we need the new APEX_ADMINISTRATOR_READ_ROLE. The role is automatically granted to DBAs, Apex Admins and some other privileged database accounts during installation of Apex 5.0.4.

If you don’t have it already, then you can grant it like this

grant APEX_ADMINISTRATOR_READ_ROLE to mySchema;
Advertisements

Integrate Oracle JET into Apex 5.0

Introduction

For Apex 5.1 it is announced that the new chart library will be Oracle JET. It is possible to use Oracle Jet already in Apex 5.0. How to integrate it is the scope of this article.

Rationale

I don’t see that most of the projects will switch very soon to Apex 5.1. Many have just made the transition to Apex 5 and Universal Theme or are about to do so. This was usually quite some work. It is difficult to argue for making a new conversion to Apex 5.1 very soon. Even if some of the reasons (Interactive Grids!) are extremly compelling. However since Oracle JET Charts will be one of the major areas where we can expect some adaptation when migrating, it makes sense to start working with that technology as soon as possible. Using Oracle JET charts in Apex 5.0 would be a very good preparation and might smooth a future upgrade path.

In the scope of the Apex Dashboard Competition I made a try to combine Oracle Jet into Apex 5.0. There are quite a few traps and surprises when trying to do so. I plan to do a series of blog posts that all deal with the combination of Apex 5 and oracle jet.

This article will just concentrate on how to install Oracle Jet (OJ) and make it available in Apex 5.0.

License considerations

Since a few weeks Oracle Jet is open source.
See : http://www.oracle.com/technetwork/developer-tools/jet/downloads/jetlicense-2905156.html

“Anyone and Everyone can use Oracle JET. It’s an Open Source project sponsored by Oracle. It is distributed under the Universal Permissive License(UPL).”

Essentially it means we can use and distribute oracle jet as long as the appropriate license document is included in the distribution. The license document is also part of the oraclejet.zip file that we need to download.

Download sources

To install Oracle JET, we first need to download the newest version. Since we do not create a Oracle JET only application, most of the “Getting Started” docs and templates are not really relevant. The oraclejet.zip file hides under the “Oracle JavaScript Extension Toolkit : Base Distribution” link in the download page.

Download page: http://www.oracle.com/technetwork/developer-tools/jet/downloads/index.html

General documentation links:

Installation

Preparation

Prerequisites: the oraclejet.zip file and an apex 5.0 database.

I tested the whole installation process in the vanilla oracle developer virtual machine. This VM includes an Oracle EE database 12.1.0.2, Apex 5.0, ORDS 3.0.x and SQL Developer 4.1.

http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html

Remember it is for evaluation purposes, but not for production development!

Server Installation Process

Unfortunatly there is no CDN link that we could simply include in our apex page. Instead we need to reference the needed js and css files somehow in our Apex page.

To install Oracle JET we need access to the apex web server, where the image files are located. To do so unzip the downloaded oraclejet.zip file into a usable folder. I suggest to use the normal apex/images/libraries folder.

e.g.

/apex/images/libraries/oraclejet

apex/images is typically mapped as the image path /i/. It can later be used with a substituition variable #IMAGE_PREFIX#. To access an oraclejet file, we could include it like this #IMAGE_PREFIX#libaries/oraclejet/libs/oj/v2.0.0/ojs.js .

Unzipping the file into some accessible folder is all we need for the server installation part.

OJ configuration and integration into Universal Theme (UT)

The oj documentation states that the require.js framework is mandatory if data visualization components are used. Since the charts are the most interesting parts for Apex, we are forced to use require.js. Most of the installation problems I encountered had to do with using require.js. More details are in the “About requirejs” part. I hope that in the distribution for Apex 5.1 this dependency will be gone.

About requirejs

Require.js is a tool that is able to load needed ressources dynamically. Typically those resources are other javascript libraries. If multiple parts of an application will load the same library, require.js will only load it once including dependencies.

The Oracle JET documentation tells us how to install requirejs.

The loader that makes it easier to manage library references and is designed to improve the speed and quality of your code. Oracle JET uses RequireJS by default for the Oracle JET QuickStart Template and is required if you plan to use Oracle JET’s internationalization, data visualization components, or the oj.OAuth plugin in your application.

To use RequireJS to manage references:

  1. Download Oracle JET as described in Download the Oracle JET Zip File.
  2. Copy js/libs/oj/version/main-template.js to the js folder.
  3. In the js folder, rename main-template.js to main.js.
  4. Add the following script reference to your index.html file:
  5. Update main.js as needed to reference Oracle JET modules or your own scripts.

    For a list of Oracle JET modules and additional details about using RequireJS in your Oracle JET application, see Using RequireJS for Modular Development.

For more information about RequireJS, see http://requirejs.org.

Ok, so lets do this. Do not copy the code for the main.js from the documentation. This code is outdated and does not have all the correct paths. The reason is simple. Some libraries, like oj itself have now a newer version and the version is included in the path. If you copy the main-template.js as stated in the documentation, you should be safe. This is what I used for the main.js file. The paths are relative to the location of the main.js file itself. in this case to set the baseUrl is not needed.

requirejs.config({
  // baseUrl: '#IMAGE_PREFIX#libraries/oraclejet/js',
  // Path mappings for the logical module names
  paths: {
    'knockout': 'libs/knockout/knockout-3.4.0',
    'jquery': 'libs/jquery/jquery-2.1.3.min',
    'jqueryui-amd': 'libs/jquery/jqueryui-amd-1.11.4.min',
    'ojs': 'libs/oj/v2.0.0/min',
    'ojL10n': 'libs/oj/v2.0.0/ojL10n',
    'ojtranslations': 'libs/oj/v2.0.0/resources',
    'signals': 'libs/js-signals/signals.min',
    'text': 'libs/require/text',
    'promise': 'libs/es6-promise/promise-1.0.0.min',
    'hammerjs': 'libs/hammer/hammer-2.0.4.min',
    'ojdnd': 'libs/dnd-polyfill/dnd-polyfill-1.0.0.min',
  },
  // Shim configurations for modules that do not expose AMD
  shim: {
    'jquery': {
      exports: ['jQuery', '$']
     }
    }
  },

// This section configures the i18n plugin. It is merging the Oracle JET built-in translation
// resources with a custom translation file.
// Any resource file added, must be placed under a directory named "nls". You can use a path mapping or you can define
// a path that is relative to the location of this main.js file.
    config: {
        ojL10n: {
            merge: {
                //'ojtranslations/nls/ojtranslations': 'resources/nls/myTranslations'
            }
        }
    }
});

/**
 * A top-level require call executed by the Application.
 * Although 'ojcore' and 'knockout' would be loaded in any case (they are specified as dependencies
 * by the modules themselves), we are listing them explicitly to get the references to the 'oj' and 'ko'
 * objects in the callback.
 *
 * For a listing of which JET component modules are required for each component, see the specific component
 * demo pages in the JET cookbook.
 */

require(['ojs/ojcore', 'knockout', 'jquery', 'ojs/ojknockout', 'ojs/ojbutton', 'ojs/ojtoolbar','ojs/ojmenu','ojs/ojpictochart'], // add additional JET component modules as needed
  function(oj, ko, $) // this callback gets executed when all required modules are loaded
  {
      // add any startup code that you want here
  }
);

This file will be included in our Apex page using a js script inclusion.

<script data-main="#IMAGE_PREFIX#libraries/oraclejet/js/main" 
src="#IMAGE_PREFIX#libraries/oraclejet/js/libs/require/require.js" 
></script>

Since the script tag needs a special data-main attribute, we can not use the normal Apex 5 mechanism of adding the js.file name to the appropriate page section.

Instead we need to modify the page template. I copied the minimal one.
The result looks like this.

...
<head>
  <meta charset="utf-8">  
  <title>#TITLE#</title>
  #APEX_CSS#
  #THEME_CSS#
  #TEMPLATE_CSS#
  #THEME_STYLE_CSS#
  #APPLICATION_CSS#
  <!-- Oracle JET CSS files -->
  <link rel="stylesheet" href="#IMAGE_PREFIX#libraries/oraclejet/css/libs/oj/v2.0.0/alta/oj-alta-min.css" type="text/css"/>
  <script data-main="#IMAGE_PREFIX#libraries/oraclejet/js/main" src="#IMAGE_PREFIX#libraries/oraclejet/js/libs/require/require.js" ></script>
    
  #PAGE_CSS#  
  #FAVICONS#
  #HEAD#
  
  <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no"/>
</head>
...

There is one more problem. Require.js does not like if a library with the same name is loaded previously without using require.js. (There are some notable exceptions that depend on using Asyncronous Module Definition Api (AMD)).

Apex 5.0 already loads the Hammer.js library in a different version than the one, that is included with the base Oracle JET distribution. Hammer.js provides the possibility to use Touch Gestures. So it is especially useful for mobile applications.

Because Apex already loaded Hammer require.js throws an error message. The only way I found to make it work, was to remove Hammer.js from the Theme. Unfortunately this can be done only by unsubscribing the Theme. After unsubscribing, we can edit the Theme and remove Hammer.js from the Javascript Files Section.

Thanks to Roel Hartmann for the tipp!

In any case you should check that the libraries were loaded successfully.

On firefox open the console (SHIFT+CTRL+K) and switch on the network traffic.

apex5_log_network

If all files loaded successfully then we are up and running.

It is time to try our first chart from the cookbook.

Using a static Cookbook Demo

So lets try to use the page and add a pictoChart to it

Link: http://www.oracle.com/webfolder/technetwork/jet/uiComponents-pictoChart-default.html

First we create a static region and add the html from the cookbook.

<div  id="picto-container">
  <div id='pc1' data-bind="ojComponent:{
      component: 'ojPictoChart',
      items: pictoChartItems,
      animationOnDisplay: 'auto',
      columnCount: 5
    }"
    style="vertical-align:middle; margin-right:15px">
  </div>
  <div style="display:inline-block; vertical-align:middle; font-weight:bold">
    <span style="color:#333333; font-size:1.1em">7 out of 10 college students</span><br>
    <span style="color:#ed6647; font-size:1.3em">have sleep problems.</span>
  </div>
</div>

Then we add some javascript functions to the page itself (global functions section).

In the javascript section we need to repeat the require call including the configuration part. This is because the libs are loaded asynchronously. On each page where we want to add some js files from Oracle Jet we would do a require call immediately before the relevant section. This time the baseUrl setting is helpful.

To enable pictocharts we need to require the ojs/ojpictochart lib.

require.config({
  baseUrl: '#IMAGE_PREFIX#libraries/oraclejet/js',
  paths: {
    'knockout': 'libs/knockout/knockout-3.4.0',
    'jquery': 'libs/jquery/jquery-2.1.3.min',
    'jqueryui-amd': 'libs/jquery/jqueryui-amd-1.11.4.min',
    'ojs': 'libs/oj/v2.0.0/min',
    'ojL10n': 'libs/oj/v2.0.0/ojL10n',
    'ojtranslations': 'libs/oj/v2.0.0/resources',
    'signals': 'libs/js-signals/signals.min',
    'text': 'libs/require/text',
    'promise': 'libs/es6-promise/promise-1.0.0.min',
    'hammerjs': 'libs/hammer/hammer-2.0.4.min',
    'ojdnd': 'libs/dnd-polyfill/dnd-polyfill-1.0.0.min'
  },
   // waitSeconds: 1,
  // Shim configurations for modules that do not expose AMD
  shim: {
    'jquery': {
      exports: ['jQuery', '$']
    }
  }
});      

require(['ojs/ojcore', 'knockout', 'jquery', 'ojs/ojknockout', 'ojs/ojpictochart'], function (oj, ko, $) {
    function PictoChartModel() {
      this.pictoChartItems = ko.observableArray([
        {name: 'Have  Sleep Problems', shape: 'human', count:7, color: '#ed6647'},
        {name: 'Sleep Well', shape: 'human', count: 3}
      ]);
    }
    var pictoChartModel = new PictoChartModel();

    $(document).ready(
      function() {
        ko.applyBindings(pictoChartModel, document.getElementById('picto-container'));
      }
    );
  });

Voilá we are done!

This is how the result looks like.
Screen Shot 2016-04-07 at 01.16.45

How to enhance the pictoCharts a bit including adding other shapes will be explained in another blog post.

Here are just two more screenshots to give you some ideas.

Screen Shot 2016-04-07 at 01.17.16Screen Shot 2016-04-07 at 00.28.13

Sequence and Audit columns with Apex 5 and 12c

Introduction

In the good ol’ times database triggers were used to populate database columns like primary identifiers or audit columns.

Here is an example of such a trigger.
For sake of simplicity I concentrate on the insert trigger.

create table swe_demo (id number primary key
                      ,col1 number
                      ,col2 varchar2(30)
                      ,inserted_date date not null
                      ,inserted_from varchar2(30) not null);

create sequence swe_demo_seq cache 10000;

create or replace trigger swe_demo_bri_trg
  BEFORE INSERT ON swe_demo
  FOR EACH ROW
BEGIN
   -- record needs always a key
   IF :new.id IS NULL
   THEN
      :new.id := swe_demo_seq.NEXTVAL;
   END IF;

   -- timestamp of the last changes
   :new.inserted_date := SYSDATE;
   :new.inserted_from := COALESCE(v('APP_USER'), user);
END swe_demo_bri_trg;
/

What does it do?

  • The ID column is filled with a sequence value.
  • The inserted_date column is filled with sysdate.
  • The inserted_from column is filled with the current apex user or if that is not set, then the connected user is choosen.

It is time to revisit that functionality and see which new and maybe better conceptes are offered when the newest technologies are in place.

This was tested against Apex 5.0.1 and Oracle 12.1.0.1 .
It should work also in Apex 5.0.3 and Oracle 12.1.0.2

Solution

If you are just looking for the final code – here is my “perfect” solution.

create sequence swe_demo_seq cache 10000;
create table swe_demo
  (id number default swe_demo_seq.nextval primary key
   ,col1 number
   ,col2 varchar2(30)
   ,inserted_date date default sysdate not null
   ,inserted_from varchar2(30) default coalesce(
          sys_context('APEX$SESSION','app_user')
         ,regexp_substr(sys_context('userenv','client_identifier'),'^[^:]*')
         ,sys_context('userenv','session_user')
         )
      not null);

The inserted_from value is now more accurate.

You might have noticed that the need for an insert trigger is gone.
This increases performance considerably.

To insert 1 mill rows the old version needed more than a minute. This can be considered fast.
Elapsed: 00:01:12.904

The new version needs less than 6 seconds. This is a blizzard!
Elapsed: 00:00:05.598

I admit that the test was specifically set up to show a major difference. Feel free to set up your own more realistic test cases.

Still 12 times faster is really nice.

The test code to get this result can be seen in chapter TC4.

Considerations and test cases

How to find out about the current user

This line

COALESCE(v('APP_USER'), user)

from the example is really bad.

v(‘APP_USER’) is a bit slow, although not as slow as I suspected. It will not work on databases without apex installations. “v” is just a public synonym for an apex function that looks up session specific values in the apex repository. Surprisingly the “user” function is a much bigger drag. See TC1 below for performance comparisons.

In older apex versions a good way to find out who was logged in, was to read the client_identifier from the userenv context.

sys_context('userenv','client_identifier')

The structure of this did change between the different apex versions. In Apex 5 it now holds the APP_USER and the Session ID, e.g. SVEN:0123456789

A context is an extremly efficient way to read global session or application data.

Apex 5 introduced a new context with the namespace “APEX$SESSION”. This context is populated by the apex engine (APEX_050000.WWV_FLOW_SESSION_CONTEXT) and holds app_user, app_session and workspace_id. There is a good chance that future versions will add more parameters to this namespace.

See also: http://jeffkemponoracle.com/2015/11/24/apex-5-application-context/

To read the logged in user we fetch SYS_CONTEXT(‘APEX$SESSION’,’APP_USER’).
To read the user if there is something non-apex running, we should read the client_identifer. But there is more to consider.

database links

In many apex projects we do not insert into the local apex database, but into some remote project schema. This is often the case when I just add some reporting or monitoring solution in apex, without interfering with the real project database too much. So lets compare how the different contexts behave when done local vs. remote (over a db link).

12c introduced some changes to the userenv namespace. Especially the “current_user” is now deprecated.
Here is a list of all parameters that belong to the userenv namespace:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm#g1513460

The interesting ones are:

  • client_identifier
    Returns an identifier that is set by the application through the DBMS_SESSION.SET_IDENTIFIER procedure, the OCI attribute OCI_ATTR_CLIENT_IDENTIFIER, or the Java class Oracle.jdbc.OracleConnection.setClientIdentifier. This attribute is used by various database components to identify lightweight application users who authenticate as the same database user.
  • current_schema
    Name of the default schema being used in the current schema. This value can be changed during the session with an ALTER SESSION SET CURRENT_SCHEMA statement.
  • current_user
    Deprecated – Use the SESSION_USER parameter instead.
  • session_user
    For enterprises users, returns the schema. For other users, returns the database user name by which the current user is authenticated. This value remains the same throughout the duration of the session.
  • dblink_info
    Returns the source of a database link session. Specifically, it returns a string of the form:SOURCE_GLOBAL_NAME=dblink_src_global_name, DBLINK_NAME=dblink_name, SOURCE_AUDIT_SESSIONID=dblink_src_audit_sessionid
  • authenticated_identity
    Returns the identity used in authentication. In the list that follows, the type of user is followed by the value returned: …

For testing purposes I just made a loopback link to the same database but a different schema. The testscript can be found in section TC2.

The following tests were done:
1) An insert from an apex page into a view that included a db link to REMOTE_B
2) An insert from an apex page, directly into a local view (LOCAL_A) which was mapped to demo.swe_demo.
5) A direct database insert in the view with the DB link (remote insert)
6) A direct database insert in the view without the db link
7) an insert from a DBA “IAMDBA” using alter session set current_schema

This was the result

Id Some Text Inserted Date Inserted From App User Apex Session Client Identifier Current Schema Current User User Session User Authenticated Identity Dblink Info
1 apex remote insert 22-FEB-16 SVEN SVEN:4628609689353 DEMO DEMO REMOTE_B REMOTE_B REMOTE_B SOURCE_GLOBAL_NAME=DB_A.11623201
2 apex local insert 22-FEB-16 SVEN SVEN SVEN SVEN:4628609689353 DEMO DEMO APEX_PUBLIC_USER APEX_PUBLIC_USER APEX_PUBLIC_USER
5 direct remote insert 22-FEB-16 REMOTE_B DEMO DEMO REMOTE_B REMOTE_B REMOTE_B
6 direct local insert 22-FEB-16 LOCAL_A DEMO DEMO LOCAL_A LOCAL_A LOCAL_A
7 direct insert current_schema 22-FEB-16 IAMDBA DEMO DEMO IAMDBA IAMDBA IAMDBA

I did some more tests, for example using definer and invoker rights procedures, but they didn’t reveal any important differences.

The findings

APEX$SESSION is not populated via db link, but CLIENT_IDENTIFIER is.
Other than those two SESSION_USER has the best information and is always populated.

Therefore in an apex near environment the best expression to find out who inserted some record would be

coalesce(
   sys_context('APEX$SESSION','app_user')
   ,regexp_substr(sys_context('userenv','client_identifier'),'^[^:]*')
   ,sys_context('userenv','session_user')
   )

One should also notice that current_user returns the same values as current_session. This is contrary to the documentation in older database versions. And Oracles suggestion to use session_user instead is certainly correct, but you should be aware that then a different user might be stored than before.

Authenticated_Identity promises to be interesting in very specific security environments.

12c identity columns and default values

With 12c we can consider to use an identity column as our primary key. Also it is now possible to use a sequence as a default value for the column.

The base syntax for an identity column is

id number generated as identity

The base syntax for a default value column is

id number default swe_demo_seq.nextval

There are several differences between the two.
An identity column essentially prevents that values are inserted into this column. So that it is ensured that always the sequence was used to populate the data. The default value column uses the sequence only if null was inserted. This is essentially the same functionality what the trigger did.

It is possible to setup an identity column to behave almost the same as the default value column.

The extended syntax for such an identity column is

id number generated by default on null as identity (cache 10000)

I slightly prefer the default value column syntax for two reasons

  1. The default value syntax is also used for the two audit columns. So it is consistent to use the same mechanism.
  2. Default value columns allow more control over the sequence object.

I plan to write an extra blog post to discuss the differences in more detail.

TC3 shows an performance comparison between trigger logic, default value and identity column. Default values and identity are equally fast. But the trigger looses by a large margin!

Various testcases

For most cases the results should be consistent in other environments too. However always test it your own. Here are the scripts that I used. They might help to setup your own test cases.

TC1) Compare performance of different expressions to read the client identifer

The following plsql block was used and run

set time on
set timing on
declare
  v_result varchar2(100);
begin
  for i in 1..1000000 loop
  v_result := ##EXPRESSION##;
  end loop;
end;
/

This is the output for several tested expressions.
Watch out that some expressions return different results.

I always did three runs to see if the results are consistent and copied one of the elapsed times to the table.

sys_context('userenv','client_identifier')

00:00:02.436
This is the base. It should not be possible to go faster.

substr(sys_context('userenv','client_identifier'),1
            ,instr(sys_context('userenv','client_identifier'),':')-1)

00:00:04.288
Finds the colon and returns everything before that. But returns NULL if no colon is in the client_identifer.

substr(sys_context('userenv','client_identifier'),1, 
   coalesce(nullif(instr(sys_context('userenv','client_identifier'),':'),0)-1,
 length(sys_context('userenv','client_identifier'))))

00:00:06.311
A little complex to read, isn’t it…

regexp_substr(sys_context('userenv','client_identifier'),'^[^:]*')	

00:00:03.459
This is the winner!

translate(sys_context('userenv','client_identifier'),'A:0123456789','A')

00:00:05.663
Surprisingly slow

It surprised me that the REGEXP_SUBSTR expression was the best performing. Previous comparisons between regular expressions and substr/instr combinations always went in favor of substr. It seems either the internal code was optimized by oracle. Or more likely that this specific task (simple search from the beginning of the string) is well suited for a fast regexp search.

In general all expressions were fast. Interesting side note is that REGEXP_SUBSTR and TRANSLASTE were a tiny bit slower for the second identifier, while the other expressions performed equally fast.

Some other expressions:

user 

Elapsed: 00:00:20.652
Really really slow!

sys_context('APEX$SESSION','app_user')

00:00:01.549
Blazingly fast!

v('APP_USER')

00:00:05.646
Not as slow as I thought!

TC2) Analyze the value of differnt contexts with regards to DB links

This script can be run as DBA to setup a demo user.
Make sure you do not have a schema named “demo” already in your system.


create user demo identified by demo;
grant unlimited tablespace to demo;

drop table demo.swe_demo ;
drop sequence demo.swe_demo_seq;

create table demo.swe_demo 
                      (id number primary key
                      ,some_text varchar2(30)
                      ,inserted_date           date not null
                      ,inserted_from           varchar2(30) not null
                      ,c_app_user                varchar2(30)
                      ,c_apex_session            varchar2(30)
                      ,c_client_identifier       varchar2(100)
                      ,c_current_schema          varchar2(30)
                      ,c_current_user            varchar2(30)
                      ,c_user                    varchar2(30)
                      ,c_session_user            varchar2(30)
                      ,c_authenticated_identity  varchar2(100)
                      ,c_dblink_info             varchar2(100)
                      ,c_external_name           varchar2(30)
                      );

create sequence demo.swe_demo_seq cache 10000;


create or replace trigger demo.swe_demo_bri_trg
  BEFORE INSERT ON demo.swe_demo
  FOR EACH ROW
BEGIN
   -- record needs a key
   IF :new.id IS NULL
   THEN
      :new.id := swe_demo_seq.NEXTVAL;
   END IF;

   -- timestamp of last changes 
   :new.inserted_date := SYSDATE;
   :new.c_app_user               := v('APP_USER');
   :new.c_apex_session           := sys_context('APEX$SESSION','app_user');
   :new.c_client_identifier      := sys_context('userenv','client_identifier');
   :new.c_current_schema         := sys_context('userenv','current_schema');
   :new.c_current_user           := sys_context('userenv','current_user');
   :new.c_user                   := user;
   :new.c_authenticated_identity := sys_context('userenv','authenticated_identity');
   :new.c_session_user           := sys_context('userenv','session_user');
   :new.c_dblink_info            := sys_context('userenv','dblink_info');
   :new.c_external_name          := sys_context('userenv','EXTERNAL_NAME');
   
   -- recommended:
   :new.inserted_from        := coalesce(sys_context('APEX$SESSION','app_user')
                                     ,regexp_substr(sys_context('userenv','client_identifier'),'^[^:]*')
                                     ,sys_context('userenv','session_user')
                                     );
   
END swe_demo_bri_trg;
/

show errors

grant select, insert, update, delete on demo.swe_demo to SchemaB;

TC3) Performance comparison for populating PK columns

--------------------------------------------------------------------------------
-- swe 12c demo identity columns
--------------------------------------------------------------------------------
set time on
set timing on

-- old logic using trigger
drop table swe_demo ;
drop sequence swe_demo_seq;
create table swe_demo (id number primary key, col1 number, col2 varchar2(30));
create sequence swe_demo_seq cache 10000;

create or replace trigger swe_demo_bri_trg
  BEFORE INSERT ON swe_demo
  FOR EACH ROW
BEGIN
   -- record needs a key
   IF :new.id IS NULL
   THEN
      :new.id := swe_demo_seq.NEXTVAL;
   END IF;

END swe_demo_bri_trg;
/

-- Performance test to insert 1 mill records
insert into swe_demo (col1)
select level from dual connect by level &amp;amp;lt;= 1000000;
   
-- result
1,000,000 rows inserted.
Elapsed: 00:00:36.854

-- setup 12c logic using default value sequences
drop table swe_demo ;
drop sequence swe_demo_seq;
create sequence swe_demo_seq cache 10000;
create table swe_demo (id number default swe_demo_seq.nextval primary key, col1 number, col2 varchar2(30));


-- performance test to insert 1 mill records
insert into swe_demo (col1)
select level from dual connect by level &amp;amp;lt;= 1000000;

-- result
1,000,000 rows inserted.
Elapsed: 00:00:04.068


-- 12c logic using identity column 
drop table swe_demo ;
drop sequence swe_demo_seq;
create table swe_demo (id number generated as identity primary key, col1 number, col2 varchar2(30));

-- Performance test to insert 1 mill records
insert into swe_demo (col1)
select level from dual connect by level &amp;amp;lt;= 1000000;

--result 
1,000,000 rows inserted.
Elapsed: 00:00:08.626

There is a performance difference compared to the default value solution. This is purely based upon the sequence cache
The identity column was created with a default cache size of 20.

--Try to manually insert a row into the identiy column 
insert into swe_demo (id, col1)
values (null, -1);

SQL Error: ORA-32795: cannot insert into a generated always identity column
32795.0000 -  &amp;amp;quot;cannot insert into a generated always identity column&amp;amp;quot;
*Cause:    An attempt was made to insert a value into an identity column
           created with GENERATED ALWAYS keywords.
*Action:   A generated always identity column cannot be directly inserted.
           Instead, the associated sequence generator must provide the value.
;
-- use non default settings for the identity column
-- 12c logic using identity column 
drop table swe_demo purge;
create table swe_demo (id number generated by default on null as identity (cache 10000) primary key , col1 number, col2 varchar2(30));

-- Performance test to insert 1 mill records
insert into swe_demo (col1)
select level from dual connect by level &amp;amp;lt;= 1000000;

--result 
1,000,000 rows inserted.
Elapsed: 00:00:03.763    

-- test insert
insert into swe_demo (id, col1)
values (null, -1);

1 row inserted.

insert into swe_demo (id, col1)
values (-1, -1);

1 row inserted.

It seems as this is even a tiny bit faster than the default value solution. The test results were influenced heavily by other network or database activities.
Both execution times are very close. The top score however was done using an identity column.

TC4) Extend performance test TC3 with audit columns

--------------------------------------------------------------------------------
-- Adding audit columns to the previous example
-- Final comparison
--------------------------------------------------------------------------------
-- old logic 
drop table swe_demo purge;
drop sequence swe_demo_seq ;

create sequence swe_demo_seq cache 10000;
create table swe_demo (id number primary key
                      ,col1 number
                      ,col2 varchar2(30)
                      ,inserted_date date not null
                      ,inserted_from varchar2(30) not null);

create or replace trigger swe_demo_bri_trg
  BEFORE INSERT ON swe_demo
  FOR EACH ROW
BEGIN
   -- record needs always a key
   IF :new.id IS NULL
   THEN
      :new.id := swe_demo_seq.NEXTVAL;
   END IF;

   -- timestamp of the last changes
   :new.inserted_date := SYSDATE;
   :new.inserted_from := COALESCE(v('APP_USER'), user);
END swe_demo_bri_trg;
/

-- Performance test to insert 1 mill rows
insert into swe_demo (col1)
select level from dual connect by level <= 1000000;

1,000,000 rows inserted.
Elapsed: 00:01:12.904

-- new logic 
drop table swe_demo purge;
drop sequence swe_demo_seq;
create sequence swe_demo_seq cache 10000;
create table swe_demo
  (id number default swe_demo_seq.nextval primary key
   ,col1 number
   ,col2 varchar2(30)
   ,inserted_date date default sysdate not null
   ,inserted_from varchar2(30) default coalesce(
          sys_context('APEX$SESSION','app_user')
         , regexp_substr(sys_context('userenv','client_identifier'),'^[^:]*')
         ,sys_context('userenv','session_user')
         )
      not null);

-- Performance test to insert 1 mill rows
insert into swe_demo (col1)
select level from dual connect by level <= 1000000;

1,000,000 rows inserted.
Elapsed: 00:00:05.598

Side notes

EBR considerations

A trigger is an editionable object. A default column value is not editionable. So if the default expression needs to change, then this will be done in all editions at the same time. With a trigger we are able to choose.

Possible issues

The db_link_info context did not return what the documentation said.
This might be an issue specific to the oracle db version (12.1.0.1).

Security considerations

Consider if you want to trust the client_identifier. Any session can simply set this value and _identify_ as someone else. The apex$session context can not so easily be manipulated.

fragments

When using identity columns if you drop the table the automatically generated sequence (ISEQ$…) will stay. This is needed in case the table is flashbacked from the recycle bin. Therefore I always try to remember to purge the table.

drop table swe_demo purge;

Cleanup

Cleanup code for most of the generated objects:

drop user demo cascade;
drop table swe_demo purge;
drop sequence swe_demo_seq;

Apex 5 Universal Theme – Navigation Experiment #1

Task

The idea was to have a left sidebar menu where all the top level menu entries are always open. The reason is to optimize screen estate and avoid clicks when navigating to some sub menu.

Opening the second level menu entry

The following solution is a cheat. It does not really use a list with two different hierarchy levels. Instead it is using a normal side navigation menu. But the entries on the first level (top level entires) are enhanced with attributes, that will make some entries look more important, than other entries.

Here is the css to do so.


div.FlatHierachie-ItemMain{
font-weight:bolder;
font-size: 1.5em;
line-height: 1.5em;
text-shadow: 2px 2px 4px #000000;
}
div.FlatHierachie-Item {
font-weight:lighter;
font-size: 0.75em;
line-height: 1.5em;
}

This css will work only if the list template is changed.

Each entry for a top level menu point will get an additional class:

class="FlatHierachie-Item#A06#"

apex5_sidebar_expand

The following fields need to be changed:

  • List Template Current
  • List Template Current with Sublist Items
  • List Template Noncurrent
  • List Template Noncurrent with Sublist Items

The Sublist entries will stay as they were.

Additionally the Navigation list itself needs to be changed.

Each top level list entry will get an additional attribute number 06 set to “Main”.

apex5_sidebar_expand_navlist

All second level list entries will get there parent entry removed.
To remain the ordering of the list, it is advisable to RESEQUENCE the list entries first, before removing the parent information.

This is the most manual work to do. Also it is not so easy to get back to the old list, therefore we should first make a copy .

And this is how the result looks like:

apex5_sidebar_expand_after

Overview about other Experiments

APEX 5 Universal Theme – Analyzing and Modifing Navigation Menus

Preparation

I’m currently analyzing a lot of the navigation menu possibilities of the universal theme. The goal is to find ways to enhance the current navigation possibilities in various way. I’m not a webdesigner. I do understand a fair bit of HTML5 and CSS3, but I’m far from an expert there. But this is why I like Apex. In general we do not need to have an extensive design wisdom. My personal strength is on the database level. However I feel that especially with APEX5 it becomes even more useful to understand CSS.

Information gathering

Unfortunatly there is not much information available from oracle. Patrick Wolf made a nice list of Apex Blog entries. However most of them do show only how apex 5 behaves normally. Not like you can break out of the norm. Fortunatly the list gets longer and longer, so you might want to recheck it from time to time.

One should be used to analyze the html code using tools like code inspector and firebug.

Or you can “read” the css and js files. I did this a few times, just to see if I can find some more hidden options or reusable css classes.

John Synder blogged about the different widgets that are used in the page designer: http://hardlikesoftware.com/weblog/2015/04/29/apex-5-0-page-designer-meet-the-widgets/

How to test drive new layout settings

There are a couple of things I regularily do when experimenting with layouts, especially with the menu.

Since I’m usually not the only developer in a project, I do not want to confuse or disturb the others. Therefore some reasonable precautions seem necessary.

  1. Copy the whole application into the same workspace
  2. When playing around with the theme roller, create a new style and save that.

If you have static files set up, then you can directy use the result from the theme roller in the application copy and later in the original application. If not, then you can download and store the resulting css manually and reference it.

Experiments

#1 Trying to keep the first two menu hierachy levels open

The goal was to reduce the number of clicks for the users. One idea was to keep the first two levels in the menu hierarchy always open.

Different approaches tried :

  1. set the autocollapsible option for the navigation tree to false
    ==> No success so far
    The autocollapsible option is a default option for the tree widget. Not sure if I correctly used that, but changing it to false seemed to have no effect.
  2. use the class a-TreeView–noCollapse
    ==> No success so far
    This class should be considered during tree initialisation and by the expand/collapse events. I could add the class using a modified list template for the top level list entries, but that was not working.
  3. make second level hierachy entries look like top level entries
    ==> Seems possible with large changes to the list template and by adding some additional attributes.
  4. make all second level entries to top level entries and add an attribute to change the look like second level entries
    ==> this is the only one I sucessfully implemented. Not my favourite solution, but only 2 hours of work.
    Here is a blog entry to show the steps how to do it.

#2 Trying to create a “Windows Startmenu” like navigation

The idea is to have a menu on the left hand side, where the sub menu entries open simliar to the windows start menu (if you still remember that you metro users!). One problem is to have the sub menus overlap the normal page body. This is solved for the top menu bar widget, but not so easily done with the side bar tree widget.

Different approaches possible:

  1. Use the top menu logic for the side.
    ==> This is tricky, but I got some mediocre “in between” results.
    So far the additional work to do is more time than I wanted to spent. So I stopped the experiment. Might want to come back to that one day.
  2. Using Twitter Bootstrap directly to circumvent the whole apex internal logic.
    ==> This is definitly possible. I did the same with a Topbar Menu in Apex 4.2 . I think there is even a plugin from Dan McGhan that does it. However with the new apex 5 possibilities, this is not my preferred way.

#3 Decreasing the size of the sidebar menu

In one project we already used a tree based menu. After migration to Apex5 and Universal Theme, this menu was way larger than before. Here is how to decrease the line height for the side menu: https://svenweller.wordpress.com/2015/09/24/apex-5-universal-theme-navigation-experiment-3/

#4 Coloring the Menu

The idea is to enhance the side bar menu by having different regions colored differently. This works but I discovered a few quirks during the process of doing so.

This is the result

apex5_sidebar_colored_after

And here is how to do it: https://svenweller.wordpress.com/2015/11/05/apex-5-universal-theme-navigation-experiment-4/

 

Apex 5 Universal Theme – Navigation Experiment #3

Decreasing the size of the side navigation menu

In one project we already used a tree based menu. After migration to Apex5 and Universal Theme, this menu was way larger than before. Especially after applying the “keep two menu” hierarchies open change (i blog about that later), I decided to play a bit with the theme roller settings and try decrease the height for each menu entry.

Here is the result. if you add the following css to your theme, the height of the typical menu item will decrease from 40px to 30px.


.t-TreeNav .a-TreeView-node--topLevel > .a-TreeView-row,
.t-TreeNav .a-TreeView-node--topLevel  .a-TreeView-label,
.t-TreeNav .a-TreeView-node--topLevel > .a-TreeView-toggle,
.t-TreeNav .a-TreeView-node--topLevel > .a-TreeView-content,
.t-TreeNav .a-TreeView-node--topLevel  .a-TreeView-content .fa {
height: 30px;
line-height: 30px;
}

.t-TreeNav .a-TreeView-node--topLevel > .a-TreeView-content .a-TreeView-label {
line-height: 30px;
}

.t-TreeNav .a-TreeView-node--topLevel > .a-TreeView-toggle {
padding: 0px;
}

.t-TreeNav .a-TreeView-node--topLevel > .a-TreeView-content .fa {
padding: 0px 9px 8px 13px;
}

.t-TreeNav .a-TreeView-node--topLevel > ul > .a-TreeView-node {
padding-left: 32px; padding-top: 0px; padding-bottom: 0px;
}

.t-TreeNav .a-TreeView-node--topLevel ul .a-TreeView-content {
line-height: 0px;
}

Don’t get hilarious by the nice green color. This was just me experimenting. The css will not change your color settings.

before/after: apex5_sidebar_decrease_beforeapex5_sidebar_decrease_after

As you can see the total size decreases considerably while maintaining font sizes.

I did only change the top level entries, since they take up the most space. The sub level entries where just alined a little bit to the right. So that they match their main entries better. This depends also upon having icons or not in the sub entry.

One place where you can add the css is the custom section of the theme roller.

apex5_sidebar_decrease_TR  Simply copy and paste it there and see the change immediatly in your application.

Overview about other Experiments

Apex 5 Upgrade – correct colspan error

After upgrading to Apex 5 and switching to the new Universal Theme you might encounter an error regarding column spans. The error message will look similiar to this: “Label of Page Item P1_XXX cannot be rendered as the label column span grid setting for this page item is invalid …”

apex5_upgrade_colspan_en or the german version apex5_upgrade_colspan_de

I had the problem for an application which was gradually upgraded from Apex 4.0 to Apex 4.1 to 4.2 and now to Apex 5. During this, almost all form page items had the attribute colspan set to 1. This makes trouble with the new Universal Theme. I had more than 1500 items that needed to be changed. So clearly this was not a task to do manually.

Reason

The reason for this error is based upon how the Universal Theme handles items and its labels in combination with the page template. The standard page will use a fixed number of 12 columns in a grid to render anything. The labels will span 3 columns already. This is the default. If you have a column span set of 1, then this will include the label column span. And that is what the error message is all about.

Btw: This is also one reasons why the form pages now “waste” much more screen space than before. I changed this in the page template to a column span of 2. If you change it to 1 then this might be too small. The labels will then often be wrapped onto the next line.

Finding all items

We can use the apex dictionary to find all items that are affected.

Application Builder / Utilities / Item Utilities / All Page Items

I set a filter on “Column Span is not null”. Then the report shows many items we have in the application that have thios attribute set.

Another way to do the same is to run the follow select statement:

select * 
from apex_application_page_items
--where workspace = 'MYWORKSPACE'
where workspace != 'INTERNAL'
and application_id = 200
and column_span = 1;

Single page correction
If it is a low number of pages that is affected, we can do the change for all items on one page. From the report that shows all the items, we can navigate to the multi edit “pages” form.

apex5_upgrade_colspan_page

There we can update all items for one page easily and fast.

All pages mass correction

If we have more than just a few pages, an even faster way is needed.

You need DBA privileges to do this mass update.
As an alternative you could try the update on the view via the SQL workshop. I didn’t test that.
An update on database level to the view will give you
ORA-01031: insufficient privileges
The update can be done directly on the apex internal table wwv_flow_step_items.
Here is how to mass update your application and set the column span to NULL.

The example changes all items in application 200. Adapt to your workspace and application id.

-- set environment
alter session set current_schema = apex_050000;
execute wwv_flow_security.g_security_group_id := 200000;

-- pre update check
select count(*) --colspan
from wwv_flow_step_items i
where flow_id = 200
and colspan = 1;

-- do the change
update wwv_flow_step_items i
set colspan = null
where flow_id = 200
and colspan = 1;

-- does the result match the pre update check
1,128 rows updated.

-- save 
commit;