- About
Getting to know us
- Industries
Recent case study:
Oracle EBS Cloud Deployment
Consolidating and Migrating assets into Oracle Cloud Infrastructure.
.png?width=250&name=stonewater-logo%20(1).png)
Most Visited Pages
-
- Resources
DSP-Explorer acquires leading Oracle Applications Managed Services Provider, Claremont, to further extend its data management capabilities.
Resources
- Contact us
ORDS,OAuth2 & Web Services in APEX – Part 1
Contents
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.
- 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.
- How to protect your Web Services using OAUTH2 and how to test them with a REST client.
- 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.
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>
- Host – Name of the host
- Port – Port Number
- Schema – Pattern defined for the schema
- Module – Base path for the module
- 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/.
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.
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.
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
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.
- Click on the + to create a new request entry and ensure the request method is set to GET.
- Enter the request URL http://localhost:8081/ords/api/hr/v1/departments
- Press the Save button.
- Enter ‘List Departments’ for the Request Name.
- Press the ‘Create Collection’ link, enter ‘ORDS Demo’ for the name and press the tick icon.
- 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.
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.
Calling the departments POST handler with Postman.
- Click on the + to create a new request entry and ensure the request method is set to POST.
- Enter the request URL http://localhost:8081/ords/api/hr/v1/departments
- 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.
- Enter ‘Add Department’ for the Request Name.
- Select the collection ‘ORDS Demo’
- 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’.
Calling the departments PUT handler with Postman.
- Click on the + to create a new request entry and ensure the request method is set to PUT.
- Enter the request URL http://localhost:8081/ords/api/hr/v1/departments
- 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.
- Enter ‘Update Department’ for the Request Name.
- Select the collection ‘ORDS Demo’.
- 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.
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.
- Click on the + to create a new request entry and ensure the request method is set to DELETE.
- Enter the request URL http://localhost:8081/ords/api/hr/v1/departments
- Click on the Headers tab and add the following keys
| Key | Value |
| department_number | 70 |
4. Press the Save button.
- Enter ‘Delete Department’ for the Request Name.
- Select the collection ‘ORDS Demo’.
- 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’.
Calling the employees GET handler with Postman.
- Click on the + to create a new request entry and ensure the request method is set to GET.
- Enter the request URL http://localhost:8081/ords/api/hr/v1/employees ?department_number=10
- Press the Save button.
- Enter ‘List Employees’ for the Request Name.
- Select the collection ‘ORDS Demo’.
- 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.
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.