APEX allows you to set the maximum idle time for a given session. This is an application level attribute that is set in Shared Components > Security Attributes. Under the Session Management there is a setting for Maximum Session Idle Time in Seconds which defaults to 3600 seconds:

When a new session is created in APEX, the max idle time is “copied” from the application settings directly into the user’s session settings. If you change the setting at an application level only new sessions will use the new value. Existing sessions will use the old value.

The following example demonstrates when changes take effect. The current max idle time is set to 3600 and one session is active (i.e. one user is logged in):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- App setting
select aa.maximum_session_idle_seconds
from apex_applications aa
where aa.application_id = 101
;

   MAXIMUM_SESSION_IDLE_SECONDS
_______________________________
                           3600

-- Active sessions
select
  ws.apex_session_id,
  ws.session_max_idle_sec
from apex_workspace_sessions ws
;

   APEX_SESSION_ID    SESSION_MAX_IDLE_SEC
__________________ _______________________
    10974491127217                    3600

The Maximum Session Idle Time in Seconds setting is is changed to 200 seconds and then a new user logs in (i.e. a new session is created):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- App setting
select aa.maximum_session_idle_seconds
from apex_applications aa
where aa.application_id = 101
;

   MAXIMUM_SESSION_IDLE_SECONDS
_______________________________
                            200

-- Active sessions
select
  ws.apex_session_id,
  ws.session_max_idle_sec
from apex_workspace_sessions ws
;

   APEX_SESSION_ID    SESSION_MAX_IDLE_SEC
__________________ _______________________
     3565745405186                     200
    10974491127217                    3600

You can see that the older session (10974491127217) still retains the old max idle time (3600 seconds) whereas the new session (3565745405186) uses the new value (200 seconds).

When testing these settings it’s important that you create a new session each time a change is made.

In APEX you can save Interactive Reports (IR) default reports as both Primary and Alternate. Both of these types of default reports are available to all users. To save an alternate report, click the Actions button then Report > Save Report (Note you must be logged into the APEX developer to save default reports). Select the option to save As Default Report Settings and the save screen will look like below:

In the APEX developer you can now see the saved report (Managers in this case) and modify its alias. By default a random number will be assign as the alias.

You can then use the alias to link to a saved report anywhere in your application.

The following query will get a list of all the default reports, their aliases, and a report link for a given page:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

select 
  pr.region_name, 
  nvl(pir.report_name, 'Primary') report_name, 
  pir.report_alias, 
  pir.report_link_example
from dual
  join apex_application_page_ir_rpt pir on 1=1
  join apex_application_page_regions pr on 1=1
    and pr.application_id = pir.application_id
    and pr.page_id = pir.page_id
    and pr.region_id = pir.region_id
where 1=1
  and pir.application_id = :app_id
  and pir.page_id = :app_page_id
  and pir.status = 'PUBLIC'
;

REGION_NAME REPORT_NAME    REPORT_ALIAS  REPORT_LINK_EXAMPLE
___________ ______________ _____________ __________________________________________
Report 1    Primary        167050        f?p=&APP_ID.:3:&APP_SESSION.:IR_167050
Report 1    Sal GTE 2000   180055        f?p=&APP_ID.:3:&APP_SESSION.:IR_180055
Report 1    Managers       MANAGERS      f?p=&APP_ID.:3:&APP_SESSION.:IR_MANAGERS

When upgrading an application to APEX 19.x, if you have a Switch page item it probably still looks like the old “Yes/No” boxes:

To change to the new, more modern, Switch item you need to change this for the entire application. Do to so go to Shared Components > Component Settings > Switch. From there set the Display Style to Switch. All the Switch items will now look like:

For all new applications created in 19.x the Display Style is already Switch so there is nothing to do in order to have this type of Switch.

Interactive Reports (IR) in APEX have a attribute to include a link column:

When you run the page the link column takes up more space than necessary:

You can reduce the width by adding the following CSS to your application: Note width may vary depending on circumstances)

1
2
3
4
5

th#LINK,
td[headers=LINK]
{
  width:40px;
}

Column links will now look like:

This article is part of a series on using Docker for CLI tools. You can read the original article here.

Oracle SQL*Plus is the Command Line (CLI) tool to connect to Oracle. It is part of the Oracle Instant Client set of tools. I’ve always found the installation process to be cumbersome as it requires a few steps (not a straight install or base executable like SQLcl).

Oracle has made the Instant Client files now available on a pre-built Docker image. This article covers how to download the Docker image and use it to run SQl*Plus in a container rather than a local install.

Accept License Agreements

Oracle has its own container registry. Before using any of their pre-built images you must accept the license agreements (this only has to be done once).

• Go to container registry
• Search for instantclient (all one word)
  • Select the instantclient link
• Sign In (image below) and accept the license agreements

Docker Login to Oracle’s Container Registry

In your terminal run:

1

docker login container-registry.oracle.com

You will be required to enter in your Oracle SSO login credentials (only need to do this once).

Setup sqlplus alias

In ~/.bash_profile or ~/.zshrc add the following alias

1
2
3
4
5
6
7
8
9

alias sqlplus='docker run -it --rm \
  -e ORACLE_PATH=/oracle/ \
  -e TNS_ADMIN=\$TNS_ADMIN \
  -w /myhost \
  --network="host" \
  -v ~/Documents/Oracle/:/oracle/ \
  -v `pwd`:/myhost \
  container-registry.oracle.com/database/instantclient:latest \
  sqlplus '

Run SQL*Plus

Start a new terminal (so the alias is registered) and just run sqlplus as you normally would.

Considerations

The current image from Oracle is using Instant Client 12.2.0.1.0 where as at the time of writing the most current version is 19.3.0.0.0. If you want to build your own image so you can run the latest version, you can use Oracle’s Docker Image files (just search for the version you want).

This article is part of a series on using Docker for CLI tools. You can read the original article here.

Oracle SQLcl is the new command line (CLI) tool for connecting to the database. I prefer to use it instead of SQL*Plus as it has a lot of new features and is constantly upgraded.

SQLcl is fairly easy to install. Just download and unzip the file then you can run it. The caveat is that it requires Java Runtime Engine (JRE) 8. For most people the JRE dependency isn’t an issue but for some it may cause problems.

I built a Docker image for SQLcl and it is available on Github. The catch is that you need to clone the repo and build the image yourself (due to Oracle licensing restrictions). If this changes in the future I’ll update the repo so a Docker image is available on Docker Hub.

The Github page covers how to clone and setup the alias to run SQLcl via a Docker container. A demo of it in action is included below. A few notes about the demo:

• The initial command docker ps |grep sql shows all the active docker containers
• SQLcl is run via a bash alias (see Github instructions for how to do this).
  • It kicks off my local login.sql script that is on my laptop (not on the docker container).
• docker ps |grep sql is run again (in different terminal) to show that SQLcl is actually being run as a Docker container

A while ago I wrote an article called Docker as a Micro Service which covered how I used Docker to run the Alexa CLI tool. This was my first time using Docker for a CLI application.

Since then, I’ve started to Dockerize other CLI tools and have found it very useful. Before I continue, I’ll admit there’s been some additional overhead both running a container for each command and also getting the Docker images correct. It has solved a few issues for me:

• I no longer worry about OS upgrade/changes on my laptop as my CLI commands all “reside” in the Docker image
• I avoid cluttering my system with a bunch of tools that can be a pain to remove if I don’t like them
• Avoid all the installation, dependency issues, etc
• When I change laptops I don’t need to reinstall all my tools. Docker will fetch them automatically the first time I call them.
  • This is also true for working with multiple machines

Going down the path of Dockerizing my CLI applications I’ve learned a few things that others may find helpful looking to take the same approach:

• Use `pwd` to evaluate your current directory as a mount volume to the container (demo in next point)
• Use single quotes around the alias definition so that expressions aren’t evaluated until run time. Ex:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# Double Quote issue
alias dummycommand="docker run -it --rm \
  -v `pwd`:/opt/node_app \
  somedockerimage:latest"

# This will always (no matter where I call dummycommand from) 
# map "~/" on my laptop to "/opt/node_app" on the container

# Single Quote (Solution)
alias dummycommand='docker run -it --rm \
  -v `pwd`:/opt/node_app/app \
  somedockerimage:latest'

# This will always map the current directory I'm in on my 
# laptop to "/opt/node_app/app" on the container

In the next few articles I’ll write about some of the CLI tools I use and how I’ve moved them to Docker.

• Docker Image for Oracle SQLcl
• Docker for Oracle SQL*Plus

When working with Git (or any version control system) most developers tend to commit all the changes in a given file. What if you wanted to only commit part of the file? This situation can occur in a few situations, such as:

• Code not ready to be committed
• Working on two tickets at once and want separate commits for each set of work
• Some TODO comments that you don’t want in the codebase

Git has a feature called interactive staging which lets you select specific lines of code you want staged (and then committed). The demo below highlights this using Visual Studio Code‘s (VSCode) Git tools.

In my current file I have the following:

We can divide up the “new code” into two sections. The first select statement should be committed but the second statement (staring with -- TODO mdsouza) should not be committed.

If the file needs to be committed without interactive staging a few options exist:

• Commit everything
• Cut out the sections I don’t want in and paste them back in after commit
• Copy file (as a backup), remove sections I don’t want, commit, restore backup

Neither of these options will lead to good results and take additional time. Using VSCode you can do interactive staging in multiple ways. The following shows how to stage specific lines.

A few things to note from the demo:

• Once staged, test.sql was still marked as having both changes and staged changes
• Only selected lines were staged (then committed)

Note: In Source Tree (a free Git UI from Atlassian) this feature is called hunks.

Since finding out about interactive staging I’ve been using it to restrict what goes into each commit. It’s allowed me to keep my commits more concise along with not having to commit work that isn’t ready for others (ex: personal TODO comments).

Sometimes in your PL/SQL code you need to query a table which may or may not contain what you’re looking for. This post will cover some different ways of doing it.

The problem

Suppose you need to find an employee’s ID named SAM. If he doesn’t exist in the table then insert a new employee record.

Side note: Technically you can use a merge statement for this functionality but want to highlight that a row may not be found.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

declare
  l_empno emp.empno%type;
begin
  select e.empno
  into l_empno
  from emp e
  where e.ename = 'SAM';

  if l_empno is null then
    -- ... insert new emp
  end if;

  -- ...
end;
/

-- Errors
ORA-01403: no data found
ORA-06512: at line 4
1.     00000 -  "no data found"

An error is raised if the employee (SAM in this case) isn’t found. Here are some options to get around this.

Explicit exception block

This is the most correct way to know if the value exists or not. Some don’t like to use this approach as it can clutter code (due to additional lines of code), on the positive side it is very explicit.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

declare
  l_empno emp.empno%type;
begin

  begin
    select e.empno
    into l_empno
    from emp e
    where e.ename = 'SAM';
  exception
    when no_data_found then 
      l_empno := null;
  end;
  
end;
/

Using max

Using an aggregate function will always return a value even if no rows are returned. For example:

1
2
3
4
5
6
7
8

select nvl(max(dummy), 'still returns value') val
from dual
where 1=2 -- Always false
;

VAL          
-------------
still returns value

Using this logic with the base example we can do:

1
2
3
4
5
6
7
8
9
10
11

declare
  l_empno emp.empno%type;
begin
  
  select max(e.empno)
  into l_empno
  from emp e
  where e.ename = 'SAM';
  
end;
/

I’ve used this in the past, however it comes with a caveat. If used on a column that isn’t unique it will still return one value. If multiple values are present (ex: all emps who’s job is MANAGER) it can lead to false positives.

Using left join

Similar to the previous option, selecting from dual (always returns a row) we can left join to optionally find the value.

1
2
3
4
5
6
7
8
9
10
11
12

declare
  l_empno emp.empno%type;
begin
  
  select e.empno
  into l_empno
  from dual
    left join emp e on 1=1
      and e.ename = 'SAM';
    
end;
/

I prefer this approach compared to the max approach since if multiple values are possible an error is returned (which in most cases we want):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

declare
  l_empno emp.empno%type;
begin
  select e.empno
  into l_empno
  from dual
    left join emp e on 1=1
     and e.job = 'MANAGER' -- Will return multiple values
  ;   
end;
/

-- Error
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5

Suppose you have a set of data in Oracle, how can you find any other matching sets of data? This is different then most queries in SQL which just have “standard” predicates. For example most queries answer the question “find me all cars that are red“ (where color = 'red') whereas set comparison is answering the question “find me all cars that are the same as a given set of cars“.

A good example of this problem is if you need to find orders that are exactly the same as a given order. An order can have multiple products in it. It’s not as easy as comparing one row to another, rather sets of rows to other sets of rows.

Set Theory

Before continuing it’s important to go over some basic set theory. One way to think of sets are as arrays of data. When comparing for equivalence of sets we need to ask two questions:

• Is everything in A in B? (i.e. A - B = 0)
• Is everything in B in A? (i.e. B - A = 0)

At first glance, the second point may seem unnecessary but it isn’t as A may be a subset of B.

Take for example the following arrays:

• A = 1,2,3
• B = 1,2,3,4

In the above case, everything in A is in B, however everything in B (i.e. 4) is not in A.

Example

Suppose we have a table (tab_a) and we want to find any other tables with the exact same column definition.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- Base table to be compared against
create table tab_a (
  id number,
  first_name varchar2(255),
  birth_date date
);

-- Close to tab_a but has an additional column (last_name)
create table tab_b (
  id number,
  first_name varchar2(255),
  last_name varchar2(255),
  birth_date date
);

-- Same as tab_a
create table tab_c (
  id number,
  first_name varchar2(255),
  birth_date date
);

The above script will create three tables. If we start with tab_a only tab_c is the exact same. Using the set theory logic above, and the minus set operator clause in Oracle you can find matching tables using the following query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

select at_match.table_name
from dual
  -- Using the all_tables view so we have a large data set to compare against
  join all_tables at_src on 1=1
  join all_tables at_match on 1=1
    -- Exclude the same table
    and at_match.table_name != at_src.table_name
where 1=1
  and at_src.table_name = 'TAB_A'
  -- A - B = 0
  and not exists (
    select atc.column_name, atc.data_type
    from all_tab_columns atc
    where atc.table_name = at_src.table_name
    minus
    select atc.column_name, atc.data_type
    from all_tab_columns atc
    where atc.table_name = at_match.table_name
  )
  -- B - A = 0
  and not exists (
    select atc.column_name, atc.data_type
    from all_tab_columns atc
    where atc.table_name = at_match.table_name
    minus
    select atc.column_name, atc.data_type
    from all_tab_columns atc
    where atc.table_name = at_src.table_name
  )
;

TABLE_NAME
----------
TAB_C