Friday, 14 October 2016

EPM and ORDS – Part 3

In the last part I went through getting up and running with administration using SQL Developer and delving into the “AutoREST” functionality.

In this part I will take it one step further and look at developing REST resources using SQL Developer and touch on protecting access to the resources with security.

In order to develop RESTful services using SQL Developer a user is required with the “SQL Developer” role, the process for creating a user and assigning a role is exactly the same as I went through in the last part for the administrator user.

This can be achieved through command line with the following syntax:

java -jar ords.war user <username> "SQL Developer"

Before you can start developing you need to be able to connect to a REST enabled schema and this can be done by creating a new development connection in SQL Developer.

Add a new development connection.

The username is the user that I have just created with the “SQL Developer” role.

The hostname/port and server path are for the web server hosting ORDS

The schema is an Oracle schema that has been enabled for REST services, I am using the same schema alias I used in the last part which holds the Planning Vision application.

A connection will be required for each REST enabled schema you want to develop REST resources for.

To access the development area in SQL Developer, go to View > REST Data Services > Development

This will open the REST Development pane

Click the connect icon and select the connection.

The password is then then entered for the user with the “SQL Developer” role.

Once connected REST Data Services will be populated with Modules and Privileges.

I am sure it doesn’t mean much at the moment but hopefully it should become clearer as I go through the steps of creating a new REST resource.

To do this we need to create a new module, a module is just a way of grouping REST resources together.

In the following example I am going to create a REST resource to view results from the job information table in the planning vision applications schema.

In the last part I used the same table but with the “AutoREST” functionality, this time I want to add some control over it and create custom SQL to return the results in a desired format

Selecting “New Module” will open the RESTful Services Wizard which guides you through creating a new resource.

The first part of the wizard is to specify the module information.

The module name as I mentioned is just a grouping for the REST resources together.

The “URI Prefix” identifies the resource module, so in my example I have called it “jobinfo” as it will be part of the REST resources which return results from the job information table.

You can see the URI that will be used to access the resource module being displayed as an example, it follows the format of:


I have enabled the “Publish” option so that once the resource has been created it will be available for use.

The Pagination Size is the number of rows to return in JSON format and the default is 25, you may ask well what is the query returns 100 records, well if you use the default then only 25 rows will be returned, this doesn’t mean you can’t access the remaining 75 rows, don’t worry I will go into more detail later.

I am not going to get into “Origins Allowed” but basically you can restrict which hosts can access the REST resource.

The next step is in the wizard to specify the template for the REST resource.

It is possible to have a number of different resources that are part of a module so this step is just to define the URI for the resource.

I have called it “view” as it is going to view the results of the job information table, now we have the full resource URL using the format:


I am not going to cover Priority and Entity Tag as they are not relevant for any of the examples I will be showing, if you wish to find out about these areas then refer to the documentation.

The next step is to define method for the resource template, there are four options available.

GET is typically used to read data which is what I will be doing, POST is used for inserting data, PUT is for updates to data and DELETE for deleting data.

As I using the GET method there are a list of source types available.

Here are the definitions of each one:
  • Collection Query -  Executes a SQL query and transforms the result set into an ORDS Standard JSON representation.

  • Collection Query Item - Executes a SQL query returning one row of data into a ORDS Standard JSON representation.

  • Query - Executes a SQL query and transforms the result set into an ORDS legacy JSON representation.

  • Query One Row -  Executes a SQL query returning one row of data into an ORDS legacy JSON representation.

  • Feed - Executes a SQL query and transforms the results into a JSON Feed representation. Each item in the feed contains a summary of a resource and a hyperlink to a full representation of the resource.

  • PL/SQL - Executes an anonymous PL/SQL block and transforms any OUT or IN/OUT parameters into a JSON representation.

  • Media Resource - Executes a SQL query conforming to a specific format and turns the result set into a binary representation.
There may seem like a lot of types to choose from but the first four are similar and only differ in the result set format and to be honest in EPM terms you will probably be using either one of them or the PL/SQL option, the others I doubt you would ever bother with.

For this example, I am going with the "Collection Query" type.

Depending on the source type selected the results can be returned either in JSON or CSV format.

I am going to stick with JSON for now but later I do have some CSV examples.

You also have the option to set the pagination size again and this will override the setting defined early at the module level.

The final step of the wizard displays a summary of the RESTful service that will be created.

It may seem like a lot to digest but most of the time you will probably use the same set of options and will start to fly through creating a new resource.

Once created the module, resource and method will be shown under Modules in the REST Development pane.

The final stage now is to create the SQL query for the resource and this can be done by selecting open on the resource method.

A SQL worksheet will open where you can define the SQL query.

I created a SQL query against the job info table HSP_JOB_STATUS which formats the results into a more readable format.

Just like with standard SQL Developer functionality the query can be run and the results viewed.

Once happy with the SQL statement and the results the module can validated and uploaded to the ORDS web application.

As the option to publish was enabled earlier in the configuration then after the module has uploaded the resource should be available to access.

To access the resource, I am going to use a free REST client again and it is as simple as entering the URL and firing off a request.

So now we quickly have the rows returned from the SQL query as a http response in JSON format

Putting that into a scripting world like PowerShell couldn’t be easier and with three lines of code we have the results in a nicely formatted table.

For a system administrator this provides quick access to what has been happening with jobs in planning and then can act on the results.

Let us take another example from Planning and one that comes up time and time again, audit records.

You can enable auditing through the planning web interface and select various options to track what has been happening in planning.

The document has always had the following about accessing the audit information:

“View results in the HSP_AUDIT_RECORDS table using a RDBMS report writer.”

Not the most helpful and sometimes can be a bit of pain to provide a process to access to this information.

Well ORDS can help out here and deliver the information in a structured format, once set up you don’t have to worry about it too much.

To demonstrate this, I am going to create a new module using pretty much the same configuration as the last example.

The only difference is the module and resource template name, there is no need for me to repeat with the same screenshots as the summary has all the information you need.

This time I have created a module called “audit” and a resource template “view”, the rest of the configuration is exactly same as before.

Just like earlier the new module, resource and method will be shown under Modules in the REST Development pane

Now to write a simple SQL query against the planning applications audit table.

I am trying to keep this as basic as possible, obviously you can write the SQL to be as complex as you want to match your requirements.

After uploading. the results can be returned in JSON format by issuing a GET request.

The pagination size has been kept as the default value meaning that 25 rows will be returned.

As part of the JSON response there is additional information provided on the current data set.

hasMore” provides us with whether there are more rows of data available.

limit” is the maximum number or rows to return in the result set.

offset” is the starting point of the returned result set.

count” is the number of rows that have been returned.

It is possible to control the paging of the result data using parameters.

So if we need to return rows 25 on wards the “offset” parameter can be used.

The returned “offset” value will reflect the parameter used in the request.

To override the value set for “pagination size” then the “limit” parameter can be set.

Now 100 rows have been returned.

It is possible to use a combination of parameters.

Just like I covered with the “AutoREST” functionality, filter parameters can also be applied to return subsets of data.

This can again easily be put into a script and in the following example I have filtered on data audit records.

To provide an idea of the number of records by type in the audit table I added a new resource template named “count” to the audit module.

The resource has the following SQL assigned and after validating the module it was uploaded again.

With only needing to change the resource URL from “view” to “count” a summary of records in the audit table can be returned.

Pretty quickly you can build up a set or resources that can help make those everyday EPM requests much easier.

Up to now these REST resources can be accessed by anybody that knows the URL which is not ideal so I will go through protecting these with privileges.

I am going to start by creating a new user with one of the available default roles:

RESTful Services - This is the default role associated with a protected RESTful service.

The method to create the user is the same as before and can be achieved from command line.

java -jar ords.war user <username> "RESTful Services"

The next step is to create a new privilege from within the REST Development pane in SQL Development.

This will open the create privilege window.

A name, title and description for the privilege can be entered, the title and description are optional.

I have selected the “RESTful Services” role so any users that have this role assigned will be able to access.

I have selected the “audit” module to be protected, it is possible to protect down to individual resources though.

After applying this privilege will appear under privileges.

The privilege still needs to be uploaded to the ORDS web application before it will become active.

To best demonstrate whether it is working or not is to enter the URL to the resource in a browser.

The resource cannot be accessed due to authorisation being required.

The user credentials can be added as basic authentication to the request header.

To achieve this depends on the method you are using to access the REST resource.

For the REST client I am using there is an authentication tab in the request.

This is then automatically base64 encoded and added to the request header

Adding the authorisation header can be achieved in scripting and the following is an example in PowerShell.

So now we have successfully protected the REST resource from being accessed without authenticated credentials.

I am going to leave the post here for today and in the next and final part I will go through further examples of creating REST resources across EPM products.

Friday, 30 September 2016

EPM and ORDS – Part 2

Moving swiftly on to part 2 in this series about EPM and ORDS, in the last part I gave a quick overview of ORDS and went through the process of getting up and running with it.

In this part I am going to look at administrating ORDS and enabling EPM related Oracle schema objects for REST access.

Now it is possible to do most of the administration and development in ORDS through PL/SQL statements but to make life much easier I am going down GUI route and will be using SQL Developer.

To be able tale advantage of this functionality you must use SQL Developer 4.1 or later.

If you want to administrator ORDS using SQL Developer, you must first configure an administrator user.

There are a number of predefined roles available in ORDS and the ones I will be interacting with are:

Listener Administrator - Users who want to administrate an Oracle REST Data Services instance through Oracle SQL Developer must have this role.

SQL Developer - Users who want to use Oracle SQL Developer to develop RESTful services must have this role.

RESTful Services - This is the default role associated with a protected RESTful service.

So to be able to administrator we need to create a user with the “Listener Administrator” role and this can be achieved by the following command line:

java -jar ords.war user <username> "Listener Administrator"

The credentials will be stored in the ORDS configuration directory.

In SQL Developer a new connection to ORDS can be created with the admin user.

Under Tools there is menu called “REST Data Services” and then “Manage Connections

This will open a manage connections window and a new administration connection can be added.

The username that has just been created and the ORDS web application information is entered.

To access ORDS administration you can go to View > REST Data Services > Administration

Once ORDS Administration opens then right click and select ”Connect

This will open the connection window and the connection that has just been created can be selected.

Enter the administrator user details.

Now ORDS Administration will connect to the web application and be populate with all the configuration details

So now you can manage any of the administrative settings from within SQL Developer, any changes can then be uploaded back to the web application.

Let us move on to the more interesting stuff and look at “AutoREST” which means the automatic enabling of schema objects for REST Access.

The documentation provides the lowdown to what it is all about:

"If Oracle REST Data Services has been installed on the system associated with a database connection, and if the connection is open in SQL Developer, you can use the AutoREST feature to conveniently enable or disable Oracle REST Data Services access for specified tables and views in the schema associated with that database connection. Enabling REST access to a table or view allows it to be accessed through RESTful services.

AutoREST is a quick and easy way to expose database tables as REST resources. You sacrifice some flexibility and customizability to gain ease of effort. AutoRest lets you quickly expose data but (metaphorically) keeps you on a set of guide rails. For example, you cannot customize the output formats or the input formats, or do extra validation."

So basically with AutoREST you can quickly enable REST access for specified tables and views in a schema but you are limited to what you can do, I will be going through manually developing REST resources using SQL Developer in the next part as this provides much more flexibility.

To enable REST Services, open a connection to a schema in SQL, I am going to be using the planning vision application schema as an example.

Right click the connection and select “REST Services” > “Enable REST Services

Enable and the schema and provide a schema alias which is used as part of the REST URL to access the schema objects.

I did not enable “Authorization required” as I will be looking at restricting access in the next part.

A summary is then displayed.

It is possible to view PL/SQL that has been generated and will be executed as all that SQL Developer is really doing is running PL/SQL behind the scenes.

All being well the schema should be REST enabled.

The schema has been enabled but there is not much we can do yet until we enable tables or views.

To enable a table then all you need to do is right click the table name and select “Enable REST Service

In my example I using the planning application repository table “HSP_JOB_STATUS” which hopefully you can guess what type of information it stores.

The setup is pretty much the same to enabling a schema except this time it is an object.

Once again a summary of the configuration is shown.

To understand what is going to be executed you can be view the SQL tab.

Very quickly we have enabled a table so we can start getting somewhere with accessing the REST services.

As I will be accessing REST resources I am going to be using the free REST client called boomerang which is available for chrome but there are many different REST clients available.

To view all the REST enabled tables or views in a schema you can access the REST resource with the following URL pattern:

GET http://<HOST>:<PORT>/ords/<SchemaAlias>/metadata-catalog/

In the last part I configured OHS to proxy requests to ORDS so I can access the REST services over the same host/port as EPM products, this makes is extremely useful if you are providing REST access to a EPM user base as there will be no need to open firewalls and manage additional URLs as it will be the same as the one used for EPM.

The schema alias is the one that was defined earlier.

The response in JSON format provides all the table/views that have been enabled which is only one at the moment, URL links are also included to access the REST resource on each enabled object.

You will notice there are two links for each table, the canonical link retrieves the metadata for the table, the format is:


This returns the following information.

With one request we can find information like primary keys, columns and column types for the table.

Putting this into the scripting world is extremely simple as well, I am going to provide an example using PowerShell just because it is accessible on pretty much any machine running Windows, there is an IDE available and it is easy to work with, you can use REST with most scripting languages so feel free to pick one you are most comfortable with.

So with a few lines of code I can return the table structure and then process it how I want.

To access the records in a table is just as easy and follow the following format:

GET http://<HOST>:<PORT>/ords/<SchemaAlias>/<ObjectAlias>/

Each row in the table in returned as a JSON object and contains the column and data.

This does not require much effort to put into a script and return only the columns we are interested in.

With a few lines of code, I have information about the jobs that have been run in planning, ok I could convert the run status value to a more meaningful description like completed but hopefully you get the idea of simple this is.

To return a table row using the primary key then all that is required is the primary key value to be added to the end of the URL, the format is:


The primary key in the HSP_JOB_STATUS table is the JOB_ID column so an example to return a row by job id would be:

There a large amount of different options available to filter queries and this can be achieved by using the parameter q=FilterObject , where FilterObject  is a JSON object providing the information to filter on.

It is easier to show with an example, let us filter on job name and return rows where the name is “Refresh Database”, the JSON object would be:

{"job_name":"Refresh Database"}

then add this to the URL as a parameter

If I wanted to return all the jobs that have failed so that would be any with a run status value of 3 then I could use the equals operator.

Or using a script

There are far too many filtering objects to go through so please check out the documentation for further details.

I have only touched the surface with what you can do with “AutoREST” and all the examples have been using the GET method, other methods can be used such as POST to insert data, PUT to update data and DELETE to delete data.

Hopefully you have seen the potential and how easy it is to enable a table/view for REST, not forgetting this can also be applied to any of the EPM repositories.

The “AutoREST” functionality is great but can be limited so in the next part I will go through creating REST resources with more flexibility by using custom SQL and PL/SQL, I will also cover restricting access with security.