8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

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.

Related articles.

Assumptions and Comments

This article assumes the following.

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

There are a number of steps that are necessary for ORDS to publish REST web services from a database user.

Installing ORDS configures a default database connection called "apex", available from the base URL "/ords/". We could use this existing database connection, but instead we will create a new database connection. If you already have a connection you are happy to use ignore the following database connection and URL mapping ORDS commands.

$ cd /u01/ords
$ $JAVA_HOME/bin/java -jar ords.war setup --database pdb1
Enter the name of the database server [ol7-121.localdomain]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name [pdb1]:
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:2
Nov 29, 2016 11:04:54 AM
INFO: Updated configurations: pdb1_pu
Nov 29, 2016 11:04:54 AM oracle.dbtools.rt.config.setup.SchemaSetup install
INFO: Oracle REST Data Services schema version 3.0.8.277.08.01 is installed.
$

We create a URL mapping to the new database connection.

$ cd /u01/ords
$ $JAVA_HOME/bin/java -jar ords.war map-url --type base-path /pdb1 pdb1
Nov 29, 2016 11:06:25 AM
INFO: Creating new mapping from: [base-path,/pdb1] to map to: [pdb1,,]
$

Web services from the connection can now be referenced using the following base URL.

http://ol7-121.localdomain:8080/ords/pdb1/

The final step is to enable REST web services for the test schema itself. We could use any unique and legal URL mapping pattern for the schema, so it is not necessary to expose the schema name as we have done here.

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => 'TESTUSER1',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'testuser1',
    p_auto_rest_auth      => FALSE
  );
    
  COMMIT;
END;
/

Web services from the schema can now be referenced using the following base URL.

http://ol7-121.localdomain:8080/ords/pdb1/testuser1/

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    => 'testmodule1',
    p_base_path      => 'testmodule1/',
    p_pattern        => 'emp/',
    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.

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://ol7-121.localdomain:8080/ords/pdb1/
Schema (alias): http://ol7-121.localdomain:8080/ords/pdb1/testuser1/
Module        : http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule1/
Template      : http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule1/emp/

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 testmodule1		/testmodule1/

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 emp/

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    => 'testmodule2',
    p_base_path      => 'testmodule2/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'testmodule2',
   p_pattern        => 'emp/');

  ORDS.define_handler(
    p_module_name    => 'testmodule2',
    p_pattern        => 'emp/',
    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://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/emp/

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    => 'testmodule3',
    p_base_path      => 'testmodule3/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'testmodule3',
   p_pattern        => 'emp/');

  ORDS.define_handler(
    p_module_name    => 'testmodule3',
    p_pattern        => 'emp/',
    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    => 'testmodule3',
   p_pattern        => 'emp/:empno');

  ORDS.define_handler(
    p_module_name    => 'testmodule3',
    p_pattern        => 'emp/: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://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule3/emp/
http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule3/emp/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    => 'testmodule3b',
    p_base_path      => 'testmodule3b/',
    p_items_per_page => 0);

  ORDS.define_template(
   p_module_name    => 'testmodule3b',
   p_pattern        => 'emp/');

  ORDS.define_handler(
    p_module_name    => 'testmodule3b',
    p_pattern        => 'emp/',
    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://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule3b/emp/?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    => 'testmodule3b',
    p_base_path      => 'testmodule3b/',
    p_items_per_page => 0);

  ORDS.define_template(
   p_module_name    => 'testmodule3b',
   p_pattern        => 'emp/:empno_start/:empno_end');

  ORDS.define_handler(
    p_module_name    => 'testmodule3b',
    p_pattern        => 'emp/',
    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://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule3b/emp/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    => 'testmodule3c',
    p_base_path      => 'testmodule3c/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'testmodule3c',
   p_pattern        => 'emp/:empno');

  ORDS.define_handler(
    p_module_name    => 'testmodule3c',
    p_pattern        => 'emp/: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://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule3c/emp/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,'DD-MON-YYYY') 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    => 'testmodule4',
    p_base_path      => 'testmodule4/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'testmodule4',
   p_pattern        => 'emp/');

  ORDS.define_handler(
    p_module_name    => 'testmodule4',
    p_pattern        => 'emp/',
    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    => 'testmodule4',
   p_pattern        => 'emp/:empno');

  ORDS.define_handler(
    p_module_name    => 'testmodule4',
    p_pattern        => 'emp/: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://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule4/emp/
http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule4/emp/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,'DD-MON-YYYY') 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    => 'testmodule5',
    p_base_path      => 'testmodule5/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'testmodule5',
   p_pattern        => 'emp/');

  ORDS.define_handler(
    p_module_name    => 'testmodule5',
    p_pattern        => 'emp/',
    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    => 'testmodule5',
   p_pattern        => 'emp/:empno');

  ORDS.define_handler(
    p_module_name    => 'testmodule5',
    p_pattern        => 'emp/: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://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule5/emp/
http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule5/emp/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, 'DD-MON-YYYY'), 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    => 'testmodule6',
    p_base_path      => 'testmodule6/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'testmodule6',
   p_pattern        => 'emp/');

  ORDS.define_handler(
    p_module_name    => 'testmodule6',
    p_pattern        => 'emp/',
    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://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule6/emp/
Method     : POST
Header     : Content-Type: application/json
Raw Payload: { "empno": 9999, "ename": "HALL", "job": "ANALYST", "mgr": 7782, "hiredate": "01-JAN-2016", "sal": 1000, "comm": null, "deptno": 10 }

$ curl -i -X POST --data-binary @/tmp/insert-payload.json -H "Content-Type: application/json" http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule6/emp/

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, 'DD-MON-YYYY'),
      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    => 'testmodule7',
    p_base_path      => 'testmodule7/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'testmodule7',
   p_pattern        => 'emp/');

  ORDS.define_handler(
    p_module_name    => 'testmodule7',
    p_pattern        => 'emp/',
    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://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule7/emp/
Method     : PUT
Header     : Content-Type: application/json
Raw Payload: { "empno": 9999, "ename": "WOOD", "job": "ANALYST", "mgr": 7782, "hiredate": "01-JAN-2016", "sal": 1000, "comm": null, "deptno": 20 }

$ curl -i -X PUT --data-binary @/tmp/update-payload.json -H "Content-Type: application/json" http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule7/emp/

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    => 'testmodule8',
    p_base_path      => 'testmodule8/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'testmodule8',
   p_pattern        => 'emp/');

  ORDS.define_handler(
    p_module_name    => 'testmodule8',
    p_pattern        => 'emp/',
    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://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule8/emp/
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://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule8/emp/

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    => 'testmodule9',
    p_base_path      => 'testmodule9/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'testmodule9',
   p_pattern        => 'emp/');

  -- READ : All records.
  ORDS.define_handler(
    p_module_name    => 'testmodule9',
    p_pattern        => 'emp/',
    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    => 'testmodule9',
    p_pattern        => 'emp/',
    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    => 'testmodule9',
    p_pattern        => 'emp/',
    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    => 'testmodule9',
    p_pattern        => 'emp/',
    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    => 'testmodule9',
   p_pattern        => 'emp/:empno');

  ORDS.define_handler(
    p_module_name    => 'testmodule9',
    p_pattern        => 'emp/: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://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule9/emp/
http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule9/emp/7499


INSERT
======
URL        : http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule9/emp/
Method     : POST
Header     : Content-Type: application/json
Raw Payload: { "empno": 9999, "ename": "HALL", "job": "ANALYST", "mgr": 7782, "hiredate": "01-JAN-2016", "sal": 1000, "comm": null, "deptno": 10 }

$ curl -i -X POST --data-binary @/tmp/insert-payload.json -H "Content-Type: application/json" http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule9/emp/


UPDATE
======
URL        : http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule9/emp/
Method     : PUT
Header     : Content-Type: application/json
Raw Payload: { "empno": 9999, "ename": "WOOD", "job": "ANALYST", "mgr": 7782, "hiredate": "01-JAN-2016", "sal": 1000, "comm": null, "deptno": 20 }

$ curl -i -X PUT --data-binary @/tmp/update-payload.json -H "Content-Type: application/json" http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule9/emp/


DELETE
======
URL        : http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule9/emp/
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://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule9/emp/

For more information see:

Hope this helps. Regards Tim...

Back to the Top.