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.
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.
The following diagram shows the structure of Web Services within ORDS.
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 have provided us with two APIs for defining, maintaining and protecting Web Services.
The URL for an ORDS Web Service is made up of five elements
https://<host>:<port>/ords/<schema>/<module>/<template>
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.
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;
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.
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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;
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/.
There are several ways we can test our web services.
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.
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.
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
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.
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
The request is submitted and the first five department are returned along with the relevant navigation links.
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/
Open Postman and follow these steps to create a new request entry for the departments GET method request.
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.
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.
| Key | Value |
| department_number | 70 |
| department_name | TECH SERVICES |
| location_name | NEW YORK |
4. Press the Save 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’.
| Key | Value |
| department_number | 70 |
| department_name | TECHNICAL SERVICES |
| location_name | BOSTON |
4. Press the Save 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.
If you now resend the List Departments GET request, you will see there are now seven departments, with 70 being TECHNICAL SETVICES in BOSTON.
| Key | Value |
| department_number | 70 |
4. Press the Save 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’.
Calling the employees GET handler with Postman.
Now press the Send button to call the request and return all of the employees allocated to department 10.
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...
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.
