ORDS,OAuth2 & Web Services in APEX – Part 1

Colin Archer Oct 30, 2017 10:08:26 AM

Contents

ORDS, OAuth2 & Web Services in APEX – Part 3

ORDS, OAuth2 & Web Services in APEX – Part 2

The Pitfalls of APEX Authentication with OAuth2

I recently gave a presentation on how to get started with Web Services at the UKOUG APEX SIG in London. The first half covered how to create Web Services with ORDS, how to protect them with OAuth2 and how to test them with a REST client. The second half covered how to call Web Services from your APEX applications.

The following video is a recording of the presentation.

 

 

Following on from that presentation, I would like to go into a little more detail on these topics and will be releasing a series of three blogs over the coming weeks coving the following areas.

  1. How to create a simple set of Web Services utilizing the standard Oracle EMP and DEPT tables, and how to test them using a REST client.
  2. How to protect your Web Services using OAUTH2 and how to test them with a REST client.
  3. How to access protected and non-protected Web Services within an APEX application, and how to deal with paginated responses.

Part 1 – Creating and testing ORDS Web Services

This first blog in the series will show you how to create a simple set of Web Services using ORDS and covers all four supported HTTP methods. It will also show you how to test the Web Services using a REST client.

Assumptions

  • You have a functioning installation of ORDS 3.0+.
  • You have a suitable REST client for testing the web services. Simple GET method Web Services can be tested using a browser, however, Web Services that use the HTTP Header or require the use of a REST client such as Advanced, Insomnia, SoapUI, or Postman. The latter being my REST Client of choice.

 

ORDS Structure

The following diagram shows the structure of Web Services within ORDS.

ORDS,OAuth2 & Web Services in APEX

 

Each database schema you enable for ORDS will consist of modules, templates, handlers and parameters. A module can have multiple templates, and a template can have a handler for each supported HTTP method, which are GET, POST, PUT and DELETE.

This demonstration will show you how to create the following Web Services within a new schema called ORDSDEMO.

Template Handler Description
departments GET List all rows form DEPT
departments POST Insert a row into DEPT
departments PUT Update a row in DEPT
departments DELETE Delete a row from DEPT
employees GET List Employees in EMP (optionally for a single Department)

Oracle APIs

Oracle have provided us with two APIs for defining, maintaining and protecting Web Services.

  • ORDS – For developing Web Services
  • OAUTH – For implementing OAUTH authentication

Web Service URL

The URL for an ORDS Web Service is made up of five elements

https://<host>:<port>/ords/<schema>/<module>/<template>

  1. Host – Name of the host
  2. Port – Port Number
  3. Schema – Pattern defined for the schema
  4. Module – Base path for the module
  5. Template – Pattern defined for the template

ORDS Metadata

As you build your APIs, you can use the public views owned by the ORDS_METADATA schema to verify what you have built. The following five views are the primary ones to query.

  • USER_ORDS_SCHEMAS
  • USER_ORDS_MODULES
  • USER_ORDS_TEMPLATES
  • USER_ORDS_HANDLERS
  • USER_ORDS_PARAMETERS

Create a new user for the demo

First log onto SYS (as SYSDBA) and create a new user with the necessary privileges.

CREATE USER ORDSDEMO IDENTIFIED BY ORDSDEMO DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;
GRANT CREATE TABLE TO ORDSDEMO;
GRANT CREATE SESSION TO ORDSDEMO;

Next log on as the newly created user ordsdemo and create a copy of the standard Oracle EMP and DEPT tables with sample data using the following SQL.

CREATE TABLE DEPT(  
  DEPTNO     NUMBER(2,0),  
  DNAME      VARCHAR2(14),  
  LOC        VARCHAR2(13),  
  CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO));

CREATE TABLE EMP(  
  EMPNO    NUMBER(4,0),  
  ENAME    VARCHAR2(10),  
  JOB      VARCHAR2(9),  
  MGR      NUMBER(4,0),  
  HIREDATE DATE,  
  SAL      NUMBER(7,2),  
  COMM     NUMBER(7,2),  
  DEPTNO   NUMBER(2,0),  
  CONSTRAINT PK_EMP PRIMARY KEY (EMPNO),  
  CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO));

INSERT INTO DEPT VALUES(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES(20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES(40,'OPERATIONS','BOSTON');
INSERT INTO DEPT VALUES(50,'PURCHASING','WASHINGTON');
INSERT INTO DEPT VALUES(60,'HR','NEW YORK');
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
  VALUES (7788,'SCOTT','ANALYST',7566,TO_DATE('19-APR-87','DD-MON-RR'),3000,NULL,20);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
  VALUES (7698,'BLAKE','MANAGER',7839,TO_DATE('01-MAY-81','DD-MON-RR'),2850,NULL,30);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
  VALUES (7839,'KING','PRESIDENT',NULL,TO_DATE('17-NOV-81','DD-MON-RR'),5000,NULL,10);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
  VALUES (7566,'JONES','MANAGER',7839,TO_DATE('02-APR-81','DD-MON-RR'),2975,NULL,20);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
  VALUES (7782,'CLARK','MANAGER',7839,TO_DATE('09-JUN-81','DD-MON-RR'),2450,NULL,10);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
  VALUES (7902,'FORD','ANALYST',7566,TO_DATE('03-DEC-81','DD-MON-RR'),3000,NULL,20);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
  VALUES (7876,'ADAMS','CLERK',7788,TO_DATE('23-MAY-87','DD-MON-RR'),1100,NULL,20);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
  VALUES (7499,'ALLEN','SALESMAN',7698,TO_DATE('20-FEB-81','DD-MON-RR'),1600,300,30);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
  VALUES (7900,'JAMES','CLERK',7698,TO_DATE('03-DEC-81','DD-MON-RR'),950,NULL,50);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
  VALUES (7654,'MARTIN','SALESMAN',7698,TO_DATE('28-SEP-81','DD-MON-RR'),1250,1400,30);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
  VALUES (7934,'MILLER','CLERK',7782,TO_DATE('23-JAN-82','DD-MON-RR'),1300,NULL,10);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
  VALUES (7369,'SMITH','CLERK',7902,TO_DATE('17-DEC-80','DD-MON-RR'),800,NULL,60);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
  VALUES (7844,'TURNER','SALESMAN',7698,TO_DATE('08-SEP-81','DD-MON-RR'),1500,0,30);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
  VALUES (7521,'WARD','SALESMAN',7698,TO_DATE('22-FEB-81','DD-MON-RR'),1250,500,40);
COMMIT;

You can use the following SQL to verify the tables have been populated correctly and contain the default set of six departments and fourteen employees.

SELECT ename, dname, job, empno, hiredate, loc
FROM emp, dept
WHERE emp.deptno = dept.deptno
ORDER BY emp.ename;

Enable the ORDS schema

Before creating any Web Services, the relevant database schema needs to be enabled for ORDS. This is done using the ‘ENABLE_SCHEMA’ procedure.

BEGIN
  ords.enable_schema(p_enabled             => TRUE,
                     p_schema              => 'ORDSDEMO',
                     p_url_mapping_type    => 'BASE_PATH',
                     p_url_mapping_pattern => 'api',
                     p_auto_rest_auth      => FALSE);
  COMMIT;
END;

As you can see from this example the schema pattern mapping can be different from the actual schema name. Here the database schema is called ORDSDEMO, but it will be referenced as API within the Web Service URLs.

Define Module

The first step is to define the resource module, which will be named ‘hr.v1’. A Web Service name should always include the version number; this will allow you to publish updated versions of a Web Service that follow the same URL patterns, whilst still maintaining older iterations for backward compatibility.

BEGIN          
  ords.define_module(p_module_name    => 'hr.v1',
                     p_base_path      => 'hr/v1/',
                     p_items_per_page => 10,
                     p_status         => 'PUBLISHED',
                     p_comments       => 'Sample HR Module');             
  COMMIT;
END;

Oracle automatically paginates SQL queries returned in JSON format. The default is 25, this can be overridden (as we have done here), or alternately can be turned off by setting the value to zero.

It is also a good idea to include a comment for the module, as this will assist in self-documenting your web services and are included in the ORDS_METADATA views

Define Template

Next, we need to create the templates for the module.

BEGIN
  ords.define_template(p_module_name => 'hr.v1',
                       p_pattern     => 'departments',
                       p_comments    => 'Departments Resource');
  ords.define_template(p_module_name => 'hr.v1',
                       p_pattern     => 'employees',
                       p_comments    => 'Employees Resource');
  COMMIT;
END;

It is good practice to define a template for each resource. In this example, we are creating two templates, one for the departments and one for the employees. Again, a comment can be included if you wish, which will be included in the ORDS_METADATA views.

Define departments GET Handler

Next, the template handlers need to be defined, this is where we specify the SQL or PL/SQL the Web Service will execute when called. A template can have a handler for each supported HTTP method (GET, POST, PUT or DELETE).

The first handler is for the GET method on the departments template.

BEGIN
  ords.define_handler(p_module_name    => 'hr.v1',
                      p_pattern        => 'departments',
                      p_method         => 'GET',
                      p_source_type    => ords.source_type_query,
                      p_source         => 'SELECT deptno, dname, loc 
                                           FROM dept ORDER BY deptno',
                      p_items_per_page => 5,
                      p_comments       => 'List departments');
  COMMIT;
END;

The source type is set to ‘source_type_query’, which will return the results of the SQL query as a JSON formatted response. To return the response in CSV format you can use the type ‘source_type_csv_query’.

The pagination size for this handler’s response has been overridden to five.

Define departments POST Handler

The second handler is for the POST method on the departments template. This will insert a new record into the DEPT table using the values we pass in for the number, name and location.

BEGIN
  ords.define_handler(p_module_name => 'hr.v1',
                      p_pattern     => 'departments',
                      p_method      => 'POST',
                      p_source_type => ords.source_type_plsql,
                      p_source      => 'BEGIN
                                          INSERT INTO dept
                                            (deptno, dname, loc)
                                          VALUES
                                            (:pn_dept_no, :pv_dept_name, :pv_location);
                                          :pn_status := 200;
                                          :pv_result := ''Department Added'';
                                        EXCEPTION
                                          WHEN OTHERS THEN
                                            :pn_status := 400;
                                            :pv_result := ''Unable to add department: '' 
                                                          || SQLERRM;
                                        END;',
                      p_comments    => 'Create a Department');

END;

In this example, we are executing a simple block of PL/SQL to insert a row and return the status code and a result message. Any errors are captured using the basic exception handling.

In a production quality Web Service, I would recommend calling a package from the handler. This would perform all of the appropriate validation and return the response values.

The status variable will map to the HTTP response header Status code when the parameters are defined. Status 200 is the standard code returned when a request has been successful. Status 400 is typically returned when the request is bad, which in this case is likely to be caused by missing mandatory data or duplicate department numbers.

Define departments PUT Handler

The third handler is for the PUT method on the departments template. This will update an existing row in the DEPT table.

BEGIN
  ords.define_handler(p_module_name => 'hr.v1',
                      p_pattern     => 'departments',
                      p_method      => 'PUT',
                      p_source_type => ords.source_type_plsql,
                      p_source      => 'BEGIN
                                          UPDATE dept
                                          SET dname = :pv_dept_name, loc = :pv_location
                                          WHERE deptno = :pn_dept_no;
                                          IF SQL%ROWCOUNT = 0 THEN
                                            :pn_status := 400;
                                            :pv_result := ''Invalid department number'';
                                          ELSE
                                            :pn_status := 200;
                                            :pv_result := ''Department Updated'';
                                          END IF;
                                        EXCEPTION
                                          WHEN OTHERS THEN
                                            :pn_status := 400;
                                            :pv_result := ''Unable to update department:''
                                                          || SQLERRM;
                                        END;',
                      p_comments    => 'Create a Department');
END;

Again, in this example we are executing a simple update on the DEPT table and setting the values for the status and result parameters accordingly. The exception handler captures any errors and returns a 400 status along with the error message.

Define departments DELETE Handler

The forth handler is for the DELETE method on the departments template. This will delete an existing row from the DEPT table.

BEGIN
  ords.define_handler(p_module_name => 'hr.v1',
                      p_pattern     => 'departments',
                      p_method      => 'DELETE',
                      p_source_type => ords.source_type_plsql,
                      p_source      => 'BEGIN
                                          DELETE FROM dept
                                          WHERE deptno = :pn_dept_no;
                                          IF SQL%ROWCOUNT = 0 THEN
                                            :pn_status := 400;
                                            :pv_result := 'Invalid department number';
                                          ELSE
                                            :pn_status := 200;
                                            :pv_result := 'Department Deleted';
                                          END IF;
                                        EXCEPTION
                                          WHEN OTHERS THEN
                                            :pn_status := 400;
                                            :pv_result := 'Unable to delete department: ' 
                                                          || SQLERRM;  
                                        END;',
                      p_comments    => 'Delete a Department');
  COMMIT;
END;

Again, the handler executes a simple PL/SQL block to delete the specified department, returning a status of 200 if successful and 400 if unsuccessful.

Define employees GET Handler

The last handler is for the employee report. For this one we will add an optional parameter to restrict the results to a single department.

BEGIN
  ords.define_handler(p_module_name => 'hr.v1',
                      p_pattern     => 'employees',
                      p_method      => 'GET',
                      p_source_type => ords.source_type_query,
                      p_source      => 'SELECT d.dname, e.ename, e.job,
                                               e.empno, e.hiredate, d.loc
                                        FROM emp e, dept d
                                        WHERE e.deptno = d.deptno
                                        AND (:pn_deptno IS NULL OR d.deptno = :pn_deptno)
                                        ORDER BY d.dname, e.ename',
                      p_comments    => 'List employees');
  COMMIT;
END; 

The response text will be paginated into batches of ten items, as this was the value set against the template.

Define Parameters

When including parameters within in a handler they must be defined. Each parameter has an external name, which is used when calling the Web Service and an internal bind variable name used by the source SQL.

The access method defines if the Parameter is for input, output or both and can be IN, OUT or INOUT.

The source type identifies if the parameter originates in the URI template or the HTTP Header, and can be URI, RESPONSE or HEADER. All of the IN parameters for the department Web Services are passed in via the HTTP header, while the IN parameter for the employees Web Service is passed in via the URI.

The Parameter type can be STRING, INT, DOUBLE, BOOLEAN, LONG or TIMESTAMP.

The following scripts will define all of the parameters for each handler using them.

Departments POST

BEGIN
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'departments',
                        p_method             => 'POST',
                        p_name               => 'department_number',
                        p_bind_variable_name => 'pn_dept_no',
                        p_source_type        => 'HEADER',
                        p_param_type         => 'INT',
                        p_access_method      => 'IN',
                        p_comments           => 'Department Number');
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'departments',
                        p_method             => 'POST',
                        p_name               => 'department_name',
                        p_bind_variable_name => 'pv_dept_name',
                        p_source_type        => 'HEADER',
                        p_param_type         => 'STRING',
                        p_access_method      => 'IN',
                        p_comments           => 'Department Name');
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'departments',
                        p_method             => 'POST',
                        p_name               => 'location_name',
                        p_bind_variable_name => 'pv_location',
                        p_source_type        => 'HEADER',
                        p_param_type         => 'STRING',
                        p_access_method      => 'IN',
                        p_comments           => 'Location Name');
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'departments',
                        p_method             => 'POST',
                        p_name               => 'X-APEX-STATUS-CODE',
                        p_bind_variable_name => 'pn_status',
                        p_source_type        => 'HEADER',
                        p_param_type         => 'INT',
                        p_access_method      => 'OUT',
                        p_comments           => 'Response status');
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'departments',
                        p_method             => 'POST',
                        p_name               => 'result_message',
                        p_bind_variable_name => 'pv_result',
                        p_source_type        => 'RESPONSE',
                        p_param_type         => 'STRING',
                        p_access_method      => 'OUT',
                        p_comments           => 'Result message');
  COMMIT;
END;

The following scripts will define all of the parameters for each handler using them.

This handler has three IN parameters, which are passed in using the HTTP header. The status code is mapped to the HTTP header bind variable X-APEX-STATUS-CODE, and the result message is passed back into the Response body.

Departments PUT

BEGIN
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'departments',
                        p_method             => 'PUT',
                        p_name               => 'department_number',
                        p_bind_variable_name => 'pn_dept_no',
                        p_source_type        => 'HEADER',
                        p_param_type         => 'INT',
                        p_access_method      => 'IN',
                        p_comments           => 'Department Number');
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'departments',
                        p_method             => 'PUT',
                        p_name               => 'department_name',
                        p_bind_variable_name => 'pv_dept_name',
                        p_source_type        => 'HEADER',
                        p_param_type         => 'STRING',
                        p_access_method      => 'IN',
                        p_comments           => 'Department Name');
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'departments',
                        p_method             => 'PUT',
                        p_name               => 'location_name',
                        p_bind_variable_name => 'pv_location',
                        p_source_type        => 'HEADER',
                        p_param_type         => 'STRING',
                        p_access_method      => 'IN',
                        p_comments           => 'Location Name');
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'departments',
                        p_method             => 'PUT',
                        p_name               => 'X-APEX-STATUS-CODE',
                        p_bind_variable_name => 'pn_status',
                        p_source_type        => 'HEADER',
                        p_param_type         => 'INT',
                        p_access_method      => 'OUT',
                        p_comments           => 'Response status');
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'departments',
                        p_method             => 'PUT',
                        p_name               => 'result_message',
                        p_bind_variable_name => 'pv_result',
                        p_source_type        => 'RESPONSE',
                        p_param_type         => 'STRING',
                        p_access_method      => 'OUT',
                        p_comments           => 'Result message');
  COMMIT;
END;

This handler uses the same IN AND out parameters as the POST method.

Department DELETE

BEGIN
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'departments',
                        p_method             => 'DELETE',
                        p_name               => 'department_number',
                        p_bind_variable_name => 'pn_dept_no',
                        p_source_type        => 'HEADER',
                        p_param_type         => 'INT',
                        p_access_method      => 'IN',
                        p_comments           => 'Department Number');
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'departments',
                        p_method             => 'DELETE',
                        p_name               => 'X-APEX-STATUS-CODE',
                        p_bind_variable_name => 'pn_status',
                        p_source_type        => 'HEADER',
                        p_param_type         => 'INT',
                        p_access_method      => 'OUT',
                        p_comments           => 'Response status');
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'departments',
                        p_method             => 'DELETE',
                        p_name               => 'result_message',
                        p_bind_variable_name => 'pv_result',
                        p_source_type        => 'RESPONSE',
                        p_param_type         => 'STRING',
                        p_access_method      => 'OUT',
                        p_comments           => 'Result message');
  COMMIT;
END;

The handler for deleting a department only requires one IN parameter for the department number, but still returns the status code and the result message in the response body.

Employee GET

The GET method handler for the listing the employees uses a single parameter for the department number, which is passed in via the URI.

BEGIN
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'employees',
                        p_method             => 'GET',
                        p_name               => 'department_number',
                        p_bind_variable_name => 'pn_deptno',
                        p_source_type        => 'URI',
                        p_param_type         => 'INT',
                        p_access_method      => 'IN',
                        p_comments           => 'Restrict employees by department');  
  COMMIT;
END;

Reviewing the Web Services

Now we have created our first set of Web Services we can use the ORDS_METADATA views to query them. The following SQL query will return all of the Web Services defined for the ORDSDEMO schema.

SELECT uom.comments module_desc,
       uot.comments template_desc,
       uoh.comments handler_desc,
       uoh.method,
       uoh.source_type,
       '<host_ref>' || uos.pattern || uom.uri_prefix || uot.uri_template url,
       (SELECT COUNT(id)
        FROM user_ords_parameters
        WHERE handler_id = uoh.id) parameter_count
FROM user_ords_schemas   uos,
     user_ords_modules   uom,
     user_ords_templates uot,
     user_ords_handlers  uoh
WHERE uot.module_id = uom.id
AND uom.schema_id = uos.id
AND uoh.template_id = uot.id
AND uos.parsing_schema = 'ORDSDEMO'
ORDER BY uom.comments, uot.uri_template

The host reference must be set to the relevant name and port for your ORDS configuration. I have created the Web Services in a local copy of Oracle XE running on port 8081 with ORDS 3.11. Therefore, the prefix for my Web Services is http://localhost:8081/ords/.

ORDS,OAuth2 & Web Services in APEX

Testing Web Services

There are several ways we can test our web services.

  • Browser – Mostly limited to testing GET methods
  • CURL – Command line tool
  • REST – Client Best and simplest solution, multiple clients to choose from.

 

Browser

This is one of the simplest methods, but is mostly limited to testing GET methods that do not use HTTP headers or authentication.

To test the departments GET method handler we need to paste http://localhost:8081/ords/api/hr/v1/departments into the browser and press enter. The browser defaults to calling the GET method even though the URL for the POST, PUT and DELETE are the same.

ORDS,OAuth2 & Web Services in APEX

I recommend using a JSON viewer extension when using a browser to test so that the response is formatted. I have used Chrome with the JSONView extension.

As you can see the first five departments have been returned as the items per page value was set to five. Navigation links have be automatically added so you can page through the response. If you click on the next URL link, the Web Service will be called again with the addition of the ‘page’ parameter.

ORDS,OAuth2 & Web Services in APEX

The sixth and final department is returned along with the relevant navigation links.

The employees Web Service has the option to include a URI parameter for the department number. To obtain the list of employees for a department we need to add the parameter and its value on to the end of the URL as follows.

http://localhost:8081/ords/api/hr/v1/employees?department_number=10

ORDS,OAuth2 & Web Services in APEX

All the employees allocated to department 10 are returned. As the number returned is less than the defined number of items per page there is no next link, however there is still a first link.

CURL

Another option is to use the command line tool CURL, which you can download here. Once installed, use the following command to call the departments GET method Web Service.

curl -X GET http://localhost:8081/ords/api/hr/v1/departments

ORDS,OAuth2 & Web Services in APEX

The request is submitted and the first five department are returned along with the relevant navigation links.

REST Client

In my opinion, using a REST client such as Postman is the best option. Postman is simple to use, allows you to save your requests in custom collections, and supports authentication methods such as OAuth2. You can download Postman from https://www.getpostman.com/

Calling the departments GET handler with Postman.

Open Postman and follow these steps to create a new request entry for the departments GET method request.

  1. Click on the + to create a new request entry and ensure the request method is set to GET.
  2. Enter the request URL http://localhost:8081/ords/api/hr/v1/departments
  3. Press the Save button.
  4. Enter ‘List Departments’ for the Request Name.
  5. Press the ‘Create Collection’ link, enter ‘ORDS Demo’ for the name and press the tick icon.
  6. Press the ‘Save to ORDS Demo’ button.

You will now have a new Postman collection called ‘ORDS Demo’ containing a single GET request called ‘List Departments’. Ensure the request is selected and press the send button to submit the request.

ORDS,OAuth2 & Web Services in APEX

The request is submitted and the first five departments are returned along with the relevant navigation links. Postman recognises that the response is in JSON and formats it automatically.

You can also see that a status code of 200 OK has been returned, which tells us that the request was successful.

Now change the URL to http://localhost:8081/ords/api/hr/v1/departmentsX and press send. As the URL is invalid, we get a 404 Not Found response.

ORDS,OAuth2 & Web Services in APEXCalling the departments POST handler with Postman.

  1. Click on the + to create a new request entry and ensure the request method is set to POST.
  2. Enter the request URL http://localhost:8081/ords/api/hr/v1/departments
  3. Click on the Headers tab and add the following keys
Key Value
department_number 70
department_name TECH SERVICES
location_name NEW YORK

4. Press the Save button.

  1. Enter ‘Add Department’ for the Request Name.
  2. Select the collection ‘ORDS Demo’
  3. Press the ‘Save to ORDS Demo’ button.

Now press the Send button to call the request and add a new department. The request will be successful and return a status code of 200 OK, and a JSON response body containing the result message parameter, which will contain the value ‘Department Added’.

ORDS,OAuth2 & Web Services in APEX

Calling the departments PUT handler with Postman.

  1. Click on the + to create a new request entry and ensure the request method is set to PUT.
  2. Enter the request URL http://localhost:8081/ords/api/hr/v1/departments
  3. Click on the Headers tab and add the following keys
Key Value
department_number 70
department_name TECHNICAL SERVICES
location_name BOSTON

4. Press the Save button.

  1. Enter ‘Update Department’ for the Request Name.
  2. Select the collection ‘ORDS Demo’.
  3. Press the ‘Save to ORDS Demo’ button.

Now press the Send button to call the request and update department 70. The request will be successful and return a status code of 200 OK, and a JSON response body containing the result message parameter, which will contain the value ‘Department Updated.

ORDS,OAuth2 & Web Services in APEX

If you now resend the List Departments GET request, you will see there are now seven departments, with 70 being TECHNICAL SETVICES in BOSTON.

Calling the departments DELETE handler with Postman.

  1. Click on the + to create a new request entry and ensure the request method is set to DELETE.
  2. Enter the request URL http://localhost:8081/ords/api/hr/v1/departments
  3. Click on the Headers tab and add the following keys
Key Value
department_number 70

4. Press the Save button.

  1. Enter ‘Delete Department’ for the Request Name.
  2. Select the collection ‘ORDS Demo’.
  3. Press the ‘Save to ORDS Demo’ button.

Now press the Send button to call the request and delete department 70. The request will be successful and return a status code of 200 OK, and a JSON response body containing the result message parameter, which will contain the value ‘Department Deleted’.

ORDS,OAuth2 & Web Services in APEX

Calling the employees GET handler with Postman.

  1. Click on the + to create a new request entry and ensure the request method is set to GET.
  2. Enter the request URL http://localhost:8081/ords/api/hr/v1/employees ?department_number=10
  3. Press the Save button.
  4. Enter ‘List Employees’ for the Request Name.
  5. Select the collection ‘ORDS Demo’.
  6. Press the ‘Save to ORDS Demo’ button.

Now press the Send button to call the request and return all of the employees allocated to department 10.

ORDS,OAuth2 & Web Services in APEX

You will now have a Postman collection containing five requests, which you can rerun at any time.

If you would like to find out more information speak to one of our Oracle APEX experts, get in touch through enquiries@dsp.co.uk or book a meeting...

Book a Meeting

 

Author: Colin Archer 

Job Title: Senior Oracle APEX Development Consultant

Bio: Colin is a Senior Development Consultant at DSP-Explorer with 20 years’ experience of analysis, design, and development of bespoke Oracle applications for a wide variety of business functions. Building on his previous experience of Forms and PL/SQL he is now focusing on developing high quality fit for purpose solutions using APEX.

 

 
OAuth2 ORDS How to RESTful APEX

ORDS, OAuth2 & Web Services in APEX – Part 3

ORDS, OAuth2 & Web Services in APEX – Part 2

The Pitfalls of APEX Authentication with OAuth2
Previous

Oracle Goldengate or Dbvisit Replicate?
Next

Why invest in Exadata