X

Best Practices from Oracle Development's A‑Team

Using Stored Procedures as a Source for Oracle Analytics

Validated March 18, 2021 with OAC 5.9

Introduction

A stored procedure is usually defined as a reusable component of a database system containing business logic applied to database objects. In Oracle databases they are considered a sub-program of and are written in the PL/SQL programming language. PL/SQL is the Oracle procedural extension of SQL. Refer here and here for the Oracle documentation.

Stored procedures are like database views but with additional business logic applied. Unlike views they cannot be referenced in a SQL statement so are not natively candidates for an analytics data set definition.

Integrating stored procedures in OAC is best done using the classic elements of OAC e.g. dashboard prompts, request variables, data-level security etc.

This post, however, is a step-by-step guide for enabling stored procedures as data sets in Oracle Analytics Data Visualization. The examples use stored procedures in an Oracle Autonomous Data Warehouse.

Validations

March 18, 2021 with OAC 5.9 

Topics

Before You Begin

Enabling a Data Set Based on a Stored Procedure

Creating a Data Set Based on a Stored Procedure

Completing a Data Set Based on a Stored Procedure

 Before You Begin and Assumptions 

Acronyms

OAC Oracle Analytics Cloud
SP Stored Procedure
CV Cursor Variable
DB Database
DV Data Visualization
ORDS Oracle REST Data Services
ADW Autonomous Data Warehouse

 

Privileges

ADW privileges for creating a schema and database objects and read access to the embedded SH schema objects used as examples.
OAC privileges for creating data visualization data sets, projects, and data actions
HTTP server privileges for creating RESTful data services if using a SP requiring parameters

Database

The following are required:

A schema other than Admin for hosting the example DB objects (If using a procedure requiring parameters)
A PL/SQL package containing the definition of the SP output type and a CV. Refer 6.4 Cursor Variables for documentation and note the difference between strong and weak CVs.
A stored procedure that returns a CV. 

The following creates a schema used in this post with roles and privileges.

Disconnect and reconnect to ADW as the schema owner i.e. SPWS 

Determine the output row type for a SP with a weak CV by creating a temporary view using the SP's SQL. An example is below:

The following creates a package with a record for the output row columns and a strong cursor type.

Note: Even if your SP uses a weak cursor type define it as strong in the package for the row type to be used in subsequent steps.

 

The following creates the procedures.

 

OAC

An instance with DNS and network connectivity for the ADW hosting the SP

HTTP REST Services

This post uses the ORDS that comes with ADW hosting the SP.

 Enabling a Stored Procedure Data Set 

How you enable a stored procedure data set in DV depends on whether it requires parameters or not.

About SP Parameters

Parameters have many uses in a stored procedure such as filtering, formatting, logging and conditional logic. If a procedure only uses parameters as filters in a where clause then DV can accomplish the same thing using DV filters. In this one case you have a choice of passing the parameters or not.

Steps for a SP with Parameters

The procedure used in this section is the one defined with a strong CV.

DV is currently unable to pass parameters to a stored procedure but can pass them via a HTTP API data action to an endpoint that runs the SP and persists the output in a database table.

Create an ADW Result Set Table

Create a table to store the result set from the procedure. Add a key column to SP's row type enabling multiple instances of procedure output. An example statement is below.

 

Create a HTTP REST Endpoint

Creating a HTTP resource handler for a REST API call to run the SP is outside the scope of this post. It does needs to accept parameters from OAC, execute SQL or PL/SQL to run the procedure with them and have access to ADW for inserting the rows. One method is to use the Oracle REST Data Services that comes with ADW. Refer here for the documentation. And here for another A-team post on setting up a resource handler. This post uses a handler that accepts a POST request and executes an anonymous PL/SQL block containing the SP and embedded bind variables.

Here are the high-level steps using ORDS: 

Navigate to ADW Service Console > Development > Oracle APEX > Administration Services
 As the Admin user create an APEX workspace in the SPWS schema
 Sign out and sign in as SPWS

Navigate to SQL Workshop > RESTful Services
  Click Register Schema with ORDS
    Accept the defaults and click Register Schema with ORDS

Click Modules then Create Module
  Enter a Module Name e.g. dv.rest
  Enter a Base Path e.g. /dv/
  Click Create Module

Click Create Template
  Enter a URI template e.g. sp/
  Click Create Template

Click Create Handler
  Select POST from the Method dropdown
  Enter a Source line e.g. Select 'Just for Testing' from dual 
  Click Create Handler

Validate Initial Connectivity

Use the following CURL command to test DNS resolution of the hostname, network routing and access, and the HTTP listener. You should receive a HTTP/1.1 200 OK response.

Add the Handler Source Code

Replace the initial line with following. This deletes any previous results run with the same key, runs the SP, opens the CV, fetches each row and inserts into the result set table.

Note: This post assumes the SP is using scalar data types for the input parameters.

Validate the Source Code

Run the code in your SQL development environment. You will be prompted for the bind variable values. The following are good values:

:sp_rs_key    123
:channel_id   3
:cust_id         4
:boolean_val TRUE

In the result set table, ensure only the desired channel and customer rows are present and the product name has been shortened to a maximum five characters.

Steps for a SP without Parameters

The procedure used in this section is the one defined with a weak CV. The function uses the strong CV definition to obtain the row type.

A PL/SQL function can be used to run the SP and display the results. This method bypasses the need to persist the SP output.

Define an Object and Table Type

The package defined above has a record type for the SP output row. Define an object type with the same columns and define a table type to contain all rows returned by the procedure.

 

 

Create a Function

Create a function to run the SP and display the results. An example is below. 

 

Validate the Function

Run the code in your SQL development environment.

 

 Creating a Stored Procedure Data Set

Creating a data set involves first creating a connection to ADW with the SP schema credentials.

Create a Connection to ADW

Sign in to OAC and create a connection to ADW using the schema credentials hosting the SP i.e. SPWS

From the DV home page click Create and Data Set.
Choose the ADW Connection just created.

Steps for a SP with Parameters

Select the Schema containing the SP's result table i.e. SPWS
   Select the SP's result Table i.e. SP_RESULT_SET
      Select Live from the Data Access dropdown
      Click Add All from the column list
      Click Add 

Steps for a SP without Parameters

Click Enter SQL
  Enter a Name e.g. SP_FUNCTION_DS
  Select Live from the Data Access dropdown
  Add the following statement and click Add

 

℘ Completing a Stored Procedure Data Set 

Update the data set if necessary to change the aggregation type for numerical columns. From the DV home page: 

Navigate to Data > Data Sets.
   Double-Click on the Data Set to open it in a new project
     For each numerical column you do not want aggregated:
        Change the Treat As property to Attribute
   Exit the project creation dialog and choose Don't Save

 Creation Flow 

 

 Summary 

This post provided a step-by-step guide for enabling stored procedures as data sets in Oracle Analytics Data Visualization. Your stored procedure data set(s) are now ready to be used in DV projects. Refer here for a post on filtering the data sets in Data Visualization.

For other posts relating to analytics and data integration visit http://www.ateam-oracle.com/dayne-carley

 

⁂ Appendix ⁂

Below are scrollable lists of commands used in this post. The client commands may be downloaded here or send them to your clipboard by clicking in the text area and then clicking the copy button.

 

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