Feed aggregator

Singapore Maths Question Solution and Very Interesting Observation (The Trickster)

Richard Foote's Blog - Thu, 04/23/2015 - 08:22
OK, time to reveal the solution to the somewhat tricky Singapore maths exam question I blogged previously. Remember, there were 10 dates: May 13   May 15   May 19 June 13   June 14 July 16   July 18 August 14   August 15   August 16 Bowie only knew the month of my birthday, Ziggy only knew the day. Bowie […]

Enable Real Application Security (RAS) in APEX 5.0

Dimitri Gielis Blog - Mon, 04/20/2015 - 21:25
Oracle Database 12c introduced Oracle Real Application Security (RAS), the next generation Oracle Virtual Private Database (VPD). In APEX 5.0 RAS is declaratively build-in. Follow the below steps to enable it:

Login to the INTERNAL workspace and go to Manage Instance > Security:

In the Real Application Security section set Allow Real Application Security to Yes.

Next login to the Workspace your Application is build in and go to your Authentication Scheme.
You'll see a new section in there called Real Application Security.



The dropdown has following possibilities:

  • Disabled: Real Application Security does not get used in the application. 
  • Internal Users: APEX creates a RAS session and assumes that all users are internal and passes false via the is_external parameter to dbms_xs_sessions.assign_user. 
  • External Users: RAS session created and true gets passed via the is_external parameter to dbms_xs_sessions.assign_user. 

The last two options enable RAS Mode and make the Dynamic Roles and Namespaces shuttle available. (from the help in APEX) Make sure that the users get privileges to access the application's schema objects. For External Users, you can for example grant database privileges to a RAS Dynamic Application Role and configure it in this authentication scheme as a Dynamic Role. You can also enable roles via a call to apex_authorization.enable_dynamic_groups, e.g. in a Post-Authentication procedure.

You can read more about Oracle Real Application Security and view an entire example how to set up RAS at the database side. I'm still learning about all the RAS features myself too, but thought to already share the above. I plan to include a chapter in my e-book about RAS and APEX 5.0 with a real-case example, as I see a big benefit for using it in a highly regulatory, secure and audited environment.


JSON for APEX Developers (part 3): Querying JSON in Oracle DB 12c

Dimitri Gielis Blog - Fri, 04/17/2015 - 15:34
In previous blog posts I talked about JSON for APEX Developers:
In this post I want to show how you store and query JSON data straight in the database.

To start, create a table to store the JSON object:

CREATE TABLE ssn_json
   (id          NUMBER GENERATED ALWAYS AS IDENTITY NOT NULL,
    date_loaded DATE,
    json_document CLOB
    CONSTRAINT ensure_json CHECK (json_document IS JSON));

I inserted a couple of records in the table:


Here's in more detail the JSON in the json_document column:

Now to query the JSON data I can do :


You basically say j (table) . json_document (column) . analyses (first entry in JSON) . ... (other fields of the hierarchy you want to navigate to)
 
You see the data I get back is actually two records as I actually get back the JSON array.

To go into the array and see for example the first record, I can use json_value:


Note that the array start with 0.

And finally to see both records that are in the JSON array I can make use of json_table like this:


The above is just a grasp of what you can do with JSON directly in the database (12c) by using SQL.
If you're interested to read more about how to manipulate JSON in the database, have a look at the documentation, it contains a wealth on information with great examples.

As you can basically query the JSON with SQL you can use this SQL in your APEX reports... in the coming days I'll show you a real case where I used the above techniques to do some interesting searching in data.

Oracle APEX 5.0 released today

Dimitri Gielis Blog - Wed, 04/15/2015 - 21:36
After 2.5 years of development, today is the day APEX 5.0 is publicly released and ready to be downloaded to install on your own environment.

In my view it's the best release ever. Not so much of the new Page Designer - although that is definitely a piece of art and it increased productivity even further - but because it's the first time whole of APEX got refreshed and every piece was put under a radar to see how it could be improved. All the small changes and the new UI, together with the Page Designer makes it a whole new development tool, without losing it's strengths from before.

Also note that APEX 5.0 enables many new features build on top of the Oracle Database 12c features, so if you're on that database, you'll see even more nice features.

If you wonder if you should wait with upgrading to APEX 5.0 because you're afraid that your current APEX applications break, I can only share that I upgraded many of my applications as part of EA/beta and most of my apps kept running without issues. As always you have to try your applications yourself, but the APEX development team spend a lot of time trying to keep things backwards compatible. But make sure to have a look at the APEX 5.0 release notes and known issues as they contain important information about changes, expected behaviour and workarounds.

You can develop online on apex.oracle.com or you can download APEX 5.0 and install into your own environment.

Good Singapore Maths Students Would Likely Make Good Oracle DBAs (Problems)

Richard Foote's Blog - Wed, 04/15/2015 - 01:46
An interesting mathematics based question from a Singapore high school exam has been doing the internet rounds in the past few days. Considering it’s aimed at high school students, it’s a tricky one and obviously designed to filter out the better students, in a country with a very good reputation for churning out mathematically gifted […]

Oracle Database In-Memory Test Drive Workshop: Canberra 28 April 2015

Richard Foote's Blog - Mon, 03/30/2015 - 03:17
I’ll be running a free Oracle Database In-Memory Test Drive Workshop locally here in Canberra on Tuesday, 28th April 2015. Just bring a laptop with at least 8G of RAM and I’ll supply a VirtualBox image with the Oracle Database 12c In-Memory environment. Together we’ll go through a number of hands-on labs that cover: Configuring the Product Easily […]

Thoughts from a developing CIO

Rick's Blog - Thu, 03/19/2015 - 16:41
Thoughts, thought prompts (questions), small musings, golden ideas, tips, hacks, and some ironies to guide the open mind on the path toward truth. My motivation is enable high customer and shareholder satisfaction, dependable and innovative business, and peaceful and flawless execution of strategy. Living after the manner of happiness for all. Nothing I know is absolute. Some of my writings are designed to motivate and move the reader and myself to chose a better path and not settle for aimless repetition of yesterdays work. Dare to live your life on purpose.

Working with multiple Trees on the same APEX Page (4.2/5.0)

Dimitri Gielis Blog - Mon, 03/16/2015 - 21:36
Ever tried to put multiple Trees on the same page in APEX 4.2?

Here's my example:


On the left Tree the "Selected Node Link with" is set to hidden item P1_TREE1, on the right Tree the value that sets the tree is set to P1_TREE2. At least it should do that, but by default if you're values are not unique - in this case empno is the value behind both trees - it doesn't work...
Your first tree will be highlighted twice; one time for ALLEN and one time for SCOTT and not as it should be, on the left ALLEN selected and on the right SCOTT selected.

To fix this issue in APEX 4.2, you need to adapt your select statement so you have unique values for both trees. I typically put a meaningful character in front of the value; for example you can concatenate 'N' for new values (new tree) and 'O' for old values (old tree).


So that fixes the issue of the "Selected Node Page Item" issue with non unique values.
Behind the tree implementation in APEX 4.2, jsTree is used. APEX is not using the most recent version and the way the JavaScript is coded doesn't really work that nicely. If you really like jsTree and for example want to search in it, or you have a very large dataset, check out Tom Petrus' tree plugin and explanation.

So what about APEX 5.0? The tree implementation in APEX 5.0 has the same functionalities, but is a lot better. Behind the scenes jsTree is not used anymore, instead the tree that is also used in the Page Designer is used. The issue with unique values is off the table, so no worries anymore in APEX 5.0.


APEX 5.0: pimping the Login page

Dimitri Gielis Blog - Thu, 03/12/2015 - 09:21
When you create a new application in APEX 5.0, the login page probably looks like this:


I love the build-in login page of APEX itself - luckily it's easy enough to build that in our own apps too. Thank you APEX Dev team!

The first step is to change the region type to be of Login Region Template:


We want to add a nice icon on top of the Login text. You can use the Icon CSS Class in the Region options - in this case I opted for fa-medkit:

Next up is making the Login button bigger and make it the complete width like the items.In APEX 5.0 you can use the Template Options to do that:

Once we stretched the Login button it fits the entire size.
Next up is getting some icons in the username and password field.For the username we use the "icon-login-username" css class.Instead of the label we make that hidden and specify a placeholder, so before you start typing you see the word username and when you start typing the text disappears.

For the password field we do the same thing, but for the css class we specify "icon-login-password".


Finally your login screen looks like this:


Great? Absolutely - and so easy with APEX 5.0!

What's next? Is there anything better? Euh... yes, what about live validation?
Sure we can do that in APEX 5.0 without too much hassle :)) Thanks once again APEX Dev Team!

In the item make sure the item is set to Value Required and add in the Post Text following span:


That will give you a nice visual indication if you entered text:


Cool? Creating login pages in APEX 5.0 is ... (you fill in the word)

Interested in more? We're doing an APEX 5.0 UI Training in May.

Loading CSV files with special characters in Oracle DB

Dimitri Gielis Blog - Tue, 03/10/2015 - 16:08
I often need to load the data of Excel or CSV files into the Oracle Database.

Ever got those annoying question marks when you try to load the data? or instead of question marks you just get empty blanks when the file is using special characters? Here's an example:


My database characterset is UTF-8, so ideally you want to load your data UTF-8 encoded.
With Excel I've not found an easy way to specify the encoding to UTF-8 when saving to a CSV file.Although in Excel (OSX) - Preferences - General - Web Options - Encoding, I specified UTF-8, it still saves the file as Western (Mac OS Roman).
I've two workarounds I use to get around the issue. Open the file in a text editor e.g. BBEdit and click the encoding option and select UTF-8.

Another way is to open Terminal and use the iconv command line tool to convert the file

iconv -t UTF8 -f MACROMAN < file.csv > file-utf8.csv
If you get a CSV file and you want to import it in Excel first, the best way I found is to create a new Workbook and import the CSV file (instead of opening directly). You can import either by using File - Import or Data - Get External Data - Import Text File. During the import you can specify the File origin and you can see which data format works for you.


After the manipulations in Excel you can save again as CSV as outlines above to make sure you resulting CSV file is UTF-8 encoded.
Finally to import the data you can use APEX, SQL Developer or SQLcl to load your CSV file into your table.

Oracle Database Tools updated - check out SQLcl

Dimitri Gielis Blog - Mon, 03/09/2015 - 22:31
Today Oracle released new versions of:

Also Oracle REST Data Services 3 got a new EA2 version.You may want to check Kris Rice's blog for new features.
I already blogged about all of the tools before, but not yet about SQLcl.This is a command line tool, I call it "SQL*Plus on steroids" (or as Jeff calls it SQL Developer meets SQL*Plus). It's particularly useful when you're on your server and quickly need to run some queries. Or if you're a command line guy/girl all the time, this tool is for you.
Here's a screenshot how to connect to your database with SQLcl from Linux.

Typing help will show you a list of quick shortcuts.
For example if you type APEX you get a list of your APEX applications

What I really like about SQLcl is that it formats the output so nicely. With SQL*Plus you had to set column widths, page sizes etc. Not with SQLcl, it's smart and formats it nicely.
Next to that you can quickly output your query in JSON format by typing "set sqlformat json":

There're many more features - a good starting point is this presentation and video by Jeff Smith.

APEX 5.0: the way to use Theme Roller

Dimitri Gielis Blog - Mon, 02/23/2015 - 21:55
Once you have your new application created using the Universal Theme it's time to customise your theme with Theme Roller.

Run your application and click the Theme Roller link in the APEX Developer Toolbar:


Theme Roller will open. I won't go in every section, but want to highlight the most important sections in Theme Roller in this post:
  1. Style: there're two styles that come with APEX 5.0: Blue and Gray. You can start from one of those and see how your application changes color. It will set predefined colors for the different parts of the templates.

  2. Color Wheel: when you want to quickly change your colors, an easy way to see different options is by using the Color Wheel. You've two modes: monochroom (2 points) and dual color (3 points - see screenshot). By changing one point it will map the other point to a complementary color. Next you can move the third point to play more with those colors.

  3. Global Colors: if the Color Wheel is not specific enough for what you need, you can start by customising the Global Colors. Those are the main colors of the Universal Theme and used to drive the specific components. You can still customise the different components e.g. the header by clicking further down in the list (see next screenshot).

  4. Containers etc. will allow you to define the specific components. A check icon will say it's the standard color coming with the selected style. An "x" means the color was changed and an "!" means the contrast is probably not great.
Original with styleAfter changing colors

This is just awesome... but what if you don't like the changes you did?

Luckily you can Reset either your entire style or you can refresh the specific section by clicking the refresh icon. There's also an undo and redo button. But that is not all... for power users when you hit "ALT" when hovering a color you can just reset that color! (only that color will get a refresh icon in it and clicking it will reset it)

Note that all changes you're making are locally stored on your computer in your browsers cache (HTML5 local storage), so you don't effect other users by playing with the different colors.

Finally when you are done editing your color scheme you can hit the Save As button to save all colors to a new Style. When you close Theme Roller the style will go back how it was.
The final step to apply the new style so everybody sees that version, is to go to User Interface Details (Shared Components) and set the style to the new one.

Note that this blog post is written based on APEX 5.0 EA3, in the final version of APEX 5.0 (or 5.1) you might apply the new style from Theme Roller directly.

Want to know more about Theme Roller and the Universal Theme - we're doing an APEX 5.0 UI Training May 12th in the Netherlands.

PRCD-1229 An attempt to access configuration of database was rejected

Rick's Blog - Mon, 02/16/2015 - 21:07
While trying to run DBUA on a database the DBUA would not see the database as ‘UP’ even though the database was online and available. The error was indicated while DBUA was using ‘srvctl’ in the background like this: srvctl start database -d GM5203 PRCD-1027 : Failed to retrieve database GM5203 PRCD-1229 : An attempt to access configuration of database GM5203 was rejected because its version 11.2.0.3 differs from the program version 11.2.0.3.0. Instead run the program from /db/oracle/product/11.2.0.3. The issue was that the configuration of crsctl/srvctl had the wrong paramter ‘VERSION’ for the database. Evidence: crsctl stat res ora.GM5203.db -f|grep VERSION VERSION=11.2.0.3 Fix: crsctl modify res ora.GM5203.db -attr "VERSION=11.2.0.3.0" Verification: crsctl stat res ora.GM5203.db -f|grep VERSION VERSION=11.2.0.3.0

APEX 5.0 Fixed Headings for Interactive Reports

Dimitri Gielis Blog - Wed, 02/04/2015 - 23:30
When you click on the Attributes of your Interactive Report there's a neath little new features included called "Heading - Fixed to" with as options: None, Region and Page


We've got this request from many customers and it has always been a pain to get it working in every condition with APEX 4.x, but no hacking anymore - it's there now in APEX 5.0.
When you start to scroll the column header will move up and the content will scroll underneath it.

Here's a quick demo:

APEX 5.0 EA3 - Universal Theme Styles - Gray!

Dimitri Gielis Blog - Wed, 02/04/2015 - 22:17
In APEX 5.0 theme 42 is included - this is the Universal Theme. The theme comes with two styles: Blue and Gray.


The UI is so much improved in APEX 5, it's really a game changer. When you look at the details for example from such a style you will see it's using a Less file (Less is a CSS pre-processor) behind the scenes. So it's very easy to make changes to the CSS. But you probably even don't need to do that as the development team allows you to make customisations with ThemeRoller which generates JSON (another nice example where JSON is used - interested in JSON - I did some other posts on this blog about JSON).

To change the color for example, below you see the JSON for that.


Note that Theme Roller will generate the JSON for you. Accept from those customisations, the blue and gray style are using the exact same css.

From the Blue style many screenshots have been posted already, but I love the Gray style too, here's a screenshot when you switch the sample application's style to Gray.

APEX 5.0 EA3 released with Universal Theme

Dimitri Gielis Blog - Sun, 02/01/2015 - 13:25
Today Oracle released the latest Early Adopter release of APEX 5.0.


The release is packed with new features, but one of the biggest improvements you can find in the UI.

For me the Universal Theme with Theme Roller, Theme Styles and Template Options is the absolute killer feature of APEX 5.0. I can't better describe Universal Theme as how Oracle is describing it:

Universal Theme is an all-new user interface for your applications that has been built from the ground up for Application Express 5. It is a simpler, yet more capable theme that does away with excessive templates and enable effortless customization with Theme Roller, Template Options, and Theme Styles. Universal Theme is the culmination of all of our UI enhancements and aims to empower developers to build modern, responsive, sophisticated applications without requiring expert knowledge of HTML, CSS, or JavaScript.



The issue with APEX 4.x was the build-in themes became out-dated today, so many of us looked at integrating Bootstrap, Foundation or other frameworks. But APEX 5.0 has send us at WARP-speed to a new UI, suddenly out-of-the-box your applications are modern, responsive, flat, ... everything you can expect from a UI today. Many new components are now part of the UI: like the new Carousel and Cards region are some of my favourites.

If you are interested to know everything about the new UI, Roel and I teamed up with a one-day APEX 5.0 UI Training day.

Happy playing with APEX 5.0 EA3 and don't forget to give feedback to the development team, this is the last time you can before APEX 5.0 hits production.

Preparing architecture for APEX 5.0 upgrade

Dimitri Gielis Blog - Fri, 01/30/2015 - 00:10
I doubted to set the title of this post to "Running APEX 4.2 and 5.0 in the same Oracle instance", but decided not to do that, but that is basically what I will do. Before going into details, I'll share my architecture.

In December 2013 I wrote it was time to update your APEX environment and I gave a quick overview of the architecture we're using. I thought it's time to review that post, so below you find how my preferred APEX architecture is today and tomorrow (once APEX 5 is production).


I'm using Apache as a reverse proxy in front of Tomcat. I'm not going in too much detail about which version to take: Apache v2.2 vs 2.4 and Tomcat v7 vs 8. There're many threads on the internet about that and I guess it depends your environment and your personal feeling. I've been using both versions but currently I'm on Apache v2.2 because it comes as a default with RHEL / OEL6 and SELinux is configured out of the box to protect the webserver. And for Tomcat I'm using v8 as that's the basis for the future versions of Tomcat (v9) and when you want to do Websockets for example, v8 has a more improved spec.

A few years ago we had the discussion about mod_plsql vs APEX Listener (now Oracle REST Data Services - ORDS). I think today, it's clear ORDS is the way to go as it gives you much more features and is proven technology.

For APEX I'm always on the latest version as fast as I can, as with every new release there're great improvements and fixes.

And finally the Oracle Database I'm on 12cR1 because I like the pluggable database concept and the other features it brings. I guess most people will go to 12c very soon, as 11.2 premier support ends this month. You can read more about when what is still supported in this doc.

But in this post I want to show you how easy it is to prepare your environment for APEX 5 and to test the upgrade with an architecture as above. I basically want to run APEX 4.2 and 5.0 next to each other. I'll clone my PDB and apply the APEX 5 installation on the new PDB. Next I'll configure ORDS so it knows to which database it needs to point to depending the url I'll call.

Step 1. Clone the PDB 

sqlplus / as sysdba

create pluggable database APEX50_PDB from APEX42_PDB file_name_convert=('/u01/app/oracle/oradata/cdb/APEX42_PDB','/u01/app/oracle/oradata/cdb/APEX50_PDB');


Step 2. Open the PDB and install APEX 5.0

alter pluggable database apex50_pdb open;

So now we have a new database which is a copy of our existing database open and ready to be used. Next we will need to install APEX 5.0. As the time of writing APEX 5.0 is not available yet, but it will probably be - connect to the new PDB and run @apexins... (follow the installation guide of APEX 5.0 once available!)


Step 3. Configure ORDS

With SQL Developer you can configure ORDS and add the connection to the new PDB.
In SQL Developer 4.1, first setup a connection to your ORDS (Tools > Manage REST Data Services Connections > Add Connection). Next open the ORDS Administration window (View > REST Data Services > Administration). Right click on the REST Data Services and Connect to ORDS:


You'll see the current configuration.

In order to connect to the new database we need to add a Database. Right click in Database Settings and add a new database. Before writing it back (the icon with the green up arrow - click the Test Settings button first (the icon with a v) to make sure everything is fine.

Final step is to let ORDS know that if we put in our url /apex50 we want to connect to the new database. You can do that by adding an entry in URL Mapping:


That's it...

Note: sometimes I've issues with adding the database and URL Mapping in SQL Developer, but it's as fast to do it command line too. The doc has a great example which commands to run: https://docs.oracle.com/cd/E37099_01/doc.20/e25066/config.htm#AELIG7191


Step 4. Test

When you navigate to your normal url e.g. http://localhost/ords/f?p=ABC you will see your APEX 4.2 instance, but if you navigate to http://localhost/ords/apex50/f?p=ABC you'll see the APEX 5.0 instance.

You can play a bit more with making it nicer urls or do some redirects in Apache, but I hope you get the idea how to start testing APEX 5.0 while still running APEX 4.2 too.

Announcement: APEX 5.0 UI Training - May 12th

Dimitri Gielis Blog - Tue, 01/27/2015 - 19:27

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!

JSON for APEX Developers (part 2)

Dimitri Gielis Blog - Thu, 01/22/2015 - 23:30
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

JSON for APEX Developers (part 1)

Dimitri Gielis Blog - Thu, 01/22/2015 - 21:30
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 employeesFROM   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.

Pages

Subscribe to Better Logic LLC aggregator