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.

HEUG Alliance 2017

I’ll be at HEUG Alliance 2017 in Las Vegas next week. I’ll be giving a presentation on APEX with fellow Insum colleagues Christian Larocque and Sylvain Martel called From student systems to admin systems, one tool : Oracle APEX. The goal of this presentation is to highlight the advantages of using Oracle APEX for higher education institutions.

This will be my first time attending Alliance and I found it difficult to plan my schedule using the existing site. I wasn’t able to easily search all the presentation information at once and on a per time slot basis. APEX to the rescue! We created a simple APEX scheduling application which allows attendees to easily see all the different session slots and search on all the presentations for each slot. For those that have never seen or used APEX before this application shows how easy and quick it is to get a very useful application up and running in a short period of time.

For those attending Alliance 2017 and want to learn more about APEX we’re offering free “Ask the Expert” sessions. To book a time with one of our experts register here.

Custom Calendar CSS in APEX

The new (not legacy calendar) in APEX 5 comes with a set of default calendar CSS classes to style calendar events. The following screenshot is from the inline help for the calendar CSS Class attribute:

calendar-help.png

I couldn’t find how to create a custom class in the documents (as the help suggests). Using the pre-defined classes as an example I was able to create a custom class for a new color for my calendar. The following is an example of a custom APEX calendar class:

1
2
3
4
5
.fc .fc-event.apex-cal-demo {
background-color: #F0F8FF;
border-color: #000000;
color: #FFFFFF;
}

In the calendar query this class can now be referenced as apex-cal-demo.

How to Run Remote SQL Scripts in SQLcl

Over the past few years of developing open source PL/SQL tools I’ve learned that one of the key things to make a project successful is have a frictionless install process. Node.js does this very well with their Node Package Manager (npm). Currently there is no largely adopted solution like npm for Oracle tools so one must usually provide a downloadable zip file containing the code along with installation instructions.

Recently I found out that SQLcl (and I think SQL*Plus 12.1) support running remote SQL scripts. The following is an example of running a remote script from SQLcl:

1
2
3
SQL> @https://gist.githubusercontent.com/martindsouza/91742f28c39ed2d41d35d80b6c4cc4c1/raw/d0f28ab7e8af5f64629ad480bad570764cece543/test.sql
hello from github
SQL>

The above script is a very simple one: prompt hello from github which can be adapted to contain a full install process.

The nice thing about running remote SQL scripts is that no ACL settings are required on the server nor SSL certificates since the client (i.e. your machine) is making the web request. The bad thing about this is that it opens the door for a lot of vulnerability potentials. It’s recommended to always review the script before running it.

Using Pivot for Aggregations

Suppose you had a requirement in which you needed to return one row which had two columns. The two columns would contain the number of employees in the two different departments. This sounds like a trivial problem to solve but isn’t as easy when you get to coding it.

One way to do this is to use case statements in the aggregation function such as :

1
2
3
4
5
6
7
8
9
10
select
count(case when d.dname = 'ACCOUNTING' then 1 else null end) acct_dept_cnt,
count(case when d.dname = 'RESEARCH' then 1 else null end) rsch_dept_cnt
from emp e, dept d
where 1=1
and e.deptno = d.deptno
;
ACCT_DEPT_CNT RSCH_DEPT_CNT
3 5

Another neat way to do this is to use the pivot function:

1
2
3
4
5
6
7
8
9
10
select
acct_dept_cnt,
rsch_dept_cnt
from (
select e.deptno, d.dname
from emp e, dept d
where 1=1
and e.deptno = d.deptno)
pivot (count(deptno) as dept_cnt for (dname) in ('ACCOUNTING' as acct, 'RESEARCH' as rsch))
;

In this example they both have the same explain plan as shown below. If using on larger / more complex data sets it would be a good idea to compare the explain plans for both queries to see if there’s performance gain between the two.

explain-plan.png

How to Reference Package Variables in SQL

A long time ago (pre-12c) I wrote about How To Reference Package Variables Outside of PL/SQL. This technique used an execute immediate function to reference a given package variable. Another alternative at the time was to create individual get functions for each variable.

Starting in Oracle 12c you can directly use PL/SQL in SQL and thus reference package variables in SQL. The following example shows how:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create or replace package pkg_demo as
gc_first_name constant varchar2(255) := 'Martin';
end pkg_demo;
/
with
function get_name return varchar2 as
begin
return pkg_demo.gc_first_name;
end;
select get_name() my_name
from dual;
/
-- Will return
MY_NAME
Martin

You can also use this concept in views.

How to Reference JavsScript and CSS Files for Entire Application

Kim Mertens recently posted the following question today on Twitter: Where to put JS-file that has to be loaded on every page in #orclapex 5.0? Tried “Global Page - Static region” but jQuery isn’t loaded yet

The answer isn’t as straight forward as you think. Most people are used to referencing web files (JavaScript and CSS files) on Page 0 from legacy APEX 4. As Kim hinted at in his question this isn’t always the best thing to do.

In APEX 5 there’s a specific area dedicated to reference custom web files. It’s not the easiest thing to find at first (it takes me a few tries to find it sometimes if I haven’t used it in a while).

js-storage.gif