Thursday, February 16, 2017

Comma separated search and search with checkboxes in Oracle APEX

When you have a classic report in Oracle Application Express (APEX) and want to make it searchable you typically add a Text Item in the region, enable Submit on Enter and add a WHERE clause to your SQL statement.

Here’s an example:

Classic Report with Search (text item)

Your SQL statement probably looks like this:


When you want to search for multiple customers separated by a comma, how do you do that?
So in my search field I add for example: 1,2,3 and expect to see 3 customers.

There’re a couple of options you have, I’ll list three below:

  1. INSTR

    where INSTR(','||:P4_SEARCH||',', ',' || CUSTOMER_ID || ',') > 0

    where REGEXP_LIKE(CUSTOMER_ID, '^('|| REPLACE(:P4_SEARCH,',','|') ||')$')

    where customer_id in to_number((
      select regexp_substr(:P4_SEARCH,'[^,]+', 1, level) 
        from dual
     connect by regexp_substr(:P4_SEARCH, '[^,]+', 1, level) is not null 

Which one to choose? It depends what you need… if you need readability, maybe you find INSTR easier to understand. If you need performance, maybe the last option is the better choice… so as always it depends. If you want to measure the performance you can look at the Explain Plan (just copy the SQL in SQL Workshop and hit the Explain tab).

The Explain Plan for the first SQL looks like this:

Explain Plan INSTR

The Explain Plan for the last SQL looks like this:


The above technique is also useful when you use want checkboxes above your report, so people can make a selection. For example we select the customers we want to see:

Classic Report with checkbox selection

The where clause would be identical, but instead of a comma (,) you would use a colon (:), so the first statement would be:

where INSTR(':'||:P4_SEARCH||':', ':' || CUSTOMER_ID || ':') > 0

Happy searching your Classic Report :)

Monday, February 13, 2017

Where do you specify the Date Format Mask

When reviewing Oracle APEX applications I often see hardcoded date or timestamp formats.
You can define your date formats in multiple places in your application. In your item or column attributes, as part of your code e.g.TO_CHAR(sysdate, ‘DD-MON-YYYY HH24:MI’) or if you want to make it more reusable you might create a substitution string. That will all work, but you can make your life easier and for the ones looking or needing to maintain your code…

APEX itself provides in the Globalization attributes (Shared Components) a place where you can define your default date and format masks for your entire applications. I consider this a best practice to use those fields, as it’s defined in one logical place, so when you need to change your format, you do it once and you’re done. In your custom SQL and PL/SQL code you can also reference those format masks by predefined substitution strings:



Here’s a screenshot which shows which substitution string corresponds with which field:

Application Attributes - Globalization

You can define the format mask you want, or you can click the arrow to see most used format masks represented with an example. To make it a bit easier, I put the format mask (in red) next to it, so you see the underlying format mask more easily:

Possible date format masks defined in the pop-up

If you need to make the format mask dynamic, for example using different format masks for different language, APEX doesn’t allow you to translate that substitution string through Text Messages, but you can work around it by using your own substitution string and have that dynamically filled. In the Globalization Attributes you would add instead of a hardcoded format mask your own substitution string e.g. &MY_TRANSLATED_DATE.FORMAT.

Tuesday, February 07, 2017

Changing the label of an item in Oracle APEX dynamically

Today I got the question how to change the label of an item in Oracle Application Express (APEX) based on some condition. I actually had this requirement myself a couple of times, so maybe other people too.

Here’s an example; whenever we change the Source item, we want the Affected Item to change it’s label:

The use case: after change of the source item, the label of the affected item changes

The first thing that comes to mind (if you already know a little bit of APEX); lets use a Dynamic Action: on change of the Source item we will fire (in this example we will only fire when the value is A):

Dynamic Action in APEX

Now which action should we use when the dynamic action fires?

Default possibility of actions

Set Value will typically set the value of an Item, but what about the Label?
If I don’t find the option, I typically look for a plugin or write some code myself. In this case I wrote a bit of JavaScript, for example:

var newLabel = 'My new label for ' + $v('P2_SOURCE_ITEM');

This will set the label to "My new label for " and then the value of the item, at least if you select in the Affected Elements the item that needs the label change.

Whenever I think about writing custom code, my mind says “you should create a plugin for that”.
So I actually started to write an Oracle APEX Plug-in called “Set Label” (

While I was trying the plugin and writing up the things I needed to do, I guess something happend in my mind. I missed the obvious, it suddenly came to my mind there’s a much simpler solution to this…

You can actually use the Set Value action… just add after your item _LABEL, that’s it.

Use the Set Value dynamic action but add _LABEL to change the label of the item

Here’s the result:


Sometimes developing is much more simple than initially thought, you just have to see it :)

Update 16-FEB: if you want to make your label dynamic, in the Set Values Dynamic Action, you can specify SQL / PL/SQL:

PL/SQL Expression

or Javascript :


Tuesday, January 10, 2017

My first blog post with

I’m trying to write this blog post with

Just like Martin I’m also searching for alternative ways to write blog posts. I don’t want to completely migrate my blog to a new platform, so I’m searching for a way to write in Markdown and deploy to Blogger.

Why Markdown?
Since our development of APEX Office Print (AOP) we also use Markdown for our documentation as it makes including code samples easier, it can be version controlled and overall it’s pleasant to write in. Whenever we deploy a new version, we publish the markdown as HTML. For example you see the result of our documentation here. SSjj… we like Markdown so much that we are even looking into supporting Markdown to write your template in AOP, next to Word, Excel and Powerpoint, but more on that in the February timeframe. :)

So I’m giving a try, it’s just another Markdown editor, but it can publish directly to Blogger. It allows me to include code samples like this:

  l varchar2(100);
  l := 'hello world';

Or if I want to reference somebody I can use a quote:

Oracle Application Express (APEX) changed my life. – Dimitri Gielis

And a list … for example the top 3 reasons I’m looking at a different way to blog:

  1. Faster to write a post
  2. Reuse my writings in different ways
  3. Easier to share code

So this post is really to try the different options of which should show up in Blogger after I hit the publish button.

Here’s an image which I plan to use in my next post:
enter image description here

If you see this and the post looks ok, my test went well :)

Saturday, December 17, 2016

Start to develop in APEX 5.1, you will gain at least an hour a day!

Yesterday APEX 5.1 ( was installed on
This means that you can start developing your apps in APEX 5.1 from now on. Unlike the early adopter releases ( you can develop your apps on and later export them and import in your own environment once the on-premise version of APEX 5.1 is available.

APEX 5.1 is again a major update behind the scenes. The page processing is completely different from before; where previously full page reloads were done, now there's much more lightweight traffic and only necessary data is send across.

The big features in this new release are the introduction of Interactive Grids, which is both a successor for Interactive Reports as for Tabular Forms. The other big feature is the integration of Oracle JET, which you see mostly in the data visualisation (charts) part of APEX, but more components will probably follow in future versions. Although those two features addresses the most common issues we previously had (outdated tabular forms and charts), APEX 5.1 brings much more than that. Equally important for me are the "smaller" improvements which makes us even more productive. Below you find some examples...

When creating a new application, the login page is immediately a great looking page:

Previously in APEX 5.0 you had to adapt the login page, see my blog post Pimping the Login Page.

When you want your item to look like this:

APEX 5.1 has now a template option to display the Pre and Post text as a Block:

Or when you want an icon inside your item, there's an Icon CSS Class option selector which shows the gorgeous looking new handcrafted Font APEX icons:

You could do all the item customisations above in APEX 4.2 or 5.0 too, but it would require custom css and some code, whereas now it's declarative in APEX 5.1.

And there's so much more; ability to switch style by user, new packaged apps, warn on unsaved changes, no reload page on submit etc. features that haven't been talked about much yet, but which before you had to do with a plugin or a lot of custom code and now it's just there.

So those "smaller" features are actually not so small, they are an enormous timesaver and bring your apps in warp-speed to modern and great looking applications.

In the next blog posts I'll go in more detail on some specific features that will gain you at least an hour a day, but in the meantime, embrace APEX 5.1 and start earning those extra hours :)

Update: on December 21th APEX 5.1 was made available to download on OTN.

Tuesday, October 11, 2016

OTN Appreciation Day : APEX

If you're following some Oracle blogs or Twitter, you'll see many blog posts starting with "OTN Appreciation Day : " today. You can read the story behind this initiative on Tim Hall's blog. "The blog post content should be short, focusing more on why you like the feature, rather than technical content."

In my life Oracle played (and is still playing) an important role... and it all started because I love working with data - which lead me to the Oracle database, the *best* database in the world.

So I just have to write about a feature of the Oracle Database; but which one to pick? The way Oracle implemented SQL, or the programming language inside the database PL/SQL or the tools and options that make the database awesome?... I thought some time about it and for me personally next to the database itself, it was really APEX that changed my life, so I just have to write about it.

In this post I want to share why I love Oracle Application Express (APEX) and why I consider this the best feature of the Oracle Database *ever*.

The goal, I believe, of a database is to capture data and do something with it; either to get insight in your data or share it again in different formats with others... and Oracle Application Express is just the easiest way to do this! In no time you create a web application with some forms that capture data directly in your database. And in even less time you share and get insight in your data through beautiful reports and charts. You just need a browser... it's secure, fast, scalable and you can use the full power and features of the database - APEX is the window to your data!


Thursday, August 04, 2016

Installing SQLcl on OEL/RHEL

In my previous post I talked about how SQLcl came in handy to work with JavaScript against the database.

The installation of SQLcl is easy... you just download the zip, unpack and run the executable.

But to be fair, before I got SQLcl running (especially the script part) I encountered a number of issues, so hopefully this post helps you be able to run SQLcl with all features in minutes as it's meant to be :)

Those were the error messages I received when running sql (script):

javax.script.ScriptException: Java class "java.util.ArrayList" has no public instance field or method named "0".

javax.script.ScriptException: ReferenceError: "Java" is not defined. (#1) in at line number 1

The solution for me was to upgrade my Java version to Java 8.

Here're the steps on my OEL/RHEL system to upgrade Java:

$ cd /opt

$ wget --no-cookies --no-check-certificate --header "Cookie:; oraclelicense=accept-securebackup-cookie" ""

$ tar xzf jdk-8u102-linux-x64.tar.gz 

cd jdk1.8.0_102/

alternatives --install /usr/bin/java java /opt/jdk1.8.0_102/bin/java 2
$ alternatives --config java

There are 5 programs which provide 'java'.

  Selection    Command
   1           /usr/lib/jvm/jre-1.7.0-openjdk.x86_64/bin/java
   2           /usr/lib/jvm/jre-1.6.0-openjdk.x86_64/bin/java
   3           /usr/lib/jvm/jre-1.5.0-gcj/bin/java
*+ 4           /usr/java/jre1.8.0_101/bin/java
   5           /opt/jdk1.8.0_102/bin/java

Enter to keep the current selection[+], or type selection number: 5

$ alternatives --install /usr/bin/jar jar /opt/jdk1.8.0_102/bin/jar 2
$ alternatives --install /usr/bin/javac javac /opt/jdk1.8.0_102/bin/javac 2
$ alternatives --set jar /opt/jdk1.8.0_102/bin/jar
$ alternatives --set javac /opt/jdk1.8.0_102/bin/javac
$ java -version
java version "1.8.0_102"
Java(TM) SE Runtime Environment (build 1.8.0_102-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.102-b14, mixed mode)

$ export JAVA_HOME=/opt/jdk1.8.0_102
export JRE_HOME=/opt/jdk1.8.0_102/jre
$ export PATH=$PATH:/opt/jdk1.8.0_102/bin:/opt/jdk1.8.0_102/jre/bin

Now when running SQLcl everything worked like a charm. Hurray :)

SQLcl to the rescue when the Database and APEX fail (with JSON limitations)

In the last two years I've been using JSON in almost every project I was involved in.
For example with APEX Office Print our plugin is sending JSON to the backend. This JSON you can actually see yourself in the Remote Debug screen in your AOP Dashboard.
Another example is the wearables project (IoT) I showed at KScope 16; the wearable is sending data to a smartphone or tablet, which in his turn is doing a call to our backend (in ORDS) and sending JSON across.

At the end of the day we want the data in the Oracle Database, so our APEX apps can work with that data.

Since Oracle DB 12c, JSON is supported straight from the database. I wrote a number of blog posts how to read JSON from SQL within the database. Here's a quick demo of JSON in the database:

SQL> create table tbl_with_json (
  2    json_clob  clob, 
  3    constraint json_clob_chk check (json_clob is json)
  4  );

Table TBL_WITH_JSON created.

SQL> insert into tbl_with_json (json_clob) values ('{
  2      "items": [{
  3          "client_id": -1,
  4          "registration_date": "2016-07-29T07:46:09.941Z",
  5          "question": "My Question",
  6          "description": "My huge clob"
  7      }]
  8  }');

1 row inserted.

SQL> select a.json_clob.items.question as question, a.json_clob.items.description as description 
  2    from tbl_with_json a;

My Question                                                                                                                                                           
My huge clob                                                                                                                                                          

Now the reason of this blog posts: what if your JSON contains some very big text (>32K) in a single node e.g. in the description field? 

If you want to follow along in your own test case, open the description record in SQL Developer for example and past a large text (>32K) in the description node (so replace "My huge clob" with some other big text). Tip: For my test cases I typically use a Lorem Ipsum generator where I can specify the number of characters for example 33000 characters.

How can we parse this JSON and store for example the content of that in a CLOB field?

As I'm on 12c, should be simple right? The database is supporting reading JSON from SQL, so I first tried with JSON_TABLE, but there you can only define VARCHAR2 or NUMBER as data type, no CLOB, so went with VARCHAR2.

Here's the result:

SQL> select jt.question, jt.description
  2    from tbl_with_json, 
  3         json_table(json_clob, '$.items[*]'
  4           columns (
  5             question     varchar2 path '$.question',
  6             description  varchar2 path '$.description'
  7           )
  8*        ) as jt;

My Question                                                                                                                                                           

Oracle just returns null (nothing - blank) for the description!

But it's definitely not blank:

Next I tried the query like in my initial example, but the result was the same:

SQL> select a.json_clob.items.question as question, a.json_clob.items.description as description 
  2    from tbl_with_json a;

My Question                                                                                                                                                           

So the database will return a value when there's less than 4K (or possibly 32K depending the setting of your varchar2 size in the database) and it returns null when it's over this limit.

Hopefully Oracle Database 12.2 fixes this issue, but at the moment there's no native way to get to that data by using the Oracle supplied JSON functions. 

Edit 4-AUG: return null is default behaviour of Oracle, but you can specify you want an error instead. See the comments of Alex and Beda.

Ok, what can we try next?...

Since Oracle Application Express 5, APEX comes with a very nice package to work with JSON, APEX_JSON. This package has been heaven for us, especially with AOP.
So I thought to try to use the APEX_JSON.PARSE and store it in a temporary JSON so I can read it with the get_clob_output method:

SQL> declare
  2    l_data clob;
  3    l_json apex_json.t_values;
  4    l_return clob;
  5  begin
  6    select json_clob
  7      into l_data
  8      from tbl_with_json;
  9    apex_json.parse(l_json, l_data) ;
 10    apex_json.initialize_clob_output(, true, 0) ;
 11    apex_json.open_object;
 12    apex_json.write(l_json, 'items[1].description') ;
 13    apex_json.close_object;
 14    l_return := apex_json.get_clob_output;
 15    apex_json.free_output;
 16  end;
 17  /

Error starting at line : 1 in command -
  l_data clob;
  l_json apex_json.t_values;
  l_return clob;
  select json_clob
    into l_data
    from tbl_with_json;
  apex_json.parse(l_json, l_data) ;
  apex_json.initialize_clob_output(, true, 0) ;
  apex_json.write(l_json, 'items[1].description') ;
  l_return := apex_json.get_clob_output;
Error report -
ORA-20987: Error at line 6, col 18: value exceeds 32767 bytes, starting at Lorem ipsum dolor sit amet, consectetuer adipiscin
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 928
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 993
ORA-06512: at line 9

But as you can see, there's a limit in there as well. So APEX 5 doesn't return null, but it returns an error. Hopefully a future version of APEX removes this limit ;)

When I work with data, I prefer to do it straight in the database, but now I was stuck. At those moments you have to go for a walk, get some sleep and talk to others to get more ideas... My preferred development languages (in this order) are APEX, SQL, PL/SQL, JavaScript, Node.js, ... (and then all others)

Then I remembered a blog post of Kris Rice that SQLcl has the ability to run JavaScript too because  SQLcl includes Nashorn (A Next-Generation JavaScript Engine for the JVM). So after looking at some SQLcl script examples, I wrote my own little SQLcl script that reads out the clob and puts it in a variable "content":

SQL> script
  2     var Types = Java.type("java.sql.Types")
  3     var BufferedReader = Java.type("")
  4     var InputStreamReader = Java.type("")
  6     var GET_CLOB = "declare " + 
  7                    "   l_clob CLOB; " + 
  8                    " begin " + 
  9                    "   select json_clob " + 
 10                    "    into l_clob " + 
 11                    "    from tbl_with_json; " +
 12                    "   ? := l_clob;" + 
 13                    " end;"; 
 15     var cs = conn.prepareCall(GET_CLOB);
 16     cs.registerOutParameter(1, Types.CLOB);
 17     cs.execute();
 18     var clob = cs.getClob(1);
 19     cs.close();
 21     var r = new BufferedReader(new InputStreamReader(clob.getAsciiStream(), "UTF-8"))
 22     var str = null; 
 23     var content = "";
 24     while ((str = r.readLine()) != null) { content = content + str; }
 25     ctx.write(content);
 26  /
{ "items": [{ "client_id": -1, "registration_date": "2016-07-29T07:46:09.941Z", "question": "My Question", "description": "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor. Aenean massa. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Donec quam felis, ultricies nec, pellentesque eu, pretium quis, sem. Nulla consequat massa quis enim. Donec pede justo, fring

So the above reads the content of the clob which contains the JSON.
As we are in JavaScript I thought we can parse this JSON and navigate to the description field. Once we have it we store it in another table or do whatever we want with it.
Cool if it would work, no? And it did! :)

So lets finish this example. First we create a table to store the description field (the very big text).

SQL> create table tbl_with_description (description clob);


Here's the final script that will store the description node to another table :
- the ctx.write calls are there to send debug output
- the obj.items[0].description is how we get to the description node and we store that in a bind variable and execute another insert statement to save the description value:

SQL> script
  3  try {
  4     var Types = Java.type("java.sql.Types")
  5     var BufferedReader = Java.type("")
  6     var InputStreamReader = Java.type("")
  8     var GET_CLOB = "declare " + 
  9                    "   l_clob CLOB; " + 
 10                    " begin " + 
 11                    "   select json_clob " + 
 12                    "    into l_clob " + 
 13                    "    from tbl_with_json; " +
 14                    "   ? := l_clob;" + 
 15                    " end;"; 
 17     var cs = conn.prepareCall(GET_CLOB);
 18     cs.registerOutParameter(1, Types.CLOB);
 19     cs.execute();
 20     var clob = cs.getClob(1);
 21     cs.close();
 23     var r = new BufferedReader(new InputStreamReader(clob.getAsciiStream(), "UTF-8"))
 24     var str = null; 
 25     var content = "";
 26     while ((str = r.readLine()) != null) { content = content + str; }
 27     //ctx.write(content);
 29     var obj = JSON.parse(content);
 30     ctx.write("Question: " + obj.items[0].question + "\n");
 31     ctx.write("Description: " + obj.items[0].description + "\n");
 33     var binds =  {};
 34     binds.description = obj.items[0].description;
 36     var ret = util.execute("insert into tbl_with_description (description) values (:description)", binds);
 38     if (ret) {
 39       ctx.write("Insert done!\n");
 40     } else {
 41       ctx.write("Error :(\n");
 42       var err = util.getLastException();      
 43       ctx.write("\nERROR:" + err + "\n");  
 44     }
 46  } catch(e){
 47      ctx.write(e +"\n")
 48      e.printStackTrace();
 49  }
 51  /
Question: My Question
Description: Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor. Aenean massa. Cum sociis natoque penatibus et magnis dis ... eu,
Insert done!


SQL> select count(*) from tbl_with_description;


SQL> select substr(description,1,50) from tbl_with_description;

Lorem ipsum dolor sit amet, consectetuer adipiscin                              


I was blown away by this... and I see a lot of potential be able to run JavaScript against the database.

There's actually a way to load Nashorn in your database too, so you can do JavaScript, Node.JS etc. straight from your database. Nashorn came with Java 8, but it should run in Java 7 too, now the default version of Java in the Oracle Database is 6, so there're some extra steps to do to get it to work. Running JavaScript from the database is something I've on my list to do R&D in and I actually submitted an abstract to KScope17 where I will present my results on this topic (if it gets accepted!) :) 

So to recap this (longer) blog posts:
1) JSON is being used a lot these days and having the possibility to work with JSON in the Oracle database is very nice, but as we have seen in the above example, it can't do everything yet. It has a real issue with large nodes.
2) Knowing other languages and thinking out-of-the-box might come in handy; I would even say that JavaScript becomes more and more important for an APEX developer.
3) SQLcl is a great tool, if you don't use it yet, I would definitely recommend looking into it. 
4) Oracle Nashorn opens up an entire new set of possibilities.

In the last paragraph of this blog post I want to thank Kris Rice for his help understanding SQLcl script. Although there are many examples, it took me some time to get going and I did struggle to understand how to get to error messages for example. Although it's mostly JavaScript in the script, having some Java knowledge makes it easier. Time to refresh that a bit, it has been 15 years ago I did some real Java coding.

Hope this blog post will help you work with JSON and JavaScript within an Oracle context.

Edit 4-AUG: read the comments section for a way to get the CLOB out with PL/JSON.