Skip to main content


Showing posts from March, 2020

Oracle Apex Basics: Locking access by IP Address

Intro: This series of posts is focused on providing guides for smaller, more specific parts of Oracle Apex, to help users incorporate these features easily into their own applications. I'll be focusing on features that I myself struggled to find reference to when initially starting to use apex, so hopefully these posts help others in search of the same functionality.  Our Objective: Here I will be outlining how you lock access to an application by it's IP address, particularly useful when trying to match the security settings of your other platforms. For instance there's no point in locking Oracle Planning's access by IP whilst keeping metadata from it stored in an Apex application that isn't. Lets Begin: First within our Oracle Cloud console simply head to "Autonomous Data Warehouse" then click on the ADW you wish to IP lock. Then within that go to "More Actions" and select "Access Control List". From

Connecting EPM Cloud Planning to Apex with Rest API: Separating Substitution Variable access by User

Intro: Did you ever want users to only have access to certain substitution variables that apply to their area of the business, without giving them access to all the variables for the whole application ? Apex could work well as a solution to this problem. Using Apex we can restrict the tables that use our Rest API web source modules to only show substitution variables that meet a certain criteria , for instance only pull through those from a certain Plan Type. Our Objective: Here I will be demonstrating how to apply granular security to your Planning application's substitution variables within Apex , it will incorporate a lot from previous blog posts but I will try and point you toward these other posts when necessary. Lets Begin: We start off by creating a simple GET table that pulls through our substitution variables from Planning, I covered this in an older post here . Then we want to allow for POST functionality, so we can edit these variables, details of which ca

Connecting EPM Cloud Planning to Apex with Rest API: POST Substitution Variables Extras

In Part 2 of this post we finished up connecting our Web Source Module, Package and Interactive report, and you should now be able to submit changes straight to your substitution variables in Oracle Planning from Apex. Here I'm just going to go through a few quality of life additions. Lets begin: First off, we want to have Apex refresh the Substitution variable table we've created so we don't need to refresh the page every time we make a change, this is simply achieved by creating a dynamic action that triggers on the closure of a dialog page. To do this create a new dynamic action on the page with our Interactive Report and assign it's event as "Dialog Closed" , selection type "Region" and then select the region that contains the report itself. Then within that create a true "Refresh" action , by choosing the "Refresh" action on the right hand panel, and once again selecting "Region" and the region t

Connecting EPM Cloud Planning to Apex with Rest API: POST Substitution Variables Part 2 of 2

In Part 1 of this post I covered how to setup your web source module to allow you to POST changes to Oracle Planning, now lets build the bridge between that module and our application. Lets Begin: First we head to the "SQL Workshop" and to the "Object Browser" , then from here we want to create a new "Package" as shown below. This package will be the way in which we tell our Apex table how to interact with our web source module. Name the Package something unique and memorable, then insert the below information into the "Specification" and "Body" of your new package, substituting "SUB_VAR_MANAGEMENT" with the name you have given to the package. Or as text: Specification: create or replace package SUB_VAR_MANAGEMENT as procedure add_variable(   p_name varchar2,   p_value varchar2,   p_plantype varchar2 ); end SUB_VAR_MANAGEMENT; Body: create or replace package body SUB_VAR_MANAGEMENT

Connecting EPM Cloud Planning to Apex with Rest API: POST Substitution Variables Part 1 of 2

In my previous post I talked about using Oracle Apex to dynamically pull through substitution Variables from Oracle Planning. Our Objective: Here I will walk you through how you can edit these variables in Apex and have it push these changes straight into Planning, ideal if you need to manage multiple environments and want to keep all the variables in sync. Lets Begin: My Starting point for this walk-through will be the end of the last one as the same steps are required, so if you haven't done so already please visit my previous post on how to create a simple GET table of your substitution variables so we can begin to manage them from Apex too. So having followed my previous post you should now have in your application a report like the one below that displays your application's substitution variables. Your first task in letting you manage these variables from Apex is found back in the "Web Source Modules" page within "Shared Components" . Y