Dimitri Gielis Blog

Subscribe to Dimitri Gielis Blog feed
I created this Blog to share my knowledge especially in Oracle Application Express (APEX) and my feelings ...
Updated: 5 days 12 hours ago

Installing SQLcl on OEL/RHEL

Thu, 08/04/2016 - 21:56
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: sun.org.mozilla.javascript.EvaluatorException: Java class "java.util.ArrayList" has no public instance field or method named "0".
javax.script.ScriptException: sun.org.mozilla.javascript.EcmaError: 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: gpw_e24=http%3A%2F%2Fwww.oracle.com%2F; oraclelicense=accept-securebackup-cookie" "http://download.oracle.com/otn-pub/java/jdk/8u102-b14/jdk-8u102-linux-x64.tar.gz"
$ 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 -versionjava 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)

Thu, 08/04/2016 - 21:36
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> 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> SQL> select a.json_clob.items.question as question, a.json_clob.items.description as description   2    from tbl_with_json a;
QUESTION----------------------------------------------------------------------------------------------------------------------------------------------------------------------DESCRIPTION----------------------------------------------------------------------------------------------------------------------------------------------------------------------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;
QUESTION----------------------------------------------------------------------------------------------------------------------------------------------------------------------DESCRIPTION----------------------------------------------------------------------------------------------------------------------------------------------------------------------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;
QUESTION----------------------------------------------------------------------------------------------------------------------------------------------------------------------DESCRIPTION----------------------------------------------------------------------------------------------------------------------------------------------------------------------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(dbms_lob.call, 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 -declare  l_data clob;  l_json apex_json.t_values;  l_return clob;begin  select json_clob    into l_data    from tbl_with_json;  apex_json.parse(l_json, l_data) ;  apex_json.initialize_clob_output(dbms_lob.call, true, 0) ;  apex_json.open_object;  apex_json.write(l_json, 'items[1].description') ;  apex_json.close_object;  l_return := apex_json.get_clob_output;  apex_json.free_output;end;Error report -ORA-20987: Error at line 6, col 18: value exceeds 32767 bytes, starting at Lorem ipsum dolor sit amet, consectetuer adipiscinORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 928ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 993ORA-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("java.io.BufferedReader")  4     var InputStreamReader = Java.type("java.io.InputStreamReader")  5       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;";  14   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(); 20   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);
Table TBL_WITH_DESCRIPTION created.

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  2    3  try {  4     var Types = Java.type("java.sql.Types")  5     var BufferedReader = Java.type("java.io.BufferedReader")  6     var InputStreamReader = Java.type("java.io.InputStreamReader")  7       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;";  16   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(); 22   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); 28   29     var obj = JSON.parse(content); 30     ctx.write("Question: " + obj.items[0].question + "\n"); 31     ctx.write("Description: " + obj.items[0].description + "\n"); 32   33     var binds =  {}; 34     binds.description = obj.items[0].description; 35   36     var ret = util.execute("insert into tbl_with_description (description) values (:description)", binds); 37   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     } 45   46  } catch(e){ 47      ctx.write(e +"\n") 48      e.printStackTrace(); 49  } 50   51  /Question: My QuestionDescription: 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!

Testing:
SQL> select count(*) from tbl_with_description;
  COUNT(*)----------         1
SQL> select substr(description,1,50) from tbl_with_description;
SUBSTR(DESCRIPTION,1,50)                                                        --------------------------------------------------------------------------------Lorem ipsum dolor sit amet, consectetuer adipiscin                              
SQL> 
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.

Enter your bets on Euro2016Challenge.eu now

Fri, 06/10/2016 - 10:56
Looks like I forgot to put on my blog also this year we created a bet site for the European Cup Soccer. Thanks to the people who reminded me to put this post on my blog :)
It all started in 2006 when I first created a site to promote Oracle Application Express (APEX). The site allowed to bet on the games of the World Cup. At that time everybody was using Excel files internally to put the scores together, enter the bets of the people... so I thought why not build it in APEX :) Oh the betting is for fun and honour ... so no money involved!
Since then every two years we have updated the site and enabled it again. Today almost 3000 people are playing with us. We changed a few times from url; first it was called DG Tournament, than the World Cup Challenge and this year it's the Euro 2016 Challenge.
So if you didn't put your bets in, there're a few hours left ... happy betting and that the best may win!

This year we (Belgium) have a chance to come far in the tournament, go go go Belgium! :)

Export your APEX Interactive Report to PDF

Tue, 06/07/2016 - 15:36
Interactive Reports (and Grids in 5.1) are one of the nicest features of Oracle Application Express (APEX) as it allows an end-user to look at the data the way they want, without needing a developer to change the underlying code. End-users can show or hide columns, do calculations on columns, filter etc.

Here's an example of an interactive report where highlighting, computation and aggregation is used.


More than once I get the question, how can I export this to PDF or print this Interactive Report?

Here're 3 ways of doing this:

1. Use your browser to Print to PDF

The challenge here's that you would need to add some specific CSS to get rid of the items you don't want to be printed, e.g. the menu, the header and footer and some other components like buttons.
Also if you have many columns, they might not fit on the page and the highlighting is not working when printed, but if you can live with that, it might be an option for you.


Here's the CSS you can use:

@media print {
  .t-Body-nav {
    display:none
  }
}


2. Use the download feature of the Interactive Report itself (Actions > Download > PDF)

This feature is build-in APEX and relies on a print server supporting XSL-FO; when using ORDS it will automatically work. If you're using Apache, you will need to configure a print server like BI Publisher or Apache-FOP.


When downloading to PDF, the result looks like this:

The PDF contains the data and we can specify a header, footer and how the columns look like, but we lost many features of the Interactive Report; no highlighting, no computation or aggregation.


3. Use APEX Office Print to print the Interactive Report in your own template defined in MS Word.

One of the unique features of APEX Office Print is that it's tightly integrated with Oracle Application Express and that it understands Interactive Reports as the source of your data.

Here're the steps:

- Create your template in MS Word and add {&interactive} tag where you want the Interactive Report to be


- Give your Interactive Report a static id:



- Add the APEX Office Print Process Plugin to your page and specify the template and the static id: 


And here's the result: 

I'm biased as we created APEX Office Print (AOP), but I just find it awesome :)In your Word template you just add one tag, that's it!
In all seriousness, we would really want to hear from you if this feature works for your Interactive Report. You can try AOP for free for 100 days. We're trying to be smart and are doing automatic calculations of the column width, but we probably can improve it even more. We introduced this feature with AOP v2.0 (MAR-16) and improved it in v2.1 (MAY-16).

Crowdsourced software development experiment

Thu, 05/26/2016 - 21:30
A few days ago I got an email about an experiment how to program with the crowd.
I didn't really heard about it before, but found it an interesting thought. In this experiment people will perform microtasks (10 minutes task), as a member of the crowd. People don't know each other, but will collaborate together. The system is distributing the work and supplies instructions. The challenge is in creating quality code that meets the specifications.
Job is still searching for some people to be part of the experiment, so I thought to put it on my blog, in case you're interested you find more details below and how to contact him.


Please, use HTTPS for your APEX apps

Wed, 05/25/2016 - 22:07
Why use HTTPS?

When you Google this question you get many different answers, but this answer of Google Developers answers it for me in short (click the link for more details):
  • HTTPS protects the integrity of your website/APEX app
  • HTTPS protects the privacy and security of your users
  • HTTPS is the future of the web; many new technologies only work with https (for example Service Workers; you can read more about Service Workers and APEX in my presentation)
Industry going to HTTPS

Before websites had an HTTP portion and an HTTPS portion, which became active when you would login to the site, but nowadays everything is under HTTPS. Google will actually rank your site higher when it's using HTTPS. Look at the sites you visit; many of them will now use HTTPS as a default.

HTTPS on localhost

If you're developing locally, you don't really need HTTPS on localhost, but I still like to have that.
Here're the steps I did in Chrome on my Mac (OSX) to get the nice green lock when developing locally (works also with APEX Front-End Boost)
  • In the address bar, click the little lock with the X. This will bring up a small information screen. Click the button that says "Certificate Information."
  • Click and drag the certificate image to your desktop. 
  • Double-click it. This will bring up the Keychain Access utility. Enter your password to unlock it.
  • Be sure you add the certificate to the System keychain, NOT the login keychain. 
  • After it has been added, double-click it. 
  • Expand the "Trust" section. "When using this certificate," set to "Always Trust"
  • Close Keychain Access and restart Chrome, and your self-signed certificate should be recognized now by the browser.
HTTPS on your own server

For years I've been using SSL certificates ordered from Godaddy, but depending the certificate you get, it might not be that cheap. The APEX R&D website is a multi-site certificate - the same certificate is used for the APEX Office Print website.

But there's some good news... you can get SSL for free too (and it's very easy to do!), thanks to Letsencrypt. I used Letsencrypt to protect the Euro2016challenge.eu APEX app/website for example.
Here's the Getting Started Guide from Let's Encrypt. This is the command I used (after installing the package):

./letsencrypt-auto certonly --webroot -w /var/www/euro2016 -d euro2016challenge.eu -d www.euro2016challenge.eu


If you're not yet on https with your APEX app/site, I would definitely recommend looking into it :)