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.
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.
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.
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:
selecttrim(to_char(300, 'XXXXX')) dec2hex
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.
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.
The following function will convert dec to hex in PL/SQL:
As you can see PL/SQL uses the quotient from the previous loop to calculate the current loop’s remainder.
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.
var x number;
var base number;
:x := 300;
:base := 16;
-- Find how many loops we'll need to convert dec to basex
lvls as (
-- The <= logic will return the number of characters required for conversion
-- listagg(a.letter, '') within group (order by md.lvl desc) basex
--from my_data md, alphabet a
-- and md.remainder = a.num
LVL QUOTIENT REMAINDER LETTER
1 18 12 C
2 1 2 2
3 0 1 1
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.
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.
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.
Create an Interactive Report: select ename, sal from emp
On the IR go to Actions > Format > Control Break and Select Job as the column (shown below)
On the IR go to Actions > Data > Aggregate and select Function: Sum and Column: Sal as shown below.
The resulting report should look like the first screenshot at the begging of this article.
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.
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:
PROMPT CREATING TABLE 'MY_TABLE';
CREATETABLE 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.
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.
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.
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:
Workspace Internal for APEX admin
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
The following script will create a folder structure that looks like:
To host APEX installation and images for each version
APEX 5.1.3 installation files
Hold Oracle 12.2 data files
ORDS Dockerfile (to build ORDS image)
# The APEX folder structure allows for multiple versions of APEX to be hosted by different ORDS instances
# To store oracle data
#Oracle REST Data Services
Move Downloaded Files
# ORDS (done in ORDS section)
mv ~/Downloads/apex_5.1.3.zip .
mv apex 5.1.3
# Oracle Docker files
mv ~/Downloads/docker-images-master.zip .
mv ~/Downloads/linuxx64_12201_database.zip .
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
./buildDockerImage.sh -v 18.104.22.168 -e
# Once completed should see a message like:
# Successfully built ad2c10d804a7
# Successfully tagged oracle/database:22.214.171.124-ee
# Oracle Database Docker Image for 'ee' version 126.96.36.199 is ready to be extended:
# --> oracle/database:188.8.131.52-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.
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
# 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.
docker run \
--name oracle \
-e TZ=America/Edmonton \
-p 32712:1521 \
-v ~/docker/oracle:/opt/oracle/oradata \
-v ~/docker/apex/5.1.3:/tmp/apex-install \
# 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
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
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.
One logged in go to Database > Enterprise and read and if you agree to the Terms and Conditions click the Accept button
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.
# 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
# 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:184.108.40.206
# 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:220.127.116.11
# It takes some time for the Oracle container to fully boot up. Before trying to connect to it check the container status by running:
# 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.
# 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)
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.
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!
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!
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: 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.
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.