Tuesday, January 27, 2015

Announcement: APEX 5.0 UI Training - May 12th


APEX 5.0 will be released between now and the end of May. People who have already spent some time on the Early Adopter versions 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 Mitland Hotel in Utrecht (NL) on Tuesday May 12.
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!

Friday, January 23, 2015

JSON for APEX Developers (part 2)

In the previous post we created a service that allowed us to give our data in JSON format.

Now let's focus on consuming that JSON. In this post I want to show how to use JSON data in the client (your browser), in a future post I'll show how to use JSON on the server (in the database).

If you want to play with JSON, open the console of your browser and create some text in JSON format - easy to do - and use the JSON.parse() function to create an object from it:

var text = '{"items": {"emp":[{"empno":7369, "ename":"SMITH"},{"empno":7499, "ename":"ALLEN"} ]}}';
var obj = JSON.parse(text);  

As you will probably do a call somewhere to get JSON, I'll move on with such an example, so we will call the service we created with ORDS in the previous post and we want to use that data in our APEX page.

So edit your APEX Page in the JavaScript section "Execute when Page Loads" and put
(note you can put your own url that generates the json):

$.getJSON("https://www.apexrnd.be/ords/training/emp_json/", function(json) {
  console.log(json);
});

We just called the url from JavaScript and output the result to the console of the browser so we see what we got back:


We see the JSON (javascript object/array) we get back from the url. Note that the array starts with 0 (and not 1).

We can now do anything we want with that data. Here we set some items on the page of the first employee:

  $('#P10_EMPNO').val(json.items[0].empno);
  $('#P10_ENAME').val(json.items[0].ename);

A more interesting example is when you want to integrate Flickr foto's on your web page.
The concept is the same, call a url, once received loop over the array (see .each) and create an image tag on the fly on your page:


Another example would be when you want to include a visualisation in your page and as data it needs the data in JSON format... You could do that with an AJAX call for example (application process, plugin, ...), but that is for another post.

Hopefully this post showed how you can interact with JSON within your APEX page by using JavaScript.
You find the online example at https://www.apexrnd.be/ords/f?p=DGIELIS_BLOG:JSON

Thursday, January 22, 2015

JSON for APEX Developers (part 1)

After my post Generate nested JSON from SQL with ORDS and APEX 5 I got some requests to explain more about REST and JSON, so let me start with JSON. I'll go more into REST in some future posts.

JSON stands for JavaScript Object Notation, it's a text based format to store and transport data.

It all comes from exchanging data, and finding a format that can easily be used by the "client" who needs to do something with the data. In the past XML (and SOAP) was used a lot to fill that need, between tags you found your data. With JSON it's kinda the same, but because many "clients" are now web pages, it makes sense to use something that is very easy to use by a browser.

Here's an example of how it was with XML:


The above XML looks like this in JSON:

{"items": {
  "emp":[
    {"empno":7369, "ename":"SMITH"},
    {"empno":7499, "ename":"ALLEN"}    
  ]}
}

To generate the XML, Oracle build that straight into the database. Here's a SQL statement that does it for you:

SELECT XMLELEMENT("items", XMLAGG(
         XMLELEMENT("emp",
           XMLFOREST(
             e.empno AS "empno",
             e.ename AS "ename")
         )
       )) AS employees
FROM   emp e

To generate the JSON from within the Oracle database takes a bit more effort. You find some nice posts by Morton and Lucas with examples to generate JSON with SQL. If we use the listagg technique our query looks like this:

select '{"items": { "emp":[' 
       || listagg( '{ '
       ||' "empno":"'||empno||'"'
       ||',"ename":'||ename
       ||'} ', ',') within group (order by 1) 
       || ']} }'as  json
  from   emp

Oracle database 12.1.0.2 has JSON support, but that is more to consume JSON, not to generate. As said in my previous post, APEX 5 has a nice package to generate the JSON or you can use ORDS to generate the JSON.

Let's look step-by-step how we can generate the JSON by using ORDS.

In APEX, go to SQL Workshop > RESTful Services and hit the CREATE button and fill in the details as below:


Once you hit Create Module it has generated a REST Webservice, but more important for this post is that you have now a url that you can provide to somebody to get the data in JSON format:


There are many options for this service, but if you don't want Pagination, put a 0 in Pagination Size and if you don't run in HTTPS, put a No in Require Secure Access.

By running the url https://www.apexrnd.be/ords/training/emp_json/ we now see our data in JSON.
In the next post we will consume that data in our web page.

Wednesday, January 21, 2015

Setting up Node and Oracle Database driver

Today Oracle introduced the node-oracledb driver, so you can easily connect from node to your Oracle database.

Previously I blogged about the Oracle VM you can download so you have your own local Oracle environment. Below are the steps to install the node driver and run a first example in that environment.
(Also read the official node-oracledb installation guide for a local database - 4. Installation with a local database).

First we need to install git (a source control system) - as the source of the oracle node driver is there.

$ sudo yum install git

Download the source of the node-oracledb driver

$ git clone https://github.com/oracle/node-oracledb.git

Next we need to get node, you can download it or I used wget to get the file for linux


Install Node

tar -zxf node-v0.10.35-linux-x64.tar.gz

We follow the guide to set the PATH variable

export PATH=$HOME/node-v0.10.35-linux-x64/bin:$PATH

and we set other variables to the Oracle client and run the installer from within the node-oracledb directory:


Depending your system it might take a few seconds and it should come back after a while with this:


So we have now node installed and the oracle node driver as a module available.

Next we run the first example. It's a select on a table in the HR schema, but that schema is by default locked. So we unlock it first:

sqlplus sys/oracle as sysdba



We need to change the dbconfig.js in the examples directory to point to our database:


And now we can run the first example:


The above lets you run SQL, PL/SQL etc. commands from within Node.

Happy playing with Node! But be warned, once you start with Node its addictive to try different node modules... for example run "npm install node-tts-api" and you have a node module to do text-to-speech :)

And this module is something you can call from within your APEX application (so we go the other way compared to above - we go from Oracle to Node in this case).
It takes only one Dynamic Action - on change of the item - execute Javascript:

var url = 'https://www.apexrnd.be/node/tts/' + $v(P13_TEXT);
$.get( url, function( data ) {
  var url = data;
  var snd = new Audio(url);
  snd.play();
});

And here's a small video that shows the TTS in action



If somebody knows a Node module with a women's voice I would love to hear that :)

The TTS demo is just a quick way to show the integrating of APEX with Node, but I've some more cool (and useful!) stuff coming up in future posts :)

Tuesday, January 20, 2015

Next ORCLAPEX-BE Meetup - Feb 2nd

Roeland organised a new APEX Meetup in Belgium on February 2nd.


This time no specific topic, everybody can get on stage for maximum 5 minutes to show something they did or to ask some feedback or help on a specific problem.

If Belgium is to far, no worries, there're meetups planned all over the world.

The next meeting in the Netherlands is on February 18th organised by Alex, Christian and Roel.

But as I said, many others, just check out http://apexmeetups.com for a more local event.

Monday, January 19, 2015

Setting up your own local Oracle Development environment in less than 15 minutes

The fastest and easiest way to setup your own local Oracle Development environment is by using the "Oracle Technology Network Developer Day" Database Virtual Box Appliance.

It contains:

  • Oracle Linux 7
  • Oracle Database 12c (12.1.0.2) EE (CDB/PDB)
  • Oracle Rest Data Services (ORDS)
  • Oracle Application Express (APEX)
  • Oracle SQL Developer and Oracle SQL Developer Data Modeler
Step 1: Download and install Oracle VM VirtualBox

Step 2: Download the VM image that contains all the pieces you need

Step 3: Import the VM: File > Import Appliance

Done...

You can now start using the VM.

Some extra steps I did was adding a Shared folder and setup the Network connection


Once you add the Shared Folder open up a Terminal in your VM and type: "mount -t vboxsf Downloads /mnt"  (Downloads is my OSX Downloads folder and /mnt is my mount directory in VirtualBox), that makes it easier to transfer files to the VM.

Here's a screenshot once you run the VM:


The nice thing with this VM is that everything is ready to be used and it includes labs, so you can do some exercises too and there's even a reset script to put everything back how it was.

Exercises are available for: Database 12c (including JSON, XML DB, ...), SQL Developer, APEX, REST Data Services and Cloud Services.

Who said installing Oracle was hard? :)

Sunday, January 18, 2015

Generate nested JSON from SQL with ORDS and APEX 5

When creating web applications, at some point you will need to interact with JSON. Either you consume JSON or you need to generate it to be able to use that cool widget you found.

A few years ago when I wrote about interacting and customising charts, XML was the data format. Today JSON is more common to use as it works so well with JavaScript. I needed to pass some nested JSON - here's an example: multiple customers have multiple orders and an order consists out of multiple items: 

What is the easiest way to generate that JSON data?

Luckily we have Oracle REST Data Services (ORDS), it literally is just one SQL statement!
Here you go: 


Save and presto you're done :) Hit the TEST button and copy/paste the generated JSON in for example JSONLint to validate the output and yep we have valid JSON.


But that is not all - it gets even better. APEX 5 comes with a new package apex_json which contains so many goodies, really a wonderful piece of code. The same SQL statement I call with the package and again I get the JSON I need. It's slightly different from what ORDS generated - ORDS has an items tag surrounding the data, but again it's valid JSON.
Note that APEX 4.2 has some JSON support too (apex_util.json_from_sql), but it doesn't go as far as the package you find in APEX 5.0 - for example the cursor syntax is not working there, but for straight forward JSON it does it job.


So this shows how easy it is to generate JSON these days. It has been different where we had to use the PL/JSON package (which was good too, but build-in is what I prefer).

And finally with Oracle Database 12c, and the JSON support straight in the database, consuming and querying JSON has been made so much easier, but that is for another post. If you can't wait, the link will bring you to the official 12c Database JSON doc.