Dimitri Gielis

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

Installing SQLcl on OEL/RHEL

Thu, 2016-08-04 16:55
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 -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 :)

Categories: Development

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

Wed, 2016-08-03 17:29
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.

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 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("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 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!


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.
Categories: Development

Enter your bets on Euro2016Challenge.eu now

Fri, 2016-06-10 05:55
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! :)

Categories: Development

Export your APEX Interactive Report to PDF

Tue, 2016-06-07 10: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).

Categories: Development

Crowdsourced software development experiment

Thu, 2016-05-26 16: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.



Categories: Development

Please, use HTTPS for your APEX apps

Wed, 2016-05-25 17: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 :)

Categories: Development

Web technology in APEX Development

Fri, 2016-04-22 04:54
How did you get started with developing your first APEX app? 

My guess is either you went to https://apex.oracle.com and got a free account or Oracle Application Express was already in your company and somebody told you the url you could connect to. For me that is really the power of APEX, you just go to an url and within minutes you created your first app.

Staying within the APEX framework?

With APEX you create web application, but you don't have to worry about CSS, JavaScript, HTML5, Session State etc. it just comes with the framework. In APEX you have Universal Theme to visually adapt the look and feel of your app, there're Dynamic Actions that do all the JavaScript for you and the framework is generating all the HTML and processing that is necessary.
So although we are creating web applications, at first we are not doing what typical web developers do (creating html, css, javascript files).
Oracle closely looks at all the web technology, makes choices which streams they will follow (e.g. JQuery framework) and implements and tests it so we don't have to worry about a thing.

Going to the next level?

The web is evolving fast, and I mean really fast (!) so maybe you saw something really nice on the web you wish you had in your APEX app, but it's not yet declaratively available... now the nice thing about APEX is that you can extend it yourself by using plugins (see the plugins section on apex.world) or just by writing the code yourself as other web developers do.


Trying new web technology locally

When you want to try those shiny new web things in your APEX app, I recommend trying to get those things working locally first. Last year for example I gave a presentation about Web Components at different Oracle conferences and this year I'll present on Service Workers. All the research I did on those topics where initially not in an APEX context. But how do you get started to try this now?

The first thing you need is a local web server. Depending the OS you're on, you might already have one (e.g. IIS, Apache, ...), if not, here's what I do on OSX.
OSX comes with Python and that allows to create a simple web server.
Open Terminal and go to the directory where you want to test your local files and run:

$ python -m SimpleHTTPServer 8000   (Python 2.7)
$ python3 -m http.server 8000   (Python 3.0)

There're many other ways to have a local web server, see for example this article or a simple web server based on node.js.

The next thing is to start developing your HTML, CSS, JavaScript etc.
To do this development, you probably want some tools; an editor like Sublime or Atom, a CSS and JS preprocessor, Browser extensions, build tools like Gulp etc.
You don't need all those tools, just an editor is fine, but soon enough you want to be more efficient in your development, and tools just help :) Here're some nice articles about different tools: Google Developers - Getting Started, Keenan Payne 13 useful web dev tools and Scott Ge list of web development tools.

Going from custom web development to APEX - use APEX Front-End Boost

So you have your local files developed and next is to integrate them in your APEX app.
You add some code to your APEX pages and upload the files so APEX can see them.
If everything works immediately - great, but most of the time you probably need to make more changes, so you change your local files, test again, upload etc. You could streamline this a bit with setting up a proxy or referencing localhost files while in development... But then you're happy your part of the APEX community...


To ease the above development and integration with APEX, Vincent Morneau and Martin Giffy D'Souza created the excellent APEX Front-End Boost package. The package is using many of the above tools behind the scenes, but it's all integrated in a nice box. This video goes in full detail what the tool is doing for you and how to use it. In short; it fills the bridge of working with a file locally, making it production ready and seeing it immediately in your APEX app :)

In the next post I'll talk about the importance of using https and also setting it up for localhost (also for APEX Front-End Boost).

    Categories: Development

    Highlight numbers in an APEX Report (SQL and Class)

    Wed, 2016-01-20 09:31
    Last year I blogged about highlighting negative numbers in an APEX Report, the CSS only way.
    At that time I gave two alternative approaches; by using JQuery or SQL, but it looks like I didn't do those posts yet, till somebody reminded me. This post is about using SQL to highlight something in a report.

    Let's say we want to highlight negative numbers in a report (as in the previous post):


    We have some CSS defined inline in the Page:

    .negative-number {
      color:red;
    }

    The negative-number class we will add to some values. All the logic to know if it's a negative number will be in SQL. Why SQL you might ask? This example is very simple, but you could call a function which has a lot of complexity to decide if you want to assign a class to a record or not, the principe of this example is more important, that you can use logic in SQL to work with CSS.

    The SQL Query of the Report looks like this. Watch for the case statement where we say when to assign a value for the class:

    select 
     description,
     amount,
     case 
       when amount < 0
       then 'negative-number'
       else ''
     end as class
    from dimi_transaction
    order by id

    Finally we assign the class to the amount, by adding a span in the HTML Expression of the Amount column:


    The Class column you can make Conditional = Never as it's something we just use behind the scenes.

    That's how you make a bridge between SQL and CSS.

    You can now play more with the case statement and even let the class or style e.g. color, come from a user defined table... unlimited possibilities :)

    Categories: Development

    Opening of APEX R&D New Zealand

    Thu, 2015-10-15 02:20
    We're really excited to announce that next to our Belgium office, we have opened an office in New Zealand too. This will allow us to cover multiple timezones and better serve the Australian and New Zealand market. You can read more about it on Lino's blog

     APEX R&D

    Contact our team of experienced professionals for innovative, reliable and cost effective Oracle Application Express solutions. 

    Regarding any custom application development, training/coaching or consulting related to Oracle APEX technology we would love to hear from you.

    For more information please contact our Belgium or New Zealand office.

    We look forward working with you.

    Categories: Development

    Submit your abstract for KScope 16 - only 3 days left

    Mon, 2015-10-12 08:01
    Only 3 days left to submit your abstract for KScope 16... so don't wait longer, submit now! :)


    My first ODTUG conference was in 2006 and it was awesome. On this blog you'll find different blog posts about my experience and why I love it, so I won't go over that... but from time to time I get some questions about being a presenter at the event.

    "What should I submit, I don't know what to talk about?"

    The topics in the Oracle Application Express track are following:
    • Integration: Relates to any work where other products are integrated with APEX or a gap is bridged between platforms. Perhaps is integration with EBS, SAP, Raspberry Pi, Node.js, OAuth, REST, etc...
    • New Release: Presentations that relate to the latest version of APEX. Typically the Oracle development team is presenting those topics.
    • Real World: Everybody loves real life examples. This is anything where a real life system is presented. The problem is explained and how APEX was used to solve it.
    • Detail Plunge: These are presentations on any topic, but the topic, even if it seems narrow, is explored in detail and with focus. It doesn't have to be complicated. I could be building plugins or Dynamic Actions.
    • Other Application Express: Everything else goes here. This is the catch-all bucket.
    You can submit up to 4 abstracts.

    The way I chose my topics typically fall in one of following categories:



    "Why even bother, I won't be selected"

    Well, without submitting you don't know. What do you have to lose? I'm sure I don't speak for myself when I say, I would love to meet new people and hear about what they do and discover. There's not a single day I don't learn anything, tech or non-tech. This weekend I learned something new and the feature has been in APEX for many years! (but that's for another blob post)
    The level of presentation is very broad as well. We need presentations of all levels; beginners, intermediate and advanced.


    "I don't know how to write an abstract"

    On the internet there're many links how to present and write an abstract. On the ODTUG site you'll find some tips and examples.


    Give it a go, you'll feel good when you've done it. Here's the link. It's all about the learning experience!

    All the best and I look forward meeting you in Chicago.
    Categories: Development

    Column Group Headings in an Interactive Report (APEX 5.0)

    Tue, 2015-09-29 06:12
    In 2008 I blogged about how to add Group Headings into an Interactive Report in Oracle Application Express (APEX 3.x!).

    Here's an example what I mean with Group Headings, you want to logically group certain columns (e.g. Empno, Ename) and have a group name above them (e.g. Group 1):


    The approach I used at that time worked, but it required a lot of manual work. In 2009 Martin D'Souza improved it and wrote a blog post about that. In 2010, when plugins were introduced in APEX 4, Martin wrapped it all in a nice APEX plugin which made adding Column Group Headings almost declarative. The plugin was further improved and put on GitHub.

    Today, having APEX 5, and the Interactive Reports being changed a lot behind the scenes, the plugin was broken. As we're in the middle of restructuring how to share code, plugins etc., I forked Martin's git repo and made the plugin compatible with APEX 5 and Universal Theme (see version 1.0.4), so when you need it, you can grap it from the forked repo. At a later time the plugin will be moved to OraOpenSource, so depending when you read this post, check out OraOpenSource as Martin, I and many other developers will start putting our open source code on OraOpenSource.

    Here's a quick how to use the plugin.

    Step 1: Download the file dynamic_action_plugin_com_clarifit_apexplugin_ir_column_grouping.sql
    Step 2: Import the plugin in your APEX application
    Step 3: Define the Groups in the Interactive Report (you can right click on Column Groups in the  Attributes of your Interactive Report and add the Groups you need):


    Step 4: Assign the group to the columns of your report (you can multi select the columns and in the right pane you can select the group):


    Important: define for every column that has a group defined, a static id equal to the name of the column (e.g. ENAME).

    Step 5: Add the dynamic action plugin to the page
    When:
    - Event: After Refresh
    - Selection Type: Region
    - Region:
    Action:
    - IR Column Grouping [Plug-In]



    That's it... note that when you scroll down and you have sticky headers, the group will stay with it.


    Categories: Development

    Custom image on your APEX app login page

    Sun, 2015-09-27 16:15
    In a comment on my post APEX 5.0: pimping the Login page I got a question how to put your own logo or a custom image on the login page, instead of an icon.

    You only need a bit of CSS to do the trick:

    You can add the above CSS to your login page - Inline CSS in page attributes or you can add it to your page template or custom CSS in Universal Theme.

    The result is this:


    To differentiate the login page you can do a lot more, here're some things we show during our APEX UI training:

    • add transparency to the login box
    • add a background image to your entire page (blurred or not)
    Again, you can do that with just some CSS.
    Categories: Development

    ORDS... Yes we can!

    Fri, 2015-09-25 04:55
    On September 13th I got a nice surprise, an email of Steven Feuerstein with the message: "You have been selected as a finalist in the ORDS category for the 2015 Oracle Database Developer Choice Awards!"

    It's always nice to get recognition for the efforts you do, so thanks so much already for the nomination. I didn't make publicity yet in order to get some up-votes, but I hope by reaching out to the people who read my blog, I gain some more up-votes :)

     

    To be honest, I wondered why I deserved this nomination, especially in a category that I'm probably less "known" for.
    After giving this more thought, I remembered ORDS actually started being very much linked to APEX. In the early days ORDS was even being called the "APEX Listener", and I was one of the early adopters and promoters of using the APEX Listener (now ORDS) in your APEX architecture.

    In 2012 I literally travelled around the world (Belgium, the Netherlands, UK, San Francisco, San Antonio, Uruguay, Brazil, Chile) to talk about why you should move to the APEX Listener.



    When looking back at that presentation, today it still stand; you still have different options for your APEX architecture, but we don't have to convince anymore about the benefits of choosing for ORDS. ORDS is now main-stream, widely adopted and proven technology. Unless it's a legacy system, I don't really see any reason anymore why you should not use ORDS in your architecture.

    ORDS evolved a lot over time, and the new name reflects more what the core feature is "Oracle REST Data Services". REST web services became so important in the last years and that is exactly what I've been blogging and talking about lately (see further).

    In my presentation of Microsoft Sharepoint and Oracle APEX integration (given in San Francisco, BeNeLux and Germany) I talk about the architecture and how you get your APEX data in Microsoft Sharepoint by using ORDS. But also the other way round, by using Sharepoint APIs, REST web services come into play. When you want to integrate with other systems, ORDS can really help.



    I didn't blog much yet about a really interesting R&D project we've been working on in the last months - using wearables to capture sensor and patient data. At the Oracle Mobile day in the Netherlands I did a presentation which explains it in more detail. We developed native smarth phone applications that call REST web services all build in ORDS. ORDS is getting or pushing the data into our Oracle Database using JSON. Next to that we've dashboards in APEX to "see" and work with the data. We learned and still learn a lot of this project; about the volume of data, the security etc. but without ORDS it would not have been possible.



    And finally a product where I'm really proud of APEX Office Print (AOP). I found it always a challenge to get documents out of APEX. I'll do some more blog posts about it in the future, but where APEX is so declarative to build your web applications, it's far from declarative to get documents out in Word, Excel, Powerpoint or PDF (at least without BI Publisher). With APEX Office Print we hope to address that. Just one APEX plugin where you define the template and which data you want to use and presto you get your document. Behind the scenes we use JSON and having ORDS makes it really easy to generate it. If you want to know more about the components behind the printing solution (and do it yourself), you'll find that in my presentation about Printing through Node.js which I presented in different countries and will present at Oracle Open World.



    Furthermore, if you're interested in reading more about JSON, I've done a series of blog posts and was interviewed by Oracle about it, you find the links here.

    Hopefully by doing this post you see the power of ORDS and you get some ideas yourself how to best leverage the power of this wonderful piece of software. If you liked my "ORDS-efforts" and want to give me an up-vote in the Database Developer Choice Awards, I really appreciate that.

    Thanks so much,
    Dimitri

    Categories: Development

    Report Carousel in APEX 5 UT

    Mon, 2015-09-14 10:45
    The Universal Theme in APEX 5.0 is full of nice things.

    Did you already see the Carousel template for Regions
    When you add a region to your page with a couple of sub-regions and you give the parent region the "Carousel Container" template it turns the regions into a carousel, so you can flip between regions.

    I was asked to have the same functionality but than on dynamic content.
    So I decided to build a report template that would be shown as carousel. Here's the result:



    I really like carousels :)

    Here's how you can have this report template in your app:
    1) Create a new Report Template:


    Make sure to select Named Column for the Template Type:


    Add following HTML into the template at the given points:




    That's it for the template.

    Now you can create a new report on your page and give it the template you just created.
    Here's the SQL Statement I used:

    select PRODUCT_ID          as id,
           PRODUCT_NAME        as title,
           PRODUCT_DESCRIPTION as description,
           product_id,       
           dbms_lob.getlength(PRODUCT_IMAGE) as image,
           'no-icon'           as icon,
           null                as link_url 
      from DEMO_PRODUCT_INFO

    Note 1: that you have to use the same column aliases as you defined in the template.
    Note 2: make sure you keep the real id of your image in the query too, as otherwise you'll get an error (no data found)

    To make the carousel a bit nicer I added following CSS to the page, but you could add it to your own CSS file or in the custom css section of Theme Roller.


    Note: the carousel can work with an icon or an image. If you want to see an icon you can use for example "fa-edit fa-4x". When using an image, define the icon as no-icon.

    Eager for more Universal Theme tips and tricks? check-out our APEX 5.0 UI training in Birmingham on December 10th. :)

    For easier copy/paste into your template, you find the source below:

     *** Before Rows ***  
    <div class="t-Region t-Region--carousel t-Region--showCarouselControls t-Region--hiddenOverflow" id="R1" role="group" aria-labelledby="R1_heading">
    <div class="t-Region-bodyWrap">
    <div class="t-Region-body">
    <div class="t-Region-carouselRegions">
    *** Column Template ***
    <div data-label="#TITLE#" id="SR_R#ID#">
    <a href="#LINK_URL#">
    <div class="t-HeroRegion " id="R#ID#">
    <div class="t-HeroRegion-wrap">
    <div class="t-HeroRegion-col t-HeroRegion-col--left">
    <span class="t-HeroRegion-icon t-Icon #ICON#"></span>
    #IMAGE#
    </div>
    <div class="t-HeroRegion-col t-HeroRegion-col--content">
    <h2 class="t-HeroRegion-title">#TITLE#</h2>
    #DESCRIPTION#
    </div>
    <div class="t-HeroRegion-col t-HeroRegion-col--right"><div class="t-HeroRegion-form"></div><div class="t-HeroRegion-buttons"></div></div>
    </div>
    </div>
    </a>
    </div>
    *** After Rows ***
    </div>
    </div>
    </div>
    </div>
    *** Inline CSS ***
    .t-HeroRegion-col.t-HeroRegion-col--left {
    padding-left:60px;
    }
    .t-HeroRegion {
    padding:25px;
    border-bottom:0px solid #CCC;
    }
    .t-Region--carousel {
    border: 1px solid #d6dfe6 !important;
    }
    .t-HeroRegion-col--left img {
    max-height: 90px;
    max-width: 130px;
    }
    .no-icon {
    display:none;
    }
    Categories: Development

    Expert Oracle Application Express (second edition) available

    Thu, 2015-08-20 06:03
    During my holiday Apress released the second edition of Expert Oracle Application Express.
    In the second edition we not only updated the content to APEX 5.0, but there're some new chapters in too from new authors. In total you get 14 chapters from 14 different authors.
    I believe it's a very nice book with great content and it's for a good cause. Roel describes it very nicely in his blog post "the goal is to raise as much money as we can for the funds that support the relatives of two of the greatest Oracle APEX Development Team members who passed away a few years ago: Carl Backstrom and Scott Spadafore."


    You can get it from the Apress website or from Amazon. Happy reading :)

    Categories: Development

    First patch set (5.0.1) released for APEX 5.0

    Thu, 2015-07-16 16:22
    I know some people waiting till the first patch set that comes available after a major Oracle APEX release... today you no longer have to wait, APEX 5.0.1 is now available.

    In the patch set notes you can read what changed.

    If you're still on APEX 4.x you can go immediately to APEX 5.0.1, you only need to download the latest version on OTN.

    If you're already on APEX 5.0, you can download the patch set from support.oracle.com, search for patch number 21364820. Applying the patch took less than 5 minutes in my environment.



    This patch set updates the Universal Theme too, so don't forget to update your images folder. When you login in APEX after the patch, it will check if you have the correct images folder, if not it will give you an alert. Although I updated the images directory I still got that alert due to browser caching. Refresh your page and it should be ok.

    Note that it's important to be on APEX 5.0.1 when you use APEX Office Print - currently available in beta for a select audience, public release end of this month (July 2015). Behind the scenes we use the APEX_JSON and APEX_WEB_SERVICE packages which got an update in APEX 5.0.1.

    And finally, there's a nice new D3 chart available in APEX 5.0.1 called "D3 Collapsible Treemap Chart"


    Happy upgrading...

    Update 18-JUL-2015: there're still a couple of known issues, please read those too and install some patchset exception when necessary.
    Categories: Development

    Make even more of UKOUG Tech15: APEX 5.0 UI Training - Dec 10th in Birmingham

    Tue, 2015-06-30 01:48

    APEX 5.0 has been released this spring. People who have already spent some time on this new version know this version is packed with new features aimed to make APEX developers even more productive, like the Page Designer.
    Another striking new subset of features is aimed at creating better looking user interfaces for your APEX applications in an easy and maintainable way. 
    The definition of user interface components in APEX 5.0 is very different to what we're used to. For example there is a new Universal Theme with Template Options and a Theme Roller. To get you up and running with this new toolset as quickly as possible, Dimitri Gielis of APEX R&D and Roel Hartman of APEX Consulting have joined forces and set up a one day course fully aimed at APEX 5.0 UI. So if you want to know not only how to use the new Theme, but also how to modify it to fit your needs, this is the event you should attend!

    The training will be at the Jury’s Inn in Birmingham (UK) on Thursday Dec 10 - so conveniently immediately after the UKOUG Tech15 conference.
    More information and registration see www.apextraining.eu

    If you are from another country and think this training should be available in your country as well, please contact us - then we'll see what we can do!
    Categories: Development

    Fixing Super LOV in Universal Theme

    Thu, 2015-05-21 15:08
    When you migrate to APEX 5.0 and the Universal Theme you might see that some plugins are not behaving correctly anymore. In this post I'll discuss the Enkitec Modal LOV plugin.

    When I ran the plugin in my APEX 5.0 app with the Universal Theme it looked like this:


    There's too much space in the search bar and the close button is not showing up with an icon.

    Here're the steps I did to fix it. First you need to download the css file of the style you use and the js file from the plugin in Shared Components. I use the smoothness.css style most of the times, so I'll use that as an example.

    To fix the close icon, add !important to the png:

    .ek-ml .ui-state-default .ui-icon {
    background-image: url(ui-icons_888888_256x240.png) !important;
    }

    Note: you can do that for all those png on line 467 till 489.

    To fix the height, add following css to smoothness.css:

    .superlov-button-container {
      height:50px;
    }

    And finally in enkitec_modal_lov.min.js change the height of the searchContainer from a dynamic height (r) to 0px:

    $searchContainer.css("height","0px")

    Next upload those files again to the plugin.

    When you run the plugin it should give you this result:


    Now the bigger question is; do we still need that plugin? In APEX 5.0 there're native Modal Pages, so you could create an Interactive Report and set the page as a Modal Page. Next you can hook that up to a button or link and you've just build your own Modal LOV.

    I still like to use the plugin at the moment (as it's just one item on the page), but it could use a refresh to make it look nicer and more inline with Universal Theme.

    Wonder what you think - would you build your own Modal LOV in APEX 5.0 or would you still prefer to use a plugin? 

    Categories: Development

    2 minute Tech Tip: Working with JSON in APEX

    Tue, 2015-05-19 17:30
    On Monday Bob Rhubart did a video call with me in his series of 2MTT (2 Minute Tech Tip) on YouTube. You find my 2MMT here.

    I talked about using JSON and APEX and gave two examples were we use it.

    In previous blog posts I gave more details on those techniques. Here's a quick overview:
    Categories: Development

    Change first day of week in APEX 5.0 Calendar

    Tue, 2015-05-19 01:52
    APEX 5.0 comes with a new calendar region, which is way nicer than the previous calendar in APEX. It has more features, looks better and is also responsive. Behind the scenes you'll see the calendar region is based on Full Calendar.


    In Belgium we use Monday as first day of the week, whereas in the US they seem to use Sunday as start of the week in the calendar overview. I've been integrating Full Calendar before, so I knew that library had an option to set the first day of the week. You could either specify an option called firstDay and set that to 1, or you could change the language, and depending the language it would adjust the start day of the week.

    In APEX 5.0 I looked for that option, but there's not a specific attribute to set the first day of the week, instead it's based on the language of your application. If you go to Shared Components > Globalization Attributes by default it's set to en, which has Sunday as start of the week. If you set it to en-gb it will have Monday as start of the week.


    I searched some time to find how to do it, so hopefully this post will help others to find it more easily. Thanks to Patrick for sharing the way it was implemented.

    Categories: Development

    Pages