8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL
This article gives an overview of using Oracle REST Data Services to developing RESTful web services using the PL/SQL API provided by the ORDS package.
- Assumptions and Comments
- Create a Test Database User
- Enable ORDS
- Create GET Web Services (Read/SELECT)
- Create POST Web Services (Create/INSERT)
- Create PUT Web Services (Amend/UPDATE)
- Create DELETE Web Services (Remove/DELETE)
- Complete Example
Related articles.
- Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL
- Oracle REST Data Services (ORDS) : All Articles
- Oracle REST Data Services (ORDS) : Installation on Tomcat
- Oracle REST Data Services (ORDS) : Configure Multiple Databases
- Oracle REST Data Services (ORDS) : RESTful Web Services Handling Complex JSON Payloads
- Oracle REST Data Services (ORDS) : AutoREST
- Oracle REST Data Services (ORDS) : Authentication
Assumptions and Comments
This article assumes the following.
- You already have a functioning installation of ORDS.
- The paths for the ORDS configuration match those from the ORDS installation article listed above.
- You have an Oracle database available. In this article I will be using a 12c database, but it works just the same with an 11g database.
- You have a way to call the web services. Some GET web services can be called from a browser, but the other methods require some coding, or a REST client. I used "curl" and the "Advanced REST client" extension for Chrome.
The examples in this article are based around single operations against a single table. I don't consider these "realistic" tests by any means. From a performance perspective, web services that perform DML should call transactional APIs, which may perform multiple operations in a single call, rather than effectively exposing table APIs, like we are here.
Create a Test Database User
We need a new database user for our testing.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER=pdb1; DROP USER testuser1 CASCADE; CREATE USER testuser1 IDENTIFIED BY testuser1 DEFAULT TABLESPACE users QUOTA UNLIMITED ON users; GRANT CREATE SESSION, CREATE TABLE, CREATE TYPE TO testuser1;
Create and populate a copy of the EMP table.
CONN testuser1/testuser1@pdb1
CREATE TABLE EMP (
EMPNO NUMBER(4,0),
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0),
CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
);
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,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 (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30);
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 (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 (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 (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 (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 (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 (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 (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 (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30);
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 (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);
commit;
Enable ORDS
We are going to assume ORDS is installed and is available from the following base URL.
http://localhost:8080/ords/
We have to enable Oracle REST data services for the test schema. We could use any unique and legal URL mapping pattern for the schema, so it is not necessary to expose the schema name. In this example we used "hr".
CONN testuser1/testuser1@pdb1
BEGIN
ORDS.enable_schema(
p_enabled => TRUE,
p_schema => 'TESTUSER1',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'hr',
p_auto_rest_auth => FALSE
);
COMMIT;
END;
/
SELECT parsing_schema,
pattern
FROM user_ords_schemas;
PARSING_SCHEMA PATTERN
----------------- -----------
TESTUSER1 hr
SQL>
Web services from the schema can now be referenced using the following base URL.
http://localhost:8080/ords/hr/
We are now ready to start.
Create GET Web Services (Read/SELECT)
This section describes the creation of REST web services using the GET method, which are typically used to read data. To stay true to REST, you should not perform any changes to data with a GET operation. Think of this as query-only. It breaks down the process to give you some indication of the pieces that make up an ORDS RESTful web service. The sections for the other methods, will not be broken down in the same way, but the information in this section is applicable to them also.
Quick Build
The DEFINE_SERVICE procedure allows you to create a new module, template and handler in a single step. If the module already exists, it's replaced by the new definition.
CONN testuser1/testuser1@pdb1
BEGIN
ORDS.define_service(
p_module_name => 'rest-v1',
p_base_path => 'rest-v1/',
p_pattern => 'employees/',
p_method => 'GET',
p_source_type => ORDS.source_type_collection_feed,
p_source => 'SELECT * FROM emp',
p_items_per_page => 0);
COMMIT;
END;
/
The basic makeup of an ORDS RESTful web service is as follows.
- Module : A container for one or more templates, with an associated path (rest-v1/).
- Template : A container for one or more handlers. The template must be unique within the module and is associated with a specific path (employees/), which may or may not include parameters.
- Handler : A link to the actual work that is done. Typical handler methods include GET, POST, PUT, DELETE, which are passed in the HTTP header, rather than the URL. Each handler is associated with a specific source (or action), which can be of several types.
We can see this pattern in the URL to access this web service. Remember, the default method for a browser URL is GET, so the final URL listed will work in a browser.
Base ORDS URL : http://localhost:8080/ords/ Schema (alias): http://localhost:8080/ords/hr/ Module : http://localhost:8080/ords/hr/rest-v1/ Template : http://localhost:8080/ords/hr/rest-v1/employees/
Views
We can see the definition in the database using the USER_ORDS_% views.
-- Modules
COLUMN name FORMAT A20
COLUMN uri_prefix FORMAT A20
SELECT id, name, uri_prefix
FROM user_ords_modules
ORDER BY name;
ID NAME URI_PREFIX
---------- -------------------- --------------------
10006 rest-v1 /rest-v1/
SQL>
-- Templates
COLUMN uri_template FORMAT A20
SELECT id, module_id, uri_template
FROM user_ords_templates
ORDER BY module_id;
ID MODULE_ID URI_TEMPLATE
---------- ---------- --------------------
10007 10006 employees/
SQL>
-- Handlers.
COLUMN source_type FORMAT A15
COLUMN source FORMAT A20
SELECT id, template_id, source_type, method, source
FROM user_ords_handlers
ORDER BY id;
ID TEMPLATE_ID SOURCE_TYPE METHOD SOURCE
---------- ----------- --------------- ---------- --------------------
10008 10007 json/collection GET SELECT * FROM emp
SQL>
Manual Build
Rather than using the DEFINE_SERVICE procedure, we can build the same web service manually using the DEFINE_MODULE, DEFINE_TEMPLATE and DEFINE_HANDLER procedures. The following code creates a similar web service to that defined previously, but this time defining all the pieces manually.
CONN testuser1/testuser1@pdb1
BEGIN
ORDS.define_module(
p_module_name => 'rest-v2',
p_base_path => 'rest-v2/',
p_items_per_page => 0);
ORDS.define_template(
p_module_name => 'rest-v2',
p_pattern => 'employees/');
ORDS.define_handler(
p_module_name => 'rest-v2',
p_pattern => 'employees/',
p_method => 'GET',
p_source_type => ORDS.source_type_collection_feed,
p_source => 'SELECT * FROM emp',
p_items_per_page => 0);
COMMIT;
END;
/
The web service is available using the following URL.
http://localhost:8080/ords/hr/rest-v2/employees/
Multiple Templates
The following code creates a web service with two templates, one of which uses a parameter to return a single record. Notice the parameter is used in the associated query to limit the results returned.
CONN testuser1/testuser1@pdb1
BEGIN
ORDS.define_module(
p_module_name => 'rest-v3',
p_base_path => 'rest-v3/',
p_items_per_page => 0);
ORDS.define_template(
p_module_name => 'rest-v3',
p_pattern => 'employees/');
ORDS.define_handler(
p_module_name => 'rest-v3',
p_pattern => 'employees/',
p_method => 'GET',
p_source_type => ORDS.source_type_collection_feed,
p_source => 'SELECT * FROM emp',
p_items_per_page => 0);
ORDS.define_template(
p_module_name => 'rest-v3',
p_pattern => 'employees/:empno');
ORDS.define_handler(
p_module_name => 'rest-v3',
p_pattern => 'employees/:empno',
p_method => 'GET',
p_source_type => ORDS.source_type_collection_feed,
p_source => 'SELECT * FROM emp WHERE empno = :empno',
p_items_per_page => 0);
COMMIT;
END;
/
The web service is available using the following URLs.
http://localhost:8080/ords/hr/rest-v3/employees/ http://localhost:8080/ords/hr/rest-v3/employees/7499
Multiple Parameters
There are a number of ways to pass multiple parameters to a get services. The following code creates a web service to return a result set. Notice the parameters are not specified in the template, but are specified in the handler.
CONN testuser1/testuser1@pdb1
BEGIN
ORDS.define_module(
p_module_name => 'rest-v3b',
p_base_path => 'rest-v3b/',
p_items_per_page => 0);
ORDS.define_template(
p_module_name => 'rest-v3b',
p_pattern => 'employees/');
ORDS.define_handler(
p_module_name => 'rest-v3b',
p_pattern => 'employees/',
p_method => 'GET',
p_source_type => ORDS.source_type_collection_feed,
p_source => 'SELECT * FROM emp WHERE empno BETWEEN :empno_start AND :empno_end',
p_items_per_page => 0);
COMMIT;
END;
/
The web service is available using the following URL.
http://localhost:8080/ords/hr/rest-v3b/employees/?empno_start=7876&empno_end=7934
The following code recreates the previous web service to return a result set, but the parameters are specified in the template, so they are mandatory and the position is fixed.
CONN testuser1/testuser1@pdb1
BEGIN
ORDS.define_module(
p_module_name => 'rest-v3b',
p_base_path => 'rest-v3b/',
p_items_per_page => 0);
ORDS.define_template(
p_module_name => 'rest-v3b',
p_pattern => 'employees/:empno_start/:empno_end');
ORDS.define_handler(
p_module_name => 'rest-v3b',
p_pattern => 'employees/:empno_start/:empno_end',
p_method => 'GET',
p_source_type => ORDS.source_type_collection_feed,
p_source => 'SELECT * FROM emp WHERE empno BETWEEN :empno_start AND :empno_end',
p_items_per_page => 0);
COMMIT;
END;
/
The web service is available using the following URL.
http://localhost:8080/ords/hr/rest-v3b/employees/7876/7934
Multi-Value Parameters
In some situations need the web service to deal with multiple values of the same parameter. One way you can do this is by passing a comma-separated list of values as a single parameter value. The web service will have to be coded to deal with this. The following example creates a service that handles a list of employee numbers.
There are several ways to deal with dynamic IN-lists, described here, but we will use the following pipelined table function to make the list appear as rows.
CREATE OR REPLACE TYPE t_in_list_tab AS TABLE OF VARCHAR2 (4000);
/
CREATE OR REPLACE FUNCTION in_list (p_in_list IN VARCHAR2)
RETURN t_in_list_tab PIPELINED
AS
l_text VARCHAR2(32767) := p_in_list || ',';
l_idx NUMBER;
BEGIN
LOOP
l_idx := INSTR(l_text, ',');
EXIT WHEN NVL(l_idx, 0) = 0;
PIPE ROW (TRIM(SUBSTR(l_text, 1, l_idx - 1)));
l_text := SUBSTR(l_text, l_idx + 1);
END LOOP;
RETURN;
END;
/
We incorporate this pipelined table function into a query to handle multiple employee number values.
CONN testuser1/testuser1@pdb1
BEGIN
ORDS.define_module(
p_module_name => 'rest-v3c',
p_base_path => 'rest-v3c/',
p_items_per_page => 0);
ORDS.define_template(
p_module_name => 'rest-v3c',
p_pattern => 'employees/:empno');
ORDS.define_handler(
p_module_name => 'rest-v3c',
p_pattern => 'employees/:empno',
p_method => 'GET',
p_source_type => ORDS.source_type_collection_feed,
p_source => 'SELECT *
FROM emp
WHERE empno IN (SELECT * FROM TABLE(in_list(:empno)))
ORDER BY ename',
p_items_per_page => 0);
COMMIT;
END;
/
The web service is available using the following URL.
http://localhost:8080/ords/hr/rest-v3c/employees/7876,7934,7782
Stored Procedure (JSON)
The previous examples use handlers associated with queries, but they can be associated with stored procedures. I would suggest this is the best approach to REST enabling the database, as you can hide relational complexity behind a PL/SQL API. To show this, create the following procedure, which uses the APEX_JSON package to generate the JSON results.
CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;
GRANT CREATE PROCEDURE TO testuser1;
CONN testuser1/testuser1@pdb1
CREATE OR REPLACE PROCEDURE get_emp_json (p_empno IN emp.empno%TYPE DEFAULT NULL) AS
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR
SELECT e.empno AS "empno",
e.ename AS "employee_name",
e.job AS "job",
e.mgr AS "mgr",
TO_CHAR(e.hiredate,'YYYY-MM-DD') AS "hiredate",
e.sal AS "sal",
e.comm AS "comm",
e.deptno AS "deptno"
FROM emp e
WHERE e.empno = DECODE(p_empno, NULL, e.empno, p_empno);
APEX_JSON.open_object;
APEX_JSON.write('employees', l_cursor);
APEX_JSON.close_object;
END;
/
The following code creates a web service which calls this procedure. Notice the source type of SOURCE_TYPE_PLSQL and a PL/SQL block as the source.
CONN testuser1/testuser1@pdb1
BEGIN
ORDS.define_module(
p_module_name => 'rest-v4',
p_base_path => 'rest-v4/',
p_items_per_page => 0);
ORDS.define_template(
p_module_name => 'rest-v4',
p_pattern => 'employees/');
ORDS.define_handler(
p_module_name => 'rest-v4',
p_pattern => 'employees/',
p_method => 'GET',
p_source_type => ORDS.source_type_plsql,
p_source => 'BEGIN get_emp_json; END;',
p_items_per_page => 0);
ORDS.define_template(
p_module_name => 'rest-v4',
p_pattern => 'employees/:empno');
ORDS.define_handler(
p_module_name => 'rest-v4',
p_pattern => 'employees/:empno',
p_method => 'GET',
p_source_type => ORDS.source_type_plsql,
p_source => 'BEGIN get_emp_json(:empno); END;',
p_items_per_page => 0);
COMMIT;
END;
/
The web service is available using the following URLs.
http://localhost:8080/ords/hr/rest-v4/employees/ http://localhost:8080/ords/hr/rest-v4/employees/7499
Stored Procedure (XML)
Using a stored procedure is a simple way to produce XML REST web services. To show this, create the following procedure, which uses SQL/XML to generate the XML results.
CONN testuser1/testuser1@pdb1
CREATE OR REPLACE PROCEDURE get_emp_xml (p_empno IN emp.empno%TYPE DEFAULT NULL) AS
l_clob CLOB;
BEGIN
SELECT XMLELEMENT("employees",
XMLAGG(
XMLELEMENT("emp",
XMLFOREST(e.empno AS "empno",
e.ename AS "employee_name",
e.job AS "job",
e.mgr AS "mgr",
TO_CHAR(e.hiredate,'YYYY-MM-DD') AS "hiredate",
e.sal AS "sal",
e.comm AS "comm",
e.deptno AS "deptno"
)
)
)
).getClobVal()
INTO l_clob
FROM emp e
WHERE e.empno = DECODE(p_empno, NULL, e.empno, p_empno);
OWA_UTIL.mime_header('text/xml');
HTP.print(l_clob);
END;
/
The following code creates a web service that calls this procedure.
CONN testuser1/testuser1@pdb1
BEGIN
ORDS.define_module(
p_module_name => 'rest-v5',
p_base_path => 'rest-v5/',
p_items_per_page => 0);
ORDS.define_template(
p_module_name => 'rest-v5',
p_pattern => 'employees/');
ORDS.define_handler(
p_module_name => 'rest-v5',
p_pattern => 'employees/',
p_method => 'GET',
p_source_type => ORDS.source_type_plsql,
p_source => 'BEGIN get_emp_xml; END;',
p_items_per_page => 0);
ORDS.define_template(
p_module_name => 'rest-v5',
p_pattern => 'employees/:empno');
ORDS.define_handler(
p_module_name => 'rest-v5',
p_pattern => 'employees/:empno',
p_method => 'GET',
p_source_type => ORDS.source_type_plsql,
p_source => 'BEGIN get_emp_xml(:empno); END;',
p_items_per_page => 0);
COMMIT;
END;
/
The web service is available using the following URLs.
http://localhost:8080/ords/hr/rest-v5/employees/ http://localhost:8080/ords/hr/rest-v5/employees/7499
Create POST Web Services (Create/INSERT)
The POST method is typically used for a create operation, like inserting some data. Remember, from a database perspective, a create operation, like creating an employee, may involve multiple operations, not just inserts. The POST method expects the parameter values to be passed as a payload. I used the "Advanced REST client" extension for Chrome to send the requests here.
The source of the POST handler can be a regular PL/SQL block containing an insert statement, but it makes sense to enclose this in a stored procedure or packaged procedure. Create a stored procedure to insert rows.
CONN testuser1/testuser1@pdb1
CREATE OR REPLACE PROCEDURE create_employee (
p_empno IN emp.empno%TYPE,
p_ename IN emp.ename%TYPE,
p_job IN emp.job%TYPE,
p_mgr IN emp.mgr%TYPE,
p_hiredate IN VARCHAR2,
p_sal IN emp.sal%TYPE,
p_comm IN emp.comm%TYPE,
p_deptno IN emp.deptno%TYPE
)
AS
BEGIN
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (p_empno, p_ename, p_job, p_mgr, TO_DATE(p_hiredate, 'YYYY-MM-DD'), p_sal, p_comm, p_deptno);
EXCEPTION
WHEN OTHERS THEN
HTP.print(SQLERRM);
END;
/
The following code creates a web service with a POST handler that calls the stored procedure, passing the parameters from the payload.
CONN testuser1/testuser1@pdb1
BEGIN
ORDS.define_module(
p_module_name => 'rest-v6',
p_base_path => 'rest-v6/',
p_items_per_page => 0);
ORDS.define_template(
p_module_name => 'rest-v6',
p_pattern => 'employees/');
ORDS.define_handler(
p_module_name => 'rest-v6',
p_pattern => 'employees/',
p_method => 'POST',
p_source_type => ORDS.source_type_plsql,
p_source => 'BEGIN
create_employee(p_empno => :empno,
p_ename => :ename,
p_job => :job,
p_mgr => :mgr,
p_hiredate => :hiredate,
p_sal => :sal,
p_comm => :comm,
p_deptno => :deptno);
END;',
p_items_per_page => 0);
COMMIT;
END;
/
The web service can be called using the following URL, method, header and payload.
URL : http://localhost:8080/ords/hr/rest-v6/employees/
Method : POST
Header : Content-Type: application/json
Raw Payload: { "empno": 9999, "ename": "HALL", "job": "ANALYST", "mgr": 7782, "hiredate": "2016-01-01", "sal": 1000, "comm": null, "deptno": 10 }
$ curl -i -X POST --data-binary @/tmp/insert-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/rest-v6/employees/
You can see a more complex example here.
Create PUT Web Services (Amend/UPDATE)
The PUT method is typically used to amend data and expects the parameter values to be passed in the payload. The PUT operation is often expected to perform a create if the data doesn't already exist, but I personally don't like this approach.
The source of the PUT handler can be as simple as a regular PL/SQL block containing an update statement, but it makes sense to enclose this in a stored procedure or packaged procedure. Create a stored procedure to update rows.
CONN testuser1/testuser1@pdb1
CREATE OR REPLACE PROCEDURE amend_employee (
p_empno IN emp.empno%TYPE,
p_ename IN emp.ename%TYPE,
p_job IN emp.job%TYPE,
p_mgr IN emp.mgr%TYPE,
p_hiredate IN VARCHAR2,
p_sal IN emp.sal%TYPE,
p_comm IN emp.comm%TYPE,
p_deptno IN emp.deptno%TYPE
)
AS
BEGIN
UPDATE emp
SET ename = p_ename,
job = p_job,
mgr = p_mgr,
hiredate = TO_DATE(p_hiredate, 'YYYY-MM-DD'),
sal = p_sal,
comm = p_comm,
deptno = p_deptno
WHERE empno = p_empno;
EXCEPTION
WHEN OTHERS THEN
HTP.print(SQLERRM);
END;
/
The following code creates a web service with a PUT handler that calls the stored procedure, passing the parameters from the payload.
CONN testuser1/testuser1@pdb1
BEGIN
ORDS.define_module(
p_module_name => 'rest-v7',
p_base_path => 'rest-v7/',
p_items_per_page => 0);
ORDS.define_template(
p_module_name => 'rest-v7',
p_pattern => 'employees/');
ORDS.define_handler(
p_module_name => 'rest-v7',
p_pattern => 'employees/',
p_method => 'PUT',
p_source_type => ORDS.source_type_plsql,
p_source => 'BEGIN
amend_employee(p_empno => :empno,
p_ename => :ename,
p_job => :job,
p_mgr => :mgr,
p_hiredate => :hiredate,
p_sal => :sal,
p_comm => :comm,
p_deptno => :deptno);
END;',
p_items_per_page => 0);
COMMIT;
END;
/
The web service can be called using the following URL, method, header and payload.
URL : http://localhost:8080/ords/hr/rest-v7/employees/
Method : PUT
Header : Content-Type: application/json
Raw Payload: { "empno": 9999, "ename": "WOOD", "job": "ANALYST", "mgr": 7782, "hiredate": "2016-01-01", "sal": 1000, "comm": null, "deptno": 20 }
$ curl -i -X PUT --data-binary @/tmp/update-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/rest-v7/employees/
Create DELETE Web Services (Remove/DELETE)
Like the POST and PUT methods, the source of the DELETE handler can be a as simple as a PL/SQL block containing a delete statement, but it makes sense to enclose this in a stored procedure or packaged procedure. Create a stored procedure to delete rows.
CONN testuser1/testuser1@pdb1
CREATE OR REPLACE PROCEDURE remove_employee (
p_empno IN emp.empno%TYPE
)
AS
BEGIN
DELETE FROM emp WHERE empno = p_empno;
EXCEPTION
WHEN OTHERS THEN
HTP.print(SQLERRM);
END;
/
The following code creates a web service with a DELETE handler that calls the stored procedure, passing the parameter from the payload.
CONN testuser1/testuser1@pdb1
BEGIN
ORDS.define_module(
p_module_name => 'rest-v8',
p_base_path => 'rest-v8/',
p_items_per_page => 0);
ORDS.define_template(
p_module_name => 'rest-v8',
p_pattern => 'employees/');
ORDS.define_handler(
p_module_name => 'rest-v8',
p_pattern => 'employees/',
p_method => 'DELETE',
p_source_type => ORDS.source_type_plsql,
p_source => 'BEGIN
remove_employee(p_empno => :empno);
END;',
p_items_per_page => 0);
COMMIT;
END;
/
The web service can be called using the following URL, method, header and payload.
URL : http://localhost:8080/ords/hr/rest-v8/employees/
Method : DELETE
Header : Content-Type: application/json
Raw Payload: { "empno": 9999 }
$ curl -i -X DELETE --data-binary @/tmp/delete-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/rest-v8/employees/
Complete Example
Each of the examples above is defined as a separate module, most of which have a single template with a single handler. This is not a true reflection of how many web services will be presented. The example below combines the templates and handlers above into a single web service, allowing records to be inserted, updated, deleted and queried. It uses some of the stored procedures defined in the previous sections.
CONN testuser1/testuser1@pdb1
BEGIN
ORDS.define_module(
p_module_name => 'rest-v9',
p_base_path => 'rest-v9/',
p_items_per_page => 0);
ORDS.define_template(
p_module_name => 'rest-v9',
p_pattern => 'employees/');
-- READ : All records.
ORDS.define_handler(
p_module_name => 'rest-v9',
p_pattern => 'employees/',
p_method => 'GET',
p_source_type => ORDS.source_type_collection_feed,
p_source => 'SELECT * FROM emp',
p_items_per_page => 0);
-- INSERT
ORDS.define_handler(
p_module_name => 'rest-v9',
p_pattern => 'employees/',
p_method => 'POST',
p_source_type => ORDS.source_type_plsql,
p_source => 'BEGIN
create_employee (p_empno => :empno,
p_ename => :ename,
p_job => :job,
p_mgr => :mgr,
p_hiredate => :hiredate,
p_sal => :sal,
p_comm => :comm,
p_deptno => :deptno);
END;',
p_items_per_page => 0);
-- UPDATE
ORDS.define_handler(
p_module_name => 'rest-v9',
p_pattern => 'employees/',
p_method => 'PUT',
p_source_type => ORDS.source_type_plsql,
p_source => 'BEGIN
amend_employee(p_empno => :empno,
p_ename => :ename,
p_job => :job,
p_mgr => :mgr,
p_hiredate => :hiredate,
p_sal => :sal,
p_comm => :comm,
p_deptno => :deptno);
END;',
p_items_per_page => 0);
-- DELETE
ORDS.define_handler(
p_module_name => 'rest-v9',
p_pattern => 'employees/',
p_method => 'DELETE',
p_source_type => ORDS.source_type_plsql,
p_source => 'BEGIN
remove_employee(p_empno => :empno);
END;',
p_items_per_page => 0);
-- READ : One Record
ORDS.define_template(
p_module_name => 'rest-v9',
p_pattern => 'employees/:empno');
ORDS.define_handler(
p_module_name => 'rest-v9',
p_pattern => 'employees/:empno',
p_method => 'GET',
p_source_type => ORDS.source_type_collection_feed,
p_source => 'SELECT * FROM emp WHERE empno = :empno',
p_items_per_page => 0);
COMMIT;
END;
/
The web service supports the following URLs, methods, headers and payloads.
READ
====
http://localhost:8080/ords/hr/rest-v9/employees/
http://localhost:8080/ords/hr/rest-v9/employees/7499
INSERT
======
URL : http://localhost:8080/ords/hr/rest-v9/employees/
Method : POST
Header : Content-Type: application/json
Raw Payload: { "empno": 9999, "ename": "HALL", "job": "ANALYST", "mgr": 7782, "hiredate": "2016-01-01", "sal": 1000, "comm": null, "deptno": 10 }
$ curl -i -X POST --data-binary @/tmp/insert-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/rest-v9/employees/
UPDATE
======
URL : http://localhost:8080/ords/hr/rest-v9/employees/
Method : PUT
Header : Content-Type: application/json
Raw Payload: { "empno": 9999, "ename": "WOOD", "job": "ANALYST", "mgr": 7782, "hiredate": "2016-01-01", "sal": 1000, "comm": null, "deptno": 20 }
$ curl -i -X PUT --data-binary @/tmp/update-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/rest-v9/employees/
DELETE
======
URL : http://localhost:8080/ords/hr/rest-v9/employees/
Method : DELETE
Header : Content-Type: application/json
Raw Payload: { "empno": 9999 }
$ curl -i -X DELETE --data-binary @/tmp/delete-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/rest-v9/employees/
For more information see:
- ORDS PL/SQL Package Reference
- Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL
- Oracle REST Data Services (ORDS) : All Articles
- Oracle REST Data Services (ORDS) : Installation on Tomcat
- Oracle REST Data Services (ORDS) : Configure Multiple Databases
- Oracle REST Data Services (ORDS) : RESTful Web Services Handling Complex JSON Payloads
- Oracle REST Data Services (ORDS) : AutoREST
- Oracle REST Data Services (ORDS) : Authentication
Hope this helps. Regards Tim...