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:
http://<HOST>:<PORT>/ords/<SchemaAlias>/metadata-catalog/<ObjectAlias>
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:
http://<HOST>:<PORT>/ords/<SchemaAlias>/<ObjectAlias>/<KeyValues>
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.
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:
http://<HOST>:<PORT>/ords/<SchemaAlias>/metadata-catalog/<ObjectAlias>
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:
http://<HOST>:<PORT>/ords/<SchemaAlias>/<ObjectAlias>/<KeyValues>
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.
