How to Develop with Multiple APEX Developer Tabs in Firefox

Eventually all APEX developers need to have multiple versions of the development environment open at the same time. A simple example of this is if you’re working with two pages (Page 1 and Page 2) that are dependant on one another. You constantly have to toggle between them as you can’t have two tabs open for development (one for P1 and one for P2).

Most people either open a different browser or open a new browser tab in Private or Incognito mode. Now Firefox allows you to get around this issue by having Multi-Account Containers.

firefox-multi-account-containers.png

Multi-Account Containers segregate your cookies by containers. Aside from APEX development you can also have multiple instances of Gmail or Office 365 opened in different container tabs.

For now you must install the Multi-Account Containers Plugin (built by Mozilla) as it’s not included by default. Note I use the plugin on Firefox Developer Edition with no issues.

How to Setup Multiple APEX Environments using Docker and Oracle 12c

I’ve written a few posts on how to Dockerize your Oracle and APEX setup (post 1, post 2). I’m constantly refining the process as I learn new things and different Docker images become available.

I was writing a third post on setting up multiple APEX environments using Docker and Oracle 12c. The post was getting long and included many steps. I decided to move the instructions to Github as it solves two problems:

  • I can easily keep it up to date with the latest steps I do to create my Docker containers
  • Others can contribute to the instructions (and/or include scripts) by making pull requests (PR)

To view my latest Docker setup you can view my docker-oracle-setup page on Github. If you have any corrections, suggestions, or enhancements please create issues or submit a PR on the repository.

How to Convert Dec to Any Base in SQL

If you want to convert decimal (base 10) to to hexadecimal (base 16) in SQL you can do so easily with the built in to_char functionality:

1
2
3
4
5
select trim(to_char(300, 'XXXXX')) dec2hex
from dual;
DEC2HEX
12C

What if you wanted to convert a decimal number to base 20 or any other base? This question recently came up when I was looking to add a transactional number generator for OOS Utils. The goal of the ticket is to convert a sequence number into a human readable number (ex: invoice number). It can easily be solved using PL/SQL (solution below) but I wanted to see if I could do it in SQL as it presents an interesting problem.

The Formula

For some, this section will go down memory lane to your college / university years where you had to convert dec to hex by hand. It’s important to understand the formula to convert dec to hex (or any base),

Using the first example, this is how to convert 300 to 12C “by hand”: Note Q = Quotient, R = Remainder

  • 300/16 = Q: 18, R: 12 (hex: C)
  • 18/16 = Q:1, R: 2 (hex: 2)
  • 1/16 = Q: 0, R: 1 (hex: 1)

The steps above produce the hexadecimal number 12C. More importantly, each value is dependent on the calculation of the previous line. This isn’t easy to do in SQL.

PL/SQL Function

The following function will convert dec to hex in PL/SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
create or replace function dec2hex (
p_num in integer)
return varchar2
as
l_return varchar2(255);
l_quotient integer;
l_remainder integer;
c_base constant pls_integer := 16;
begin
l_quotient := p_num;
while l_quotient > 0 loop
l_remainder := mod(l_quotient, c_base);
l_quotient := trunc(l_quotient / c_base);
-- A=10, B=11, ... F=15
l_return := substr('0123456789ABCDEF', l_remainder+1, 1) || l_return;
end loop;
return l_return;
end dec2hex;
/

As you can see PL/SQL uses the quotient from the previous loop to calculate the current loop’s remainder.

SQL Solution

In SQL, finding the value of the previous row is easy using an analytic function such as lag. If you’ve never used analytic functions before, Connor McDonald has a great series on youtube about them.

Using the previous row’s calculated value for the current row is more difficult and can’t be done using analytic functions. This is where the model clause comes in. It’s one of the lesser known features in Oracle SQL but very powerful in the right circumstances.

The query below will do dec to hex (or any other “base X”) conversion. For demo purposes I have left the select ... from my_data portion uncommented to highlight the model clause. To do a full translation uncomment the last few lines.

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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
var x number;
var base number;
:x := 300;
:base := 16;
with
-- Find how many loops we'll need to convert dec to basex
lvls as (
select level lvl
from dual
-- The <= logic will return the number of characters required for conversion
connect by level <= ceil(log(:base, :x)) + decode(log(:base, :x), ceil(log(:base, :x)), 1,0)
),
-- Alphabet 0..Z
-- Where 0-9, A=10, B=11 ....
alphabet as (
select
level-1 num,
case
when level-1 < 10 then to_char(level-1)
else chr( ascii('A')+level-1-10)
end letter
from dual
connect by level <= :base
),
-- Returns rows for all the Quotient and Remainder in dec value
my_data as (
select
lvl,
quotient,
remainder
from lvls
model
return all rows
dimension by (lvl)
measures( 0 remainder, 0 quotient)
rules (
-- Order matters here. I.e. R must come after Q so R can "see" Q
-- cv docs: https://docs.oracle.com/database/122/SQLRF/Model-Functions.htm#SQLRF51210
quotient[lvl] = trunc(nvl(quotient[cv(lvl)-1], :x) / :base),
remainder[lvl] = mod(nvl(quotient[cv(lvl)-1], :x), :base)
)
)
-- For demo purposes
select md.*, a.letter
from my_data md, alphabet a
where 1=1
and md.remainder = a.num
order by md.lvl
-- Uncomment below for dec2hex conversion
--select
-- listagg(a.letter, '') within group (order by md.lvl desc) basex
--from my_data md, alphabet a
--where 1=1
-- and md.remainder = a.num
;
LVL QUOTIENT REMAINDER LETTER
1 18 12 C
2 1 2 2
3 0 1 1

Final Thoughts

The model clause is extremely powerful when used to solve problems it was intended for. The toughest thing to do is learn and understand how it works with all it’s options/function. If it’s any help, I’m still learning more about the model clause and by no means an expert.

If you do some interesting things with a model clause please blog about it and send me a message as I can list future posts in this article.

Reset Authorization Cache

APEX Authorization Schemes determine if a user is allowed to access APEX components (such as page, item, region, etc). Note: It’s common to confuse them with Authentication Schemes which determine if a user is allowed to login to the the application.

If an Authorization scheme is applied to many components within an application it has the potential to be run many times. To help minimize the performance impacts an Authorization Scheme may have, they can be cached using the following options:

  • Once per session
  • Once per page view
  • Once per component
  • Always (No Caching)

Suppose an Authorization Scheme is configured to Once per session and this Authorization Scheme is applied to a region on Page 1. The first time the region is to be displayed, APEX will run the Authorization Scheme to determine if it can run/display the region. The authorization result will then be cached so all other components that reference the same Authorization Scheme will use the cached result.

What happens if something changes with the system that requires the Authorization to be re-evaluated? Thankfully there’s an API for that: apex_authorization.reset_cache.

I recently encountered the need to reset the Authorization cache on an application where users had multiple roles but could only “play” one role at a time. Example roles include Admin, Manager, User. Each time a user changed their role I needed to reset all the Authorization schemes as the role they played significantly affected the access they had to the system.

Listagg for IR Column Break Report

If you’ve used Interactive Reports (IR) in APEX for a while you may find a need to display a delimited list of values next to a sum row. The following screenshot shows this problem. Suppose you wanted to have a comma delimited list of names for each job such as FORD,SCOTT in the aggregation (sum) row.

report-problem.png

In SQL this is easy to do using the listagg function. unfortunately listagg isn’t available as an IR aggregation option (Actions > Data > Aggregate). I recently needed to do this for an IR. The rest of this post will cover setup along with the JavaScript (JS) solution.

Setup

  1. Create an Interactive Report: select ename, sal from emp
  2. On the IR go to Actions > Format > Control Break and Select Job as the column (shown below)
    control-break.png
  3. On the IR go to Actions > Data > Aggregate and select Function: Sum and Column: Sal as shown below.
    aggregation-sum.png

The resulting report should look like the first screenshot at the begging of this article.

Solution

The JavaScript below will add in the necessary listagg for the Ename column for the IR.

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
35
//Set to the displayed header name
var headerName = 'Ename';
var headerNum; //ID for TH header object
// Find the header to listagg
$('a.a-IRR-headerLink').each(function(){
var $this = $(this)
if($this.text() == headerName){
headerNum = $this.closest('th.a-IRR-header').attr('id');
}
});
$('.a-IRR-aggregate-value').each(function(){
var $aggRowTr = $(this).first().closest('tr');
var $prevRow = null;
var listaggHtml = '';
// Loop over all the rows until next next column break
while (true){
$prevRow = !$prevRow ? $aggRowTr.prev('tr') : $prevRow.prev('tr');
if ($prevRow.children('th').length > 0){
// If TH is detected it's the end of the IR column break
break;
}
else{
listaggHtml = $prevRow.children(`td[headers*="${headerNum}"]`).text() + ',' + listaggHtml;
}
}//for
// Regex is to remove trailing comma
$aggRowTr.children(`td[headers*="${headerNum}"]`).html(listaggHtml.replace(/,$/gi, ''));
}); // ('.a-IRR-aggregate-value').each

After running the JS code the report will look like:

report-solution.png

A few comments about this solution:

  • This code was for a personal report and thus the JS hasn’t been optimized or properly tested on all browsers
  • Depending on your needs you may want to move it to a Dynamic Action
  • If you have time you may want to convert this to an APEX plugin. If so please share on APEX.world plugins

Top Oracle SQL Developer Data Modeler Settings

I’ve started to use SQL Developer Data Modeler](http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html)(SDDM) a lot for a project and realized that pretty much everything is configurable. The problem is that there’s no single area to search all the different settings and even the settings’ search boxes don’t filter for all the options. To get around this (and for my own references) I’ve included some settings that I found very useful. I’ll try to keep this post up to date as I continue to explore SDDM.

Notes

  • Jeff Smith has blogged about a lot of the different settings. For a more extensive list check out his blog.
  • When Preferences is mentioned it’s Tools > Preferences in Windows and Oracle Data Modeler > Preferences in Mac.
  • I did not include any scripting (design rules, transformations, or DDL transformations) in this list. I’ll write a separate article on that.

Adding PROMPT Statements in DDL

SDDM can generate DDL statements for you either by clicking the DDL button (1) or synchronizing the model with a schema (2):

It’s always helpful to have prompt statements before each DDL change to help debug where an issue occurred. To do so open Preferences and check Include PROMPT Command (for Oracle only):

All DDL will then include a prompt statement like:

1
2
PROMPT CREATING TABLE 'MY_TABLE';
CREATE TABLE MY_TABLE ...

12c Identity Column or Auto Generate Sequences

Depending on your database version you may want to either have sequences automatically generated for you or not. In my case I was using a 12c instance and was going to use Identity Columns for primary key IDs. I didn’t want sequences for each table to be automatically generated. To toggle this feature open Preferences and change the options in the image below.

More on the Identity Column settings here

DDL Format - Lowercase and Tabs

If you’re really picky on the format of the DDL that is generated you can modify some of the settings in Preferences > Format:

Naming Standard Templates

When adding primary keys (PK) and various other constraints a default name is always generated. You can determine how this name is generated by modifying the design’s properties settings:

More information on different variables can be found here

Short Table Names

In most DB instances all object names are limited to 30 characters (this is changed in 12c but most organizations still haven’t enabled 128 characters). When using naming standards this can cause some issues because just referencing <table_name> may not work as its already 30 characters. Note <table_name> isn’t a real variable in SDDM.

You can define a short name (SDDM calls it an Abbreviation) for each table via the table properties. Setting the Abbreviation is also very helpful when generating DDL statements or constraints from scripts.

Docker Oracle and APEX

Update: I’ve moved my setup process to Dockerize Oracle and APEX to Github. It will be maintained with the latest steps I’m using it. The repository is docker-oracle-setup.

Last week I needed to test an Oracle 12.2 feature and got hooked on Docker. I wrote an article on how to setup an Oracle DB 12.2 Docker container. This solved my goal to test the 12.2 feature. Roel Hartman then wrote a followup article about how to setup an Oracle database in a Docker container but preserve the data on his laptop. I.e. if the container was deleted or needed to be rebuilt his database data wouldn’t be lost.

Roel’s article inspired me to setup an entire Dockerized Oracle and APEX environment, thus replacing my current local VM infrastructure. I took a lot of notes about the process. The result is this blog post which contains everything I did to create my containers and link them together. To get it all working I used the following articles and all my scripts are a result of a combination of the code found in the links.

Background

A few things to keep in mind that will help when reading the rest of this article:

  • All my scripts are Linux / MacOS focused. If you use a Windows machine you’ll need to translate
  • I specifically made reference to “your laptop” to emphasize what was run “on your machine” vs “in a docker container”

The following key configurations were used for the containers:

Port Mapping

Container Port Description
oracle 32712 TNS listener
ords 32713 ORDS

Passwords

Container Username Password Description
oracle sys Oradoc_db1
oracle admin oracle Workspace Internal for APEX admin

Download Files

Due to licensing restrictions I can’t host/provide these files in Github or elsewhere. As such you’ll need to download them manually. Download the following files and store them in your Downloads folder

Laptop Folder Structure

The following script will create a folder structure that looks like:

Path Description
~/docker root
~/docker/apex To host APEX installation and images for each version
~/docker/apex/5.1.3 APEX 5.1.3 installation files
~/docker/oracle Hold Oracle 12.2 data files
~/docker/ords ORDS Dockerfile (to build ORDS image)
~/docker/tmp Temp folder
1
2
3
4
5
6
7
8
9
10
mkdir ~/docker
# The APEX folder structure allows for multiple versions of APEX to be hosted by different ORDS instances
mkdir ~/docker/apex
mkdir ~/docker/apex/5.1.3
# To store oracle data
mkdir ~/docker/oracle
#Oracle REST Data Services
mkdir ~/docker/ords

Move Downloaded Files

1
2
3
4
5
6
7
8
9
10
11
12
13
# ORDS (done in ORDS section)
# APEX
cd ~/docker/apex/
mv ~/Downloads/apex_5.1.3.zip .
unzip apex_5.1.3.zip
mv apex 5.1.3
# Oracle Docker files
mkdir ~/docker/tmp
cd ~/docker/tmp
mv ~/Downloads/docker-images-master.zip .
mv ~/Downloads/linuxx64_12201_database.zip .

Docker

Build Oracle Docker Image

This will be a different setup then I previously blogged about in that this docker container will separate the data from the app. I.e. we can remove the container and rebuild it while keeping all the data in place. This allows for us to

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
cd ~/docker/tmp
unzip docker-images-master.zip
mv linuxx64_12201_database.zip docker-images-master/OracleDatabase/dockerfiles/12.2.0.1
cd docker-images-master/OracleDatabase/dockerfiles
./buildDockerImage.sh -v 12.2.0.1 -e
# Once completed should see a message like:
# Successfully built ad2c10d804a7
# Successfully tagged oracle/database:12.2.0.1-ee
#
# Oracle Database Docker Image for 'ee' version 12.2.0.1 is ready to be extended:
#
# --> oracle/database:12.2.0.1-ee
#
# Build completed in 678 seconds.

Setup Docker Network

In order for the containers to “talk” to each other we need to setup a Docker network and associate all the containers on this network. Containers can reference each other by their respective container names. When referencing another container on the same Docker network the port used is the container’s native port not the mapped port on your laptop.

1
2
3
4
5
6
7
8
9
10
docker network create oracle_network
# Other docker network commands (don't need to run them as part of install)
# Connect and existing container to a docker network
# docker network connect <network name> <container name>
# View a network and connected containers
# In this example "oracle_network" is the network we're interested in
docker network inspect oracle_network

Create Oracle Docker Container

The following command will create and run the Oracle Docker container. It’s TNS listener will be accesible via port 32712 on your laptop. The reference to the APEX installation files are necessary only whe installing APEX.

Adding the -e TZ will set the appropriate timezine for the OS and the database. A full list of timezones can be found here. If excluded it will default to UTC.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
docker run \
--name oracle \
--network=oracle_network \
-e TZ=America/Edmonton \
-p 32712:1521 \
-v ~/docker/oracle:/opt/oracle/oradata \
-v ~/docker/apex/5.1.3:/tmp/apex-install \
oracle/database:12.2.0.1-ee
# Once the Docker image is working you'll see something like:
# #########################
# DATABASE IS READY TO USE!
# #########################
# It will then have a bunch of other information showing which you can ignore.

In another terminal tab, set the sys password to Oradoc_db1 and install APEX

1
docker exec oracle ./setPassword.sh Oradoc_db1

Install APEX

In a terminal tab on your laptop run:

1
2
3
4
5
# Install and configure APEX
docker exec -it oracle bash -c "source /home/oracle/.bashrc; bash"
cd /tmp/apex-install
sqlplus sys/[email protected]/orclpdb1 as sysdba

The above command will open a SQL prompt. Run the following scripts:

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
35
36
37
38
39
40
41
42
43
-- Install APEX
@apexins.sql SYSAUX SYSAUX TEMP /i/
-- APEX REST configuration
@apex_rest_config_core.sql oracle oracle
-- Required for ORDS install
alter user apex_public_user identified by oracle account unlock;
-- From Joel's blog: "Create a network ACE for APEX (this is used when consuming Web services or sending outbound mail):"
declare
l_acl_path varchar2(4000);
l_apex_schema varchar2(100);
begin
for c1 in (select schema
from sys.dba_registry
where comp_id = 'APEX') loop
l_apex_schema := c1.schema;
end loop;
sys.dbms_network_acl_admin.append_host_ace(
host => '*',
ace => xs$ace_type(privilege_list => xs$name_list('connect'),
principal_name => l_apex_schema,
principal_type => xs_acl.ptype_db));
commit;
end;
/
-- Setup APEX Admin password
begin
apex_util.set_security_group_id( 10 );
apex_util.create_user(
p_user_name => 'ADMIN',
p_email_address => '[email protected]',
p_web_password => 'oracle',
p_developer_privs => 'ADMIN' );
apex_util.set_security_group_id( null );
commit;
end;
/
-- Exit SQL
exit

Now exit bash:

1
2
# Exit bash
exit

Create ORDS Container

The following assumes that you’ve downloaded ORDS 3.0.12. Referencing the ORDS version so that can create ORDS images for each ORDS release.

The scripts below will first create the ORDS Docker image then create the containers.

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
# Uses https://github.com/martindsouza/docker-ords Dockerfile
cd ~/docker/ords
git clone [email protected]:martindsouza/docker-ords.git .
mv ~/Downloads/ords.3.0.12.263.15.32.zip ~/docker/ords
# Only need ords.war from install
unzip ~/docker/ords/ords.3.0.12.263.15.32.zip ords.war
# Build the Docker Image
docker build -t ords:3.0.12 .
# Run ORDS
# Note: Leave DB_PORT=1521 as this is NOT the port that maps to your laptop,
# rather the internal docker network that was created. I.e. container to container
docker run -t -i \
--name ords \
--network=oracle_network \
-e DB_HOSTNAME=oracle \
-e DB_PORT=1521 \
-e DB_SERVICENAME=ORCLPDB1 \
-e APEX_PUBLIC_USER_PASS=oracle \
-e APEX_LISTENER_PASS=oracle \
-e APEX_REST_PASS=oracle \
-e ORDS_PASS=oracle \
-e SYS_PASS=Oradoc_db1 \
--volume /Users/giffy/docker/apex/5.1.3/images:/usr/local/tomcat/webapps/i \
-p 32713:8080 \
ords:3.0.12

You should now be able to go to localhost:32713/ords/ on your laptop to run APEX

Useful Commands

Docker Start/Stop Containers

1
2
3
4
5
6
7
8
docker stop -t 200 oracle
docker stop -t 200 ords
# Start containers
docker start oracle
# Wait a minute to start ORDS as Oracle will take a few minutes to boot
docker start ords

Connect to DB from laptop

sqlcl is my alias for SQLcl. More information on how to install SQLcl on MacOS here.

1
2
3
4
5
# CDB (not usually required)
sqlcl sys/[email protected]:32712:orclcdb as sysdba
# PDB
sqlcl sys/[email protected]:32712/orclpdb1 as sysdba
sqlcl martin/[email protected]:32712/orclpdb1

Create New DB User

1
2
3
4
define new_user = 'martin'
create user &new_user. identified by &new_user. container = current;
grant connect, resource, create any context to &new_user;
alter user &new_user quota unlimited on users;

How to Setup Oracle DB 12.2 Docker Container

Update: I’ve moved my setup process to Dockerize Oracle and APEX to Github. It will be maintained with the latest steps I’m using it. The repository is docker-oracle-setup.

I recently had a need to do a Proof of Concept (POC) on a new feature released in Oracle 12c R2 (12.2). My first instinct was to go to my DBAs and ask them to give me access to a 12.2 environment. Thankfully I was at the Oracle ACE annual Oracle Open World dinner and Gerald Venzl (aka Mr.Oracle-Docker) was there and convinced me otherwise. I must thank Gerald for both pushing me to start using Docker again and also for some support when I ran into issues.

The following post will cover how to get a 12.2 instance up and running using the Oracle Container Registry for your own personal instances

Background

  • If you haven’t already done so, go install Docker. If you’ve never heard of Docker before I suggest reading a bit about it so you have some understanding of what is and how it works.
  • Go to container-registry.oracle.com and use your Oracle Technology Network (OTN) login and register.
    • One logged in go to Database > Enterprise and read and if you agree to the Terms and Conditions click the Accept button

Docker

The following steps will pull the appropriate image(s) and setup your docker instance. I’ve added inline comments to describe each step. If you’re new to Docker please read my comments rather than blindly running the code.

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
# Some Docker versions may complain about login credentials when calling the pull command
# If you aren't auto prompted for login credentials run the following and use your OTN credentials
# docker login container-registry.oracle.com
# They're various docker 12c images. To help reduce the number (and size) of images on my laptop I only needed the 12.2 version
# This will take a while to run as the image size is around 3.5 GB
docker pull container-registry.oracle.com/database/enterprise:12.2.0.1
# For all the examples below the name "OracleDB" was used.
# You can use any name you want or just use the docker container ID to reference it
# To run the image the documentation says to just use the -P option for something like:
# docker run -d -it --name OracleDB -P container-registry.oracle.com/database/enterprise:12.2.0.1
# This will auto map a local port on your laptop to the container's Oracle 1521 port.
# The problem with this approach is that each time you stop and start the container you may get a new local port
# To get around this restriction you can use the the "-p" option for a statically defined port.
# In this example I chose 32711
docker run -d -it --name OracleDB -p 32711:1521 container-registry.oracle.com/database/enterprise:12.2.0.1
# It takes some time for the Oracle container to fully boot up. Before trying to connect to it check the container status by running:
docker ps
# Look in the STATUS column for the container. During "boot" time it will say "... (health: starting)".
# Wait until it says (healthy) before trying anything else.
# To review/confirm the port mapping run:
docker port OracleDB
# Should result in something like: 1521/tcp -> 0.0.0.0:32711

Your docker container should now be running. The following code shows how to connect to the instance using SQLcl along with creating a test account on the PDB. You can take the connection strings in the SQLcl demos and apply to SQL Developer.

1
2
3
4
5
6
7
8
9
10
# Note on my laptop I renamed "sql" to "sqlcl". Adjust the scripts accordingly or call sqlplus
# The difference between : and / at the end of the connection strings is :SID /SERVICE_NAME
# Note: Oradoc_db1 is the default password for the image
# To connect to the CDB (you probably won't need to do this)
# sqlcl sys/[email protected]:32711:orclcdb as sysdba
# You'd then need to connect to PDB in SQL: alter session set container = orclpdb1;
# To connect to the PDB to create schema to develop with
sqlcl sys/[email protected]:32711/orclpdb1.localdomain as sysdba

In SQL>:

1
2
3
4
5
6
7
8
-- Create account to develop with
define new_user = 'martin'
create user &new_user. identified by &new_user. container = current;
grant connect, resource, create any context to &new_user;
alter user &new_user quota unlimited on users;
exit

Connect to the PDB

1
sqlcl martin/[email protected]:32711/orclpdb1.localdomain

Note: In SQL Developer this connection looks like:

docker-sql-developer.png

In SQL>

1
2
3
4
-- Create the emp and dept tables (note: only works in SQLcl / SQL Dev. Not SQL*Plus)
@https://raw.githubusercontent.com/OraOpenSource/OXAR/master/oracle/emp_dept.sql
commit;
exit;

You can now do/test whatever you want to do in your PDB!

Common docker commands

So now that you’ve started your Docker instance, and established a working connection to the PDB how do you manage the Docker container? Here are some useful Docker commands:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# See all containers (running or otherwise)
docker ps -a
# Stop the docker image
# docker stop OracleDB
# From Gerald Venzl: By default docker will kill the container after just 10 seconds.
# That usually means that your database will die and has to run through recovery when it restarts.
# I always do a "docker stop -t 100 <container name>". That gives it a minute and 40 secs.
# It won't need that, usually 30 - 40 secs is enough.
# Docker will shutdown as soon as the database is down as well.
docker stop -t 100 OracleDB
# Start the docker image
docker start OracleDB
# *** Cleanup ***
# Delete container
docker rm OracleDB
# Delete Image
docker rmi container-registry.oracle.com/database/enterprise:12.2.0.1

Final Thoughts

I’ve just started to use the Docker 12.2 image and may launch a 12.1 container as well (will blog instructions if I do it).

If I run into any issues doing my tests and “learning” development (i.e. kicking the 12.2 tires) I’ll write another article and link below. I also plan to look into upgrading APEX on the 12.2 container along with creating a simple web server container to test APEX with some 12.2 features. If I get this working I’ll blog about it.

Update: Role Hartman wrote a followup post to this article that I highly suggest reading. I will update this article with some of his suggestions on how he sets up his Docker Oracle DB container.

Montreal Oracle Dev Day 2017

For those in Montreal and the surrounding area I encourage you to come out to the Montreal Oracle Dev Day on October 25th. The event is just $15 which is a steal considering that 3 Oracle ACE Directors will be presenting. Note: The cost was to help reduce no-shows that occur when having entirely free events.

Here’s a summary agenda of the presentations with the full agenda here:

Aside from the presentations attendees will have plenty of opportunity to network and share your Oracle development experiences. The speakers will be available all day so feel free to bring your APEX questions!

You can register now online.

Oracle Open World 17

I’ll be attending Oracle Open World 17 (OOW 17) next week. This year is going to be special for me as I’ll be giving a presentation at Oak Table World for the first time. I’m a bit nervous as I’ll be presenting alongside some of the biggest names in the Oracle DB community. Specifically I’m talking between lightening talks from Oak Table members and Cary Millsap!

My Presentations

Here are the presentations I’ll be giving at OOW 17:

Explore Oracle Application Express APIs

Date: Sunday, Oct 01, 3:45 p.m. - 4:30 p.m.
Location: Marriott Marquis (Yerba Buena Level) - Salon 4-6
Session Code: SUN6244

Abstract: Most developers new to Oracle Application Express aren’t aware that it comes with a plethora of very powerful APIs for both PL/SQL and JavaScript. This session highlights some of these APIs and explains how and when to use them to speed up and simplify developing your Oracle Application Express applications. New Oracle Application Express 5.1 APIs are also discussed.

Note: This presentation was voted the best presentation at ODTUG Kscope 17

Open Source PL/SQL Utility Tools for Oracle

Date: Tuesday, Oct 03, 12:30 p.m. - 1:30 p.m.
Location: Children’s Creativity Museum (221 4th St - corner of 4th ST and Howard).

Abstract: OOS Utils is a free open-source PL/SQL tool set for common utility functionality that developers use. Its goal is to prevent developers from having to rewrite the same code over and over again.

This presentation will highlight some of the great features of OOS Utils and showcase some future plans for the project. All audience types (DBAs, PL/SQL and APEX developers, and development managers) are encouraged to attend, as all will see immediate benefits for themselves or their teams to use.

Other Notes:

  • As usual we’ll be having our annual ODTUG APEX Meetup on Tuesday (Oct 3rd) night at Johnny Foley’s. There’s a slight twist to the event. Since this has been a huge hit we’ve opened it up and made it an ODTUG All Community event! This means that the entire ODTUG community at OOW will be in one place and no need to run around to all the different events. You can register (free) for the event on meetup.com. If you’ve never been to this event before here are few tips:
    • We’re downstairs (Johnny Foley’s has two levels) where all the action is at for the dueling pianos.
    • We don’t have any special reservations.
    • The earlier you show up the better as there’s always a lineup to get in as the night goes on.
  • If this is your first time going to OOW then I highly recommend reading the OpenWorld Survival Guide that Monty Latiolais recently wrote.

Looking forward to seeing everyone there! If you want to get in touch you can send me a DM on Twitter: @martindsouza