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
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.
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.
Then with a bit of help from Google, I found I could use the
jsonlite R packages to retrieve the data into a new data frame. It was a simple as:
require("httr") require("jsonlite") rest_call 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.require("ggplot2") ggplot(data=rabs, aes(x=rabs$Weight_Date,y=rabs$Weight, col=rabs$Name)) + geom_line() + labs(colour="Name",x="Date", y="Weight (g)")
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.