Groovy and RestAPIs combined together provide a big array of possibilities. I will walk you through in this post how to connect to an external table, read records from it and add new records to it. You can extend this logic to enable Realtime capability to pull or push business critical data.
First thing I need is a database and a table that has RestAPIs enabled against it. I have used Oracle Apex , Oracle Application Express is a web-based software development environment that runs on an Oracle database. For more information please visit https://apex.oracle.com/
In Apex application you need to enable ORDS, Oracle REST Data Services (ORDS) bridges HTTPS and your Oracle Database. A mid-tier Java application, ORDS provides a Database Management REST API, SQL Developer Web, a PL/SQL Gateway, SODA for REST, and the ability to publish RESTful Web Services for interacting with the data and stored procedures in your Oracle Database. This is how it looks when registration is successful
It is time to create your table. Just creating a simple one and naming as RULES.
For this table, click on REST tab and make sure REST Enable Object is set to Yes.
You can see the RESTful URI is visible in this screen and for me, it is https://apex.oracle.com/pls/apex/daylan/rules/ you can visit this page on your browser or connect with a RestAPI client.
Now, from EPM service perspective, we need to create a connection as follows.
You can use this connection and read the table content.
HttpResponse<String> jsonResponse = operation.application.getConnection("rules").get().asString();
if(!(200..299).contains(jsonResponse.status)){
throwVetoException("Error processing RestAPI: $jsonResponse.statusText")
}
Returning response body can be read by JsonContext just as demonstrated in the following example. It prints last time Process Actual Business rule was started.
def ctx = JsonPath.parse(jsonResponse.body)
println((ctx.read('$.items[?(@.rule_name == "Process Actuals")]["start_date"]') as List)[0])
As much as we can read from the external tables, we can also write data back to them. Suppose we want to insert a new row with the current time stamp where we specify name of the rule, and name of the user.
def body = new JSONObject()
.put("RULE_NAME","Process Actuals")
.put("START_DATE",new Date().format("yyyy-MM-dd"))
.put("USER_NAME",operation.user.fullName)
.toString()
HttpResponse jsonResponse = operation.application.getConnection("rules").post("/").body(body).asString();
if(!(200..299).contains(jsonResponse.status)){
throwVetoException("Error processing RestAPI: $jsonResponse.status")
}
This script will insert a new record to the destination table as you can see in the following screen shot.
As demonstrated, reading from external tables or writing to external tables are possible and it can allow your epm application interact with source and target systems in Realtime fashion if needed. In my next post, I will demonstrate pushing edited data (for instance price, unit cost etc.) Realtime to an external table.
