Docker Oracle and APEX

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
docker run \
--name oracle \
--network=oracle_network \
-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/Oradoc_db1@localhost/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 => 'martin@talkapex.com',
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 git@github.com: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/Oradoc_db1@localhost:32712:orclcdb as sysdba
# PDB
sqlcl sys/Oradoc_db1@localhost:32712/orclpdb1 as sysdba
sqlcl martin/martin@localhost: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: If you want to setup Oracle in an Docker container where the data is retained on your laptop (and not the container) read this article.

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/Oradoc_db1@localhost: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/Oradoc_db1@localhost: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/martin@localhost: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

Oracle SQL Developer Randomly Closes

I recently had a situation that running either Oracle SQL Developer or SQL Developer Data Modeler on a Windows 7 VM (more on this later) would randomly close (or crash depending on how you look at it). The log files didn’t produce anything substantial and after a lot of digging around it was due to the JVM and the video driver. Jeff Smith confirmed this over Twitter:

I was using windows on a VM and it had no available video driver updates. After reading this post on StackOverflow I found there is a configuration setting to get around the issue. It wasn’t very clear as to where to make the configuration change so here it is:

  1. Modify datamodeler\ide\bin\jdk.conf
  2. Add AddVMOption -Dsun.awt.nopixfmt=true on a new line after AddVMOption -Dsun.java2d.noddraw=true
  3. Restart the application

A few additional notes:

  1. The versions I downloaded included the JDK. If you’re using your own JDK then the location of jdk.conf will probably be different.
  2. If you’re using the included JDK version then you’ll need to remember to do this step with each update of the applications.

Kscope 17

I’ll be attending and presenting at this year’s ODTUG Kscope 17. Here are some things that I’m involved with and my personal highlights for the conference this year:

Conference Planning APEX App

We (Insum) created a conference planning application built in APEX: bit.ly/insum-kscope17. The goal was to showcase APEX and some cool features we added into the application. If time permits I may give a brief demo into how we created the app during Monday’s APEX Open Mic Night. I made a short video to highlight this application:

Note: This is an unofficial application. Official Kscope 17 mobile apps for Apple iOS and Google Android.

Presentations

I’ll be giving / participating in the following presentations:

Explore the APEX APIs

When: Jun 26, 2017, Monday Session 5 , 4:45 pm - 5:45 pm
Room: Cibolo Canyon 5

Most developers new to APEX aren’t aware that APEX comes with a plethora of very powerful APIs for both PL/SQL and JavaScript. This presentation will highlight some of these APIs and explain how and when to use them to speed up and simplify developing your APEX applications. New 5.1 APIs will also be discussed.

Open Source PL/SQL Utility Tools for Oracle

Note: this is in the Database track rather than the APEX track. More about this below.

When: Jun 28, 2017, Wednesday Session 14 , 1:45 pm - 2:45 pm
Room: Verbena/Periwinkle

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.

#LetsWreckThisTogether APEX Talks

This is a 2 hour session with many of the APEX gurus giving 10 minute lightning talks. My 10 min talk is called “Optimizing Laziness” and will be about how to develop more efficiently.

When: Jun 29, 2017, Deep-Dive Session, 9:00 am - 11:00 am
Room: Grand Oaks G

Just in case you have not received enough inspiring content by the end of the week, the #LetsWreckThisTogether APEX Talks is back. At last year’s Kscope, this exciting new format was a success.

Instead of long, in-depth “deep dives” on a single topic. The APEX track of the conference will close with Lightning Talks presented by some of the best and well-known speakers and personalities in the APEX world.

The exact lineup will be announced closer to the event.

Insum Vendor Presentation

When: Jun 27, 2017, Tuesday Vendor Presentation, 9:45 am - 10:45 am
Room: Cibolo Canyon 5
Topic: Vendor Presentation - Subtopic: Vendor Presentation

We’re doing something very different this year for our vendor presentation. Instead of a pre-canned talk we’re letting you decide what we talk about. Think Choose Your Own Adventure but instead of stories we’ve got some great mini APEX presentations to chose from. You can vote right now for what you’d like to see in this presentation: bit.ly/insum-vote-kscope17! If you aren’t going to be at Kscope 17 we still encourage you to vote as we may do webinars on some of the top topics. We also have a full blog post about this.

Other Kscope News

Monday Night Events

Monday night is all about communities at Kscope. APEX will have its annual Open Mic Night which is one of my favorite events at Kscope. If you have an APEX application you’d like to show off this is the place to do it!

The Database Community is changing things up and doing something similar to APEX with its own twist. It will be a combination of lightning talks and a variety show. I can’t speak for the later but if you have some cool PL/SQL, SQL, etc that you’d like to show off in 5 minutes or less this is the place to do it! I may have a few demos up my sleeve that I may show if there’s any room on the signup list.

More information about all the Monday night events can be found here.

APEX Speakers in the Database Track

This year we asked some of our top APEX speakers to give presentations in the Database track. The reason behind it is that APEX developers do a lot of PL/SQL and SQL and we’re hoping that these presenters will encourage some of the APEX attendees to check out the Database presentations. Here are the APEX speakers giving talks in the Database track:

  • Open-Source PL/SQL Utility Tools for Oracle - Martin D’Souza
  • Six Reasons Why You Will Get Addicted to SQLcl Scripting - Sabine Heimsath
  • Oracle 12c for Developers - Alex Nuijten

Twitter

Keep up to date on all the Kscope news by following the #kscope17 on Twitter.

Wrapping Up

For those going to Kscope 17 see you in a few weeks and be sure to say hi!

- Martin

Automatically Update an Application's Version Number in APEX

Last week Connor McDonald tweeted that he received the following question on AskTom: “I hacked the Apex install scripts to get access to the APEX… schema..and now Apex doesnt work“ The community had a few reactions on twitter including my comment which stated that “They’re some cases where it’s good. I use it to “inject” the version as part of the build process.

I had mis-read Connor’s statement and interpreted “hacked the APEX install scripts“ as “hacked an application install script“. To correct my statement, you should NEVER modify the main APEX installation script. That said, I usually modify an application’s install script despite the fact that I shouldn’t. I have a good reason though which is described below.

Each APEX application has a meta data property called Version and is usually displayed in the bottom of most applications by referencing the substitution string APP_VERSION in the page’s template as shown below.

The application’s version is statically defined in the application’s properties which can be set in Shared Components > Application Definition:

Since the properly must be statically defined it requires a manual step to update the version number each time the application is released. This usually requires someone to update this value before exporting the application for each release. Personally I’m not a fan of this as I try to automate as much as possible for each build and it can easily lead to mistakes.

To get around this I use a mnemonic string as the release version and replace it using a script as part of my build process. Here’s an example.

Change the version number to %RELEASE_VERSION%:

As part of your build script you should be exporting the APEX application from command line. SQLcl has a built in command to easily do this that Kris Rice blogged about here.

The export file will now look like:

You can now use a command like sed in Linux or Node.js to find and replace %RELEASE_VERSION% with your build release version. An example of a build script that both exports the application and sets the version number:

build.sh:

1
2
3
4
5
VERSION=$1
echo exit | sqlcl giffy/giffy@localhost:1521/xe @apex_export.sql 123
sed -i "" "s/%RELEASE_VERSION%/$VERSION/" f123.sql

apex_export.sql:

1
2
3
4
5
6
7
set termout off
define APP_ID = &1
spool f&APP_ID..sql
apex export &APP_ID.
spool off

Running:

1
./build.sh 1.0.0

Query CSV data using APEX 5.1 APIs

When I first started using Oracle many years ago one of the most frustrating things was the amount of code and complexity required to parse/query CSV data (or any delimited data). As time passed I was able to leverage new features and techniques to help but they all had their issues. APEX 5.1 introduced a new API that may help simplify a lot of the headaches. The following example highlights this.

Suppose I had the following CSV data:

1
2
3
4
First Name, Last Name, Dept
Martin, DSouza, IT
John, Doe, Sales
Sally, Smith, Sales

The first thing to do is break each of the rows of text into rows of a query. This can be done using the new APEX 5.1 apex_string.split API

1
2
3
4
5
6
7
8
9
10
11
select *
from table(apex_string.split(:data,chr(10)))
;
-- Results in
COLUMN_VALUE
------------------------------
First Name, Last Name, Dept
Martin, DSouza, IT
john, doe, Sales
Sally, Smith, Sales

Where :data is the CSV data above. Note I’m using a Mac and the EOL character is a LF (chr(10)). Windows users use CR + LF (chr(13) || chr(10)). More info on this here

Now that each line of data is on it’s own row we need to create columns. This can be done using regular expressions:

Update: originally I used regexp_substr(column_value, '[^,]+', 1, 1) as the regexp which caused issues with null values (ex abc,,def). I’ve since updated the code samples to handle nulls.

1
2
3
4
5
6
7
8
9
10
11
12
13
select
rtrim(regexp_substr(column_value, '([^,])*(,)?', 1, 1), ',') fname,
rtrim(regexp_substr(column_value, '([^,])*(,)?', 1, 2), ',') lname,
rtrim(regexp_substr(column_value, '([^,])*(,)?', 1, 3), ',') dept
from table(apex_string.split(:data,chr(10)))
;
-- Results in
FNAME LNAME DEPT
First Name Last Name Dept
Martin DSouza IT
john doe Sales
Sally Smith Sales

To remove the header row change the query to:

1
2
3
4
5
6
7
8
9
10
11
select fname, lname, dept
from
(
select
rownum rn,
rtrim(regexp_substr(column_value, '([^,])*(,)?', 1, 1), ',') fname,
rtrim(regexp_substr(column_value, '([^,])*(,)?', 1, 2), ',') lname,
rtrim(regexp_substr(column_value, '([^,])*(,)?', 1, 3), ',') dept
from table(apex_string.split(:data,chr(10)))
)
where rn > 1

APEX 5.1 Validations and Session State

Starting in APEX 5.1, pages are submitted via AJAX by default instead of the traditional post and refresh. I recorded a short video for Insum a while back on this and suggest that you watch it before continuning.

Last week Vincent Morneau and I had a discussion that lead to the following question: What happens when an After Submit computation or process changes a page item’s value in session state and a validation fails? The short answer is that the value in session state is modified but not reflected on the page afterwards. I created the following test case to demonstrate this behavior:

On Page 2 (P2) I have an text item called P2_NAME. I then have an After Submit computation that always sets the value to Set from Computation and a validation that always fails as shown below.

If I enter martin as a value in P2_NAME and submit the page I’ll get an error message, yet the session state value is now Set from Computation. The following animation shows this.

What does this mean?

Before analyzing this behavior it’s important to understand when commits occur in APEX. After each computation or process an implicit commit may occur. Dan McGhan wrote an excellent blog post about this several years ago that I highly suggest reading it.

I can’t speak for the APEX development team but it appears they had a few options when introducing the new AJAX submission functionality:

  1. Change the behavior of when commits occur to not commit until afer all validations pass
  2. Upon a failed validation modify the page with the updated session state values
  3. Do neither

The first two options may actually introduce more problems and complexity and also ruin backwards compatibility with older applications. I think the third option is the best one as most applications don’t do computations or process after submitting but before validations. If developers pass in current page item values into AJAX Dyanmic Actions or cascading LOVs it will make the new AJAX page submission a non issue.

Why might this be a problem?

Modifying the session state and not reflecting the change on the page may cause some undesired UI bugs when dealing with AJAX functions (Dynamic Actions, cascading LOVs, etc) that reference page items. I dont’ think there are many situations where this will actually cause issues and most of this can be mitigated by passing in the reference page items when calling an AJAX function.

What to do about it?

This is one thing that I think develoeprs will just need to be aware of and analyze their code to see if there may be any impacts. The following query will identify pages where computations or processes exist before validations.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select distinct
pv.application_id,
pv.page_id,
pv.page_name
from apex_application_page_val pv
where 1=1
and pv.application_id = :app_id
and exists (
select 1
from apex_application_page_proc pp
where 1=1
and pp.application_id = pv.application_id
and pp.page_id = pv.page_id
and pp.process_point_code = 'ON_SUBMIT_BEFORE_COMPUTATION'
union
select 1
from apex_application_page_comp pc
where 1=1
and pc.application_id = pv.application_id
and pc.page_id = pv.page_id
and pc.computation_point = 'After Submit'
)

Partial Rollbacks

The ability to rollback transactionsin PL/SQL is very helpful when something goes wrong and you want to undo what was just done. At a very high level, most code that use rollback look like the following:

1
2
3
4
5
6
7
begin
...
exception
when others then
rollback;
raise;
end;

The above code makes sense as the rollback occurs when an error happens. (Note for APEX users, an implicit rollback occurs in processes if an exception occurs) The caveat is that it rollsback the entire transaction (i.e. from the start). What if the code is part of a larger block of code and you only want to rollback to the previous step? The following pseudo code is an example:

1
2
3
4
5
6
7
begin
do_step_1;
do_step_2;
-- Step 3 may error out and if it does, still want to preserve the work of steps 1 and 2.
do_step_3;
do_step_4;
end;

In do_step_3 if a generic rollback was used in the exception block it would undo any chnages that were done in step’s 1 and 2 which is not the desired outcome. Thankfully PL/SQL rollback functionality supports this by using savepoint. If we want do_step_3 to work as intented this is what it should look like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create or replace procedure do_step_3 as
begin
savepoint start_step_3;
...
exception
when some_expected_error then
-- This will rollback all changes to the start of do_step_3
rollback to savepoint start_step_3;
-- no raise as this was an expected error
when others then
raise;
end do_step_3;

A few things to note about savepoints:

  • The rollback to savepoint x doesn’t need to be called in the exception block. I’ve used it in other places as well. Ex: if <> then rollback to savepoint...
  • Try not to litter your code with savepoints. It can get very confusing and tough to debug if you have a lot of them. The business logic will really determine when you need to use them.
  • In the past ten years I’ve only needed to use them a handful of times. It’s not a common feature that you’ll need to use but good to know when you need it.

Oracle documentation for savepoint can be found here.