Welcome to the Beyond Blog

John's Blog

This is my blog of various topics, from things that I think might be useful to others, to things that I just find interesting personally. If you have any comments or questions on any of my posts then please do ask - any positive contribution is very much welcomed.

The views expressed in this blog and completely my own and do not reflect those of Beyond Systems Ltd. All content is provided for informational purposes only, and you are solely responsible for how you use this on your own or others systems. We accept no liability for any losses or damages caused.

Using REST to access Oracle APEX Cloud data in R

A while back I wrote a blog post on The Power of R, and I want to extend that a little now by looking at how easy it is to source data from an APEX Cloud instance directly into a R program.

To give a bit of background on this, for the past few weeks I've been looking after 5 baby rabbits that were rescued by our friend who is a vet. We had to weigh them daily, and keep the vets updated with these weights, so I knocked up a very simple APEX application on apex.oracle.com which let me add weights on my mobile.

Rabbit App Reporting  Rabbit App Maintenance

You can take a look at the app here (no login required) and see how they have progressed!

Anyway, this got me thinking... if I have this data in APEX Cloud instance, how difficult can it be to access that from other systems, such as an R script. Well... not difficult at all it would seem.

First of all I created a REST Service using the RESTful Services utility in APEX, which simply returns the weight data in JSON format.


REST Query

Then with a bit of help from Google, I found I could use the httr and jsonlite R packages to retrieve the data into a new data frame. It was a simple as:


Now we can see we have the data (I made the headings a bit more friendly and typed the Weight_Date field), plus the reference URL:
> head(rabs)
     Name Weight Weight_Date                                                               R1
1 Charlie    168  2018-07-28 https://apex.oracle.com/pls/apex/keymej/keymej/weights?limit=500
2 Charlie    179  2018-07-29 https://apex.oracle.com/pls/apex/keymej/keymej/weights?limit=500
3 Charlie    192  2018-07-30 https://apex.oracle.com/pls/apex/keymej/keymej/weights?limit=500
4  Tinker    157  2018-07-29 https://apex.oracle.com/pls/apex/keymej/keymej/weights?limit=500
5  Tinker    190  2018-07-31 https://apex.oracle.com/pls/apex/keymej/keymej/weights?limit=500
6   Tilly    132  2018-07-28 https://apex.oracle.com/pls/apex/keymej/keymej/weights?limit=500

Then using ggplot, I could very quickly produce a graph based on that data.

ggplot(data=rabs, aes(x=rabs$Weight_Date,y=rabs$Weight, col=rabs$Name)) + geom_line() + labs(colour="Name",x="Date", y="Weight (g)")

ggplot Graph

I can also easily see statistics about any individual bunny!

> charlie summary(charlie$Weight)
   Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
  168.0   262.5   358.0   357.5   438.0   550.0 

The great thing about this though of course, is that my data set in R is live - there's no manual downloading, no intermediate spreadsheets, and I could of course have made the data secured by authentication had I wanted to. So whilst APEX is a wonderful tool with great power and flexibility, it's good to know it's equally as easy to integrate it with other tools as well.

Don't forget to keep a watch on the progress of the little bunnies! :)

Bunnies1 Bunnies2

Monitoring ODI Load Plan Executions (II)
Architectural Considerations for Integrating Oracl...


No comments made yet. Be the first to submit a comment
Already Registered? Login Here
Wednesday, 20 November 2019


Request a demo of our products here



Beyond Systems Limited

Suite 1.01 Jactin House,

24 Hood Street, Manchester

M4 6WX United Kingdom



Tel:    +44(0)8450 940 998

Email:  contact@wegobeyond.co.uk