* This blog was last tested on OAC Version 5.9 + ADW 18c * 

 Background

This article walks through the steps to invoke Oracle REST Data Services (ORDS) in Oracle Analytics Cloud (OAC) Data Visualization (DV).

Possible Use Cases:

* Write-back

* Running DML statements (insert, update or delete etc.)

* Passing pre-process dynamic parameters to selects statements or stored procedures

* Passing parameters to a web service

Functionality Covered:

* DV Data Actions

* Apex ORDS RESTful Services

* Referencing DV Environment Variables (a.k.a Session Variables) in Data Sets and Data Actions

The Process:

* The Canvas contains two Data Sets:

Prompt Data Set

Results Data Set

* The Prompt Data Set is used as a method to select desired pre-process parameters.

* A Data Action is ran from the Canvas by the end-user from the Prompt Data Set.

* The Data Action invokes a POST handler that passes the Prompt parameters from the canvas to ORDS.

* ORDS executes a Stored Procedure passing the parameters to the database.

* The Stored Procedure inserts a row into a table that stores the users selection from the Prompt Data Set.

* The Results Data Set is filtered (at the SQL level) based on the selections made from the Prompt Data Set. 

 PL/SQL Setup

Create Prompt table

CREATE TABLE OAC_USER.DV_DOG_PROMPTS (PARM_BREED VARCHAR2(250 BYTE));

 

Insert prompt values into Prompt table

INSERT INTO OAC_USER.DV_DOG_PROMPTS(PARM_BREED) VALUES (‘Labrador Retriever’);
INSERT INTO OAC_USER.DV_DOG_PROMPTS(PARM_BREED) VALUES (‘German Shorthaired Pointer’);
INSERT INTO OAC_USER.DV_DOG_PROMPTS(PARM_BREED) VALUES (‘Cocker Spaniel’);
INSERT INTO OAC_USER.DV_DOG_PROMPTS(PARM_BREED) VALUES (‘Bloodhound’);
INSERT INTO OAC_USER.DV_DOG_PROMPTS(PARM_BREED) VALUES (‘Dachshund’);
INSERT INTO OAC_USER.DV_DOG_PROMPTS(PARM_BREED) VALUES (‘Greyhound’);
INSERT INTO OAC_USER.DV_DOG_PROMPTS(PARM_BREED) VALUES (‘Boxer’);
INSERT INTO OAC_USER.DV_DOG_PROMPTS(PARM_BREED) VALUES (‘Great Dane’);
INSERT INTO OAC_USER.DV_DOG_PROMPTS(PARM_BREED) VALUES (‘Rottweiler’);
INSERT INTO OAC_USER.DV_DOG_PROMPTS(PARM_BREED) VALUES (‘Bull Terrier’);
INSERT INTO OAC_USER.DV_DOG_PROMPTS(PARM_BREED) VALUES (‘Scottish Terrier’);
INSERT INTO OAC_USER.DV_DOG_PROMPTS(PARM_BREED) VALUES (‘West Highland White Terrier’);
INSERT INTO OAC_USER.DV_DOG_PROMPTS(PARM_BREED) VALUES (‘Chihuahua’);
INSERT INTO OAC_USER.DV_DOG_PROMPTS(PARM_BREED) VALUES (‘Pug’);
INSERT INTO OAC_USER.DV_DOG_PROMPTS(PARM_BREED) VALUES (‘Shih Tzu’);
INSERT INTO OAC_USER.DV_DOG_PROMPTS(PARM_BREED) VALUES (‘Bulldog’);
INSERT INTO OAC_USER.DV_DOG_PROMPTS(PARM_BREED) VALUES (‘Dalmatian’);
INSERT INTO OAC_USER.DV_DOG_PROMPTS(PARM_BREED) VALUES (‘Poodle’);
COMMIT; 

 

Test Prompts table

SELECT * FROM OAC_USER.DV_DOG_PROMPTS;

 

 

Create Results table

CREATE TABLE OAC_USER.DV_DOG_FACTS
(BREED VARCHAR2(250 BYTE),
 AKC_BREED_POPULARITY NUMBER);

 

Insert sample data into Results table

INSERT INTO OAC_USER.DV_DOG_FACTS(BREED,AKC_BREED_POPULARITY) VALUES (‘Labrador Retriever’,1);
INSERT INTO OAC_USER.DV_DOG_FACTS(BREED,AKC_BREED_POPULARITY) VALUES (‘German Shorthaired Pointer’,9);
INSERT INTO OAC_USER.DV_DOG_FACTS(BREED,AKC_BREED_POPULARITY) VALUES (‘Cocker Spaniel’,30);
INSERT INTO OAC_USER.DV_DOG_FACTS(BREED,AKC_BREED_POPULARITY) VALUES (‘Bloodhound’,49);
INSERT INTO OAC_USER.DV_DOG_FACTS(BREED,AKC_BREED_POPULARITY) VALUES (‘Dachshund’,12);
INSERT INTO OAC_USER.DV_DOG_FACTS(BREED,AKC_BREED_POPULARITY) VALUES (‘Greyhound’,145);
INSERT INTO OAC_USER.DV_DOG_FACTS(BREED,AKC_BREED_POPULARITY) VALUES (‘Boxer’,11);
INSERT INTO OAC_USER.DV_DOG_FACTS(BREED,AKC_BREED_POPULARITY) VALUES (‘Great Dane’,16);
INSERT INTO OAC_USER.DV_DOG_FACTS(BREED,AKC_BREED_POPULARITY) VALUES (‘Rottweiler’,8);
INSERT INTO OAC_USER.DV_DOG_FACTS(BREED,AKC_BREED_POPULARITY) VALUES (‘Bull Terrier’,62);
INSERT INTO OAC_USER.DV_DOG_FACTS(BREED,AKC_BREED_POPULARITY) VALUES (‘Scottish Terrier’,57);
INSERT INTO OAC_USER.DV_DOG_FACTS(BREED,AKC_BREED_POPULARITY) VALUES (‘West Highland White Terrier’,42);
INSERT INTO OAC_USER.DV_DOG_FACTS(BREED,AKC_BREED_POPULARITY) VALUES (‘Chihuahua’,33);
INSERT INTO OAC_USER.DV_DOG_FACTS(BREED,AKC_BREED_POPULARITY) VALUES (‘Pug’,28);
INSERT INTO OAC_USER.DV_DOG_FACTS(BREED,AKC_BREED_POPULARITY) VALUES (‘Shih Tzu’,20);
INSERT INTO OAC_USER.DV_DOG_FACTS(BREED,AKC_BREED_POPULARITY) VALUES (‘Bulldog’,5);
INSERT INTO OAC_USER.DV_DOG_FACTS(BREED,AKC_BREED_POPULARITY) VALUES (‘Dalmatian’,56);
INSERT INTO OAC_USER.DV_DOG_FACTS(BREED,AKC_BREED_POPULARITY) VALUES (‘Poodle’,7);
COMMIT;

 

Test Results table

SELECT * FROM OAC_USER.DV_DOG_FACTS;

 

 

Create Prompt Selection table:

CREATE TABLE OAC_USER.DV_DOG_SELECTED_PROMPT
(USER_NAME VARCHAR2(250 BYTE),
 SELECTED_BREED VARCHAR2(250 BYTE));

 

Insert sample row into Prompt Selection table

INSERT INTO OAC_USER.DV_DOG_SELECTED_PROMPT(USER_NAME,SELECTED_BREED)
VALUES (‘jay@oracle.com’,’Pug’);
COMMIT;

 

Test Prompt Section table

SELECT * FROM OAC_USER.DV_DOG_SELECTED_PROMPT;

 

Create Prompt Selection Stored Procedure

CREATE OR REPLACE PROCEDURE OAC_USER.SP_DOG_SELECTION (parm1 VARCHAR, parm2 VARCHAR)
IS
BEGIN
DELETE FROM OAC_USER.DV_DOG_SELECTED_PROMPT WHERE USER_NAME = parm1;
INSERT INTO OAC_USER.DV_DOG_SELECTED_PROMPT (USER_NAME, SELECTED_BREED) VALUES (parm1, parm2);
htp.p(parm1 || ‘ Your data is ready for breed ‘ || parm2);
END;

 

Test Stored Procedure

BEGIN
OAC_USER.SP_DOG_SELECTION(‘jay@oracle.com’,’Dalmatian’);
END;

SELECT * FROM OAC_USER.DV_DOG_SELECTED_PROMPT; 

 

 DV Data Set & Canvas Setup

a) Create the data set to display the prompt. Set Data Access = Live.

b) Create the Data Set to display the results from the selection. Note that the SQL reads from the parameter selection table using the USER session variable as a filter. Set Data Access = Live.

SELECT BREED, AKC_BREED_POPULARITY AS POPULAR_RANKING
FROM OAC_USER.DV_DOG_FACTS
WHERE BREED = (SELECT SELECTED_BREED FROM OAC_USER.DV_DOG_SELECTED_PROMPT WHERE USER_NAME = ‘valueof(NQ_SESSION.USER)’)

 

c) Add the Prompt & Results Data Models to the Canvas in table format.

 RESTful Services Setup

a) Log into Apex. Go to SQL Workshop -> RESTful Services

b) Create a New Module

c) Provide Module Name and Base Path

d) Hit Create Module again

e) Click Create Template

f) Prefix the URI Template name with something meaningful.

g) Click Create Template

h) ORDS Handler Definition

Method = POST

Source =

BEGIN
OAC_USER.SP_DOG_SELECTION(:parm1,:parm2);
END;

 

i) Click Create Handler

j) Test the POST in Postman

k) Confirm that the Prompt Section table has been updated.

 DV Data Action Setup

a) From DV Create a Data Action

b) Click on the + sign

c) Data Action

Type = HTTP API

Anchor to = PARM_BREED

HTTP Method = POST

URL = https://apex-1.oraclecloudapps.com/ords/oac_user/get_popularity_ranking/aka_rankings

Parameters =

parm1=${env:”sUserID”}
parm2=${keyValuesForColumn:XSA(‘jay.pearson@oracle.com’.’DV_DOG_PROMPTS’).”Columns”.”PARM_BREED”}
 

d) The UserID environment variable (a.k.a session variable) is being passed to the stored procedure. It may also be desirable to pass other environment variables to the procedure / parameter table such as ProjectPath, ProjectName, CanvasName etc.

e) When entering the keyValuesForColumn, first enter ${, then select keyValuesForColumn.

Then click on column

Available fields can then be clicked to avoid typos.

f) Run the Data Action. Right Click on desired breed -> Click “Get Popularity Ranking”.

g) Wait for the successful message.

h) Refresh the project (top right).

i) Results are updated on canvas and in parameter table.

 Want to Learn More?

Click here for more A-Team Oracle Analytics (OAC) Blogs.

 Summary

This article walked through the steps to invoke Oracle REST Data Services (ORDS) in Oracle Analytics Cloud (OAC) Data Visualization (DV). Possible use cases for this article are write-back, running DML statements, or passing pre-process parameters. Functionality covered included: DV Data Actions, Apex ORDS RESTful Services, and Referencing DV Environment Variables (a.k.a Session Variables) in Data Sets and Data Actions.