X

Best Practices from Oracle Development's A‑Team

Extracting Data using Oracle Application Express (APEX) via RESTful Webservice

* This blog was last tested on OAC 5.9 + Oracle Application Express 20.2.0.00.20 + ADW 19c by Jay Pearson (Oracle A-Team) *

Introduction

This article documents a method by which RESTful web-services can be created in Oracle Application Express (APEX) and exposed to replicate the SQL from an Oracle Analytics Cloud (OAC) analysis report.

 

Load Data

OAC report has been created from this dataset.

 

Main Article

RESTful services can be created from the SQL Workshop and then the 'RESTful Services' option:

Create Module, Template, and Handler.

For this example, use the URL Prefix 'demo' and create a 'GET' handler which will respond by selecting all the records from the SAMP_REVENUE_DENORM table based on the SQL statement of:

'select * from OAC_USER.SAMP_REVENUE_DENORM', as shown below.

Once created, test the Full URL in a browser to confirm that the JSON formatted data will be returned as expected.

https://apex-server.adb.us-ashburn-1.oraclecloudapps.com/ords/oac_user/revenue/run

This simple example showed how to extract data from a specific table, but it may be necessary to use some more complex SQL and include filters to limit the data returned.

The below analysis filters on Company.

Within OAC a user with Administrator rights, can retrieve the physical SQL.

Using the 'Administration' option, and then 'Manage Sessions' - the SQL that OAC runs can be located and examined.

With a little work, and some testing within the SQL Workshop, the SQL statement can be recreated. In this case the following SQL statement was reversed engineered to mimic the OAC report.

WITH 
SAWITH0 AS (select distinct T9893."UNITS" as c1,
     T9893."REVENUE" as c2,
     T9893."PROD_TYPE" as c3,
     T9893."PROD_NAME" as c4,
     T9893."PROD_LOB" as c5,
     T9893."PROD_BRAND" as c6,
     T9893."PROD_ATTRIBUTE2" as c7,
     T9893."PROD_ATTRIBUTE1" as c8,
     T9893."ORGANIZATION" as c9,
     T9893."ORDER_TYPE" as c10,
     T9893."ORDER_STATUS" as c11,
     T9893."ORDER_NUMBER" as c12,
     T9893."ORDER_DAY_DT" as c13,
     T9893."OFFICE_NAME" as c14,
     T9893."DISCNT_VALUE" as c15,
     T9893."DEPARTMENT" as c16,
     T9893."COMPANY" as c17,
     T9893."BILL_DAY_DT" as c18
from 
     "OAC_USER"."SAMP_REVENUE_DENORM" T9893
where  ( T9893."COMPANY" = 'genmind corp' ) )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5, D1.c6 as c6, D1.c7 as c7, D1.c8 as c8, D1.c9 as c9, D1.c10 as c10, D1.c11 as c11, D1.c12 as c12, D1.c13 as c13, D1.c14 as c14, D1.c15 as c15, D1.c16 as c16, D1.c17 as c17, D1.c18 as c18, D1.c19 as c19 from ( select 0 as c1,
     D1.c18 as c2,
     D1.c17 as c3,
     D1.c16 as c4,
     D1.c15 as c5,
     D1.c14 as c6,
     D1.c13 as c7,
     D1.c12 as c8,
     D1.c11 as c9,
     D1.c10 as c10,
     D1.c9 as c11,
     D1.c8 as c12,
     D1.c7 as c13,
     D1.c6 as c14,
     D1.c5 as c15,
     D1.c4 as c16,
     D1.c3 as c17,
     D1.c2 as c18,
     D1.c1 as c19
from 
     SAWITH0 D1
order by c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19 ) D1 where rownum <= 125001

 

This SQL can then be used to create a new RESTful 'GET' method.

This time a parameter for 'company' will be created, which is included as a filter in the select statement.

This parameter can be passed in the URL of the RESTful service.

Multiple parameters can be created in this manner to filter the dataset further and minimize the data returned.

 

Summary

This article demonstrated how the Apex RESTful services can be used to replicate OAC reports and allow data to be extracted from the reporting database via a web-service call.

 

Further Reading

https://cloud.oracle.com/_downloads/WhitePaper_Database_7/RESTful+Web+Services+for+the+Oracle+Database+Cloud+Service.pdf

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha