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.

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.

We can see the definition in the database using theUSER_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/query      GET	  SELECT * FROM emp

SQL>

Rather than using theDEFINE_SERVICEprocedure,we can build the same web service manually using theDEFINE_MODULE,DEFINE_TEMPLATEandDEFINE_HANDLERprocedures. 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_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/

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.

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 theAPEX_JSONpackage 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 ofSOURCE_TYPE_PLSQLand 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_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_source         => 'BEGIN get_emp_json(:empno); END;',204); border-radius: 4px;">http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule4/emp/
http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule4/emp/7499

Using a stored procedure is a simple way to produce XML REST web services. To show this,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.deptno AS "deptno"
               )
             )
           )
         ).getClobVal()
  INTO   l_clob
  FROM   emp e
  WHERE  e.empno = DECODE(p_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_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_source         => 'BEGIN get_emp_xml(:empno); END;',204); border-radius: 4px;">http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule5/emp/
http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule5/emp/7499

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.