ORDS-secure restful service using Command line (OAuth 2 : Authorization Code)

I was inspired by others when looking for this subject, but for me in very important to have a blog from a developer prospective and not only to show something which I not have test myself. I use my blog mostly as documentation for myself.

ORDS can be used to define database API’s. You can give third party “controlled” access to the database via REST-API’s. ORDS is a layer above your database, you can use it inside APEX or SQLDeveloper. I prefer using APEX because it has integrated ORDS GUI, which makes it easier developers to define API’s and also to secure it.
BASIC Authentication.

This part of security is not APEX related, it is ORDS related.
In this blog I will show how to define API’s in command line and also how to secure these API’s with OAuth2 method.  I have used Oracle documentation where this is described properly.
I assume your HR schema is enabled and the EMP table is there.
The first thing you need to do is enabling the ORDS for the HR schema.

 p_schema => 'HR',
 p_url_mapping_type => 'BASE_PATH',
 p_url_mapping_pattern => 'hr',
 p_auto_rest_auth => TRUE);

Define a basic webservice we can use for the test.

p_module_name => 'hrmodule',
p_base_path => 'api/hrschema/',
p_pattern => 'emp?{empno}',
p_method => 'GET',
p_source_type => ORDS.source_type_query,
p_source => 'SELECT * FROM emp WHERE empno = :empno',
p_items_per_page => 0);

You can test the service in POSTMAN by using the URL as GET method.


Response will be a JSON

"items": [
   "empno": 7788,
   "ename": "SCOTT",
   "job": "ANALYST",
   "mgr": 7566,
   "hiredate": "1987-04-18T22:00:00Z",
   "sal": 3000,
   "comm": null,
   "deptno": 20

Now we will secure the service, we will create a Role and privilege, them map these together.

– Create a ROLE

 p_role_name => 'hr_role' );

– Create a new privilege called “hr_priv”, which is associated with the role.

l_arr OWA.vc_arr;
l_arr(1) := 'hr_role';
ORDS.define_privilege (
p_privilege_name => 'hr_priv',
p_roles => l_arr,
p_label => 'HR Data',
p_description => 'Allow access to the HR data.');

To protect the web service, we associate the privilege directly to a URL pattern.

p_privilege_name => 'hr_priv',
p_pattern => '/hr/api/emp/*'


OAuth 2: Authorization Code

The authorization code flow is a three-egged process.

  • The user accesses a URL in a browser, which prompts for credentials
  •  The browser is redirected to a specified page with an authorization code as one of the parameters in the URL.
  •  The authorization code is used in a call to generate an access token


Create a client using the grant type of “authorization_code”. The redirect and support URLs are not real

p_name            => 'HR-client',
p_grant_type      => 'authorisation_code',
p_owner           => 'Tester',
p_description     => 'HR management client',
p_redirect_uri    => 'http://localhost:8080/ords/hr/redirect',
p_support_email   => 'mazin@example.com',
p_support_uri     => 'http://localhost:8080/ords/hr/support',
p_privilege_names => 'hr_priv');

You can get the client_id and client_secret

SELECT id, name, client_id, client_secret
FROM   user_ords_clients;

Associate the client with the role that holds the correct privileges for the resources it needs to access.

p_client_name => 'HR-client',
p_role_name   => 'hr_role'

Got to the browser with the below URL, replace endpoint and Client ID with yours.


You will receive the below message, you need to sign in.

You can create a ORDS use and add this to the Role by running this command where orgs.war file is.

java -jar ords.war user api_user hr_role

Login with the hr_user you created before and the approve the access.


After log in you need to approve the access. you will receive the 404 Not Found. this because the redirect page does not exist. From this step we need the URL only.

You need the code from the URL


With the code and Client ID plus the secret ID you can get a token. (You can use POSTMAN as well)

curl -i --user client_id:client_secret --data "grant_type=authorization_code&code=J0GYiIew9ZLMB6HUkjlvrA.." http://localhost:8080/ords/hr/oauth/token

The response will look like


from this point you are done. You can use the refresh token get a new token inside your external application, this after the token is expired within 3600 seconds.

curl -i --user client_id:client_secret--data "grant_type=refresh_token&refresh_token=8wnzi9WZ00CkkZnjzJLkug.." http://localhost:8080/ords/hr/oauth/token

If you have any question, please leave comment..


ORDS secure restful service (with SSL and without SSL)

With thank to Tim Hall from whom I learn a lot.  here is his detailed blog in this regard. https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-authentication#

Rendering PCS with REST service in APEX

Since Cloud has been introduced by Oracle the developers are focusing to migrate their applications and integrate cloud components with each others.

I was involved in an integration POC for a customer. When I was asked about the possibilities to render PCS tasks in Oracle APEX, my answer was directly YES.  Because I knew APEX supports REST and since Oracle Process Cloud Service does it too, there should be no doubt about the possibilities.

In this shot blog I will explain how to do this. showing all running PCS instances in APEX and submit/approve/reject selected one.

Oracle PCS has a complete API available to make this possible.

  • Create you PCS process in cloud
  • Create two services in APEX
  • Create a report page to show all open PCS Tasks
  • Create a modal page to edit and submit the Task


Lets start with APEX part directly.

  • Go to Shared components-> open Web service References
  • Create a new RESR Service

  • Add your info to the page, Method is GET and Basic Authentication is included.

  • On the next page don’t include any paramaters for now.
  • Click Next, now Select JSON as output format
  • You see on this page, you can create but also you can Test your REST service.


  • Now create a report page “Report on Web Service” and select your recently created Web Service Reference

  • Add a collection name to store the JSON response

  • Once you report is created, you will need to load the data properly.
  • Create a button to execute the process
  • Your process definition will look like below image:

  • Your report will look like below image:

  • At this point your done to load the open instances/Taks from PCS
  • You will face two issues if you develop locally.
    • ACL issue for HTTP
      • Create ACL to access the cloud server
    • Authentication ERROR
      • you need to add a new process in APEX but this time you select in Operation
  • Now go to your report source to extract the JSON CLOB to normal report attributes

  • Now if you hit Load data, your data from PCS will be loaded in your interactive report.

In the next part of this blog I will write about submitting page.