3 Developing Oracle REST Data Services Applications

This section explains how to develop applications that use Oracle REST Data Services. It includes guidance and examples.

Note:

If you want to get started quickly, you can try the tutorial in Getting Started with RESTful Services now. However, you should then return to this chapter to understand the main concepts and techniques.

Note:

Ensure that you have installed and configured both Oracle Application Express 4.2 or later, and Oracle REST Data Services 3.0 or later, before attempting to follow any of the tutorials and examples.

To use the Oracle REST API for JSON Data Persistence, you must first install the Oracle REST API. See "Oracle REST API Installation" in Oracle REST Data Services SODA for REST Developer's Guide.

It is assumed that you are familiar with Oracle Application Express. If you are new to Oracle Application Express, see the Oracle Application Express documentation.

Topics:

You may also want to review Development Tutorial: Creating an Image Gallery, a supplementary extended example that uses Oracle Application Express to build an application.

3.1 Introduction to Relevant Software

This section explains some key relevant software for developing applications that use Oracle REST Data Services.

Topics:

3.1.1 About Oracle Application Express

Oracle Application Express is a declarative, rapid web application development tool for the Oracle database. It is a fully supported, no cost option available with all editions of the Oracle database. Using only a web browser, you can develop and deploy professional applications that are both fast and secure.

3.1.2 About RESTful Web Services

Representational State Transfer (REST) is a style of software architecture for distributed hypermedia systems such as the World Wide Web. An API is described as RESTful when it conforms to the tenets of REST. Although a full discussion of REST is outside the scope of this document, a RESTful API has the following characteristics:

  • Data is modelled as a set of resources. Resources are identified by URIs.

  • A small, uniform set of operations are used to manipulate resources (for example, PUT, POST, GET, DELETE).

  • A resource can have multiple representations (for example, a blog might have an HTML representation and an RSS representation).

  • Services are stateless and since it is likely that the client will want to access related resources, these should be identified in the representation returned, typically by providing hypertext links.

Release 4.2 of Oracle Application Express leverages the capabilities of Oracle REST Data Services to provide developers with an easy to use graphical user interface for defining and testing RESTful Web Services.

3.2 Getting Started with RESTful Services

This section introduces RESTful Services, and provides guidelines and examples for developing applications that use RESTful Services.

Topics:

3.2.1 RESTful Services Terminology

This section introduces some common terms that are used throughout this document:

  • RESTful service: An HTTP web service that conforms to the tenets of the RESTful architectural style.

  • Resource module: An organizational unit that is used to group related resource templates.

  • Resource template: An individual RESTful service that is able to service requests for some set of URIs (Universal Resource Identifiers). The set of URIs is defined by the URI Pattern of the Resource Template

  • URI pattern: A pattern for the resource template. Can be either a route pattern or a URI template, although you are encouraged to use route patterns.

  • Route pattern: A pattern that focuses on decomposing the path portion of a URI into its component parts. For example, a pattern of /:object/:id? will match /emp/101 (matches a request for the item in the emp resource with id of 101) and will also match /emp/ (matches a request for the emp resource, because the :id parameter is annotated with the ? modifier, which indicates that the id parameter is optional).

    For a detailed explanation of route patterns, see docs\javadoc\plugin-api\route-patterns.html, under <sqldeveloper-install>\ords and under the location (if any) where you manually installed Oracle REST Data Services.

  • URI template: A simple grammar that defines the specific patterns of URIs that a given resource template can handle. For example, the pattern employees/{id} will match any URI whose path begins with employees/, such as employees/2560.

  • Resource handler: Provides the logic required to service a specific HTTP method for a specific resource template. For example, the logic of the GET HTTP method for the preceding resource template might be:

    select empno, ename, dept from emp where empno = :id
    
  • HTTP operation: HTTP (HyperText Transport Protocol) defines standard methods that can be performed on resources: GET (retrieve the resource contents), POST (store a new resource), PUT (update an existing resource), and DELETE (remove a resource).

3.2.2 About Request Path Syntax Requirements

To prevent path-based attacks, Oracle REST Data Services performs a number of validation checks on the syntax of the path element of each request URL.

Each path must conform to the following rules:

  • Is not empty or whitespace-only

  • Does not contain any of the following characters: ?, #, ;, %

  • Does not contain the null character (\u0000)

  • Does not contain characters in the range: \u0001-\u0031

  • Does not end with white space or a period (.)

  • Does not contain double forward slash (//) or double back slash(\\)

  • Does not contain two or more periods in sequence (.., ..., and so on)

  • Total length is {@value #MAX_PATH_LENGTH} characters or less

  • Does not match any of the following names (case insensitive), with or without file extensions: CON, PRN, AUX, CLOCK$, NUL, COM0, COM1, COM2, COM3, COM4, COM5, COM6, COM7, COM8, COM9, LPT0, LPT1, LPT2, LPT3, LPT4, LPT5, LPT6, LPT7, LPT8, LPT9

If you intend to auto-REST enable objects, then avoid object names that do not comply with these requirements. For example, do not create a table named #EMPS. If you do want to auto-REST enable objects that have non-compliant names, then you must use an alias that complies with the requirements.

These requirements are applied to the URL decoded form of the URL, to prevent attempted circumvention of percent encodings.

3.2.3 "Getting Started" Documents Included in Installation

When you install Oracle REST Data Services, an examples folder is created with subfolders and files that you may find helpful. The installation folder hierarchy includes this:

ords
  conf
  docs
  examples
    soda
    getting-started
    getting-started-nosql
  ...

In this hierarchy:

  • examples\soda: Contains sample JSON documents used in some examples included in Oracle REST Data Services SODA for REST Developer's Guide.

  • examples\getting-started: Double-click index.html for a short document about how to get started developing RESTful Services using Oracle REST Data Services. This document focuses on using SQL Developer to get started. (SQL Developer is the primary tool for managing Oracle REST Data Services. For example, the ability to auto-enable REST support for schemas and tables is available only in SQL Developer.)

  • examples\getting-started-nosql: Double-click index.html for a short document about how to get started accessing NoSQL stores using Oracle REST Data Services

3.2.4 About cURL and Testing RESTful Services

Other sections show the testing of RESTful Services using a web browser. However, another useful way to test RESTful Services is using the command line tool named cURL.

This powerful tool is available for most platforms, and enables you to see and control what data is being sent to and received from a RESTful service.

curl -i https://server:port/ords/workspace/hr/employees/7369

This example produces a response like the following:

HTTP/1.1 200 OK
Server: Oracle-REST-Data-Services/2.0.6.78.05.25
ETag: "..." 
Content-Type: application/json
Transfer-Encoding: chunked
Date: Thu, 28 Mar 2014 16:49:34 GMT  

{
 "empno":7369,
 "ename":"SMITH",
 "job":"CLERK",
 "mgr":7902,
 "hiredate":"1980-12-17T08:00:00Z",
 "sal":800,
 "deptno":20
}

The -i option tells cURL to display the HTTP headers returned by the server.

See Also:

cURL

The example in this section uses cURL with the services mentioned in Exploring the Sample RESTful Services in Application Express (Tutorial)

3.2.5 Automatic Enabling of Schema Objects for REST Access (AutoREST)

If Oracle REST Data Services has been installed on the system associated with a database connection, you can use the AutoREST feature to conveniently enable or disable Oracle REST Data Services access for specified tables and views in the schema associated with that database connection. Enabling REST access to a table, view or PL/SQL function, procedure or package allows it to be accessed through RESTful services.

AutoREST is a quick and easy way to expose database tables as REST resources. You sacrifice some flexibility and customizability to gain ease of effort. AutoRest lets you quickly expose data but (metaphorically) keeps you on a set of guide rails. For example, you cannot customize the output formats or the input formats, or do extra validation.

On the other hand, manually created resource modules require you to specify the SQL and PL/SQL to support the REST resources. Using resource modules requires more effort, but offers more flexibility; for example, you can customize what fields are included, do joins across multiple tables, and validate the incoming data using PL/SQL.

So, as an application developer you must make a choice: use the "guide rails" of AutoREST, or create a resource module to do exactly what you need. If you choose AutoREST, you can just enable a table (or set of tables) within a schema.

Note that enabling a schema is not equivalent to enabling all tables and views in the schema. It just means making Oracle REST Data Services aware that the schema exists and that it may have zero or more resources to expose to HTTP. Those resources may be AutoREST resources or resource module resources.

You can automatically enable Oracle REST Data Services queries to access individual database schema objects (tables, views, and PL/SQL) by using a convenient wizard in Oracle SQL Developer. (Note that this feature is only available for Oracle REST Data Services- enabled schemas, not for Oracle Application Express workspaces.)

To enable Oracle REST Data Services access to one or more specified tables or views, you must do the following in SQL Developer:

  1. Enable the schema (the one associated with the connection) for REST access.

    Schema level: To enable Oracle REST Data Services access to selected objects (that you specify in the next step) in the schema associated with a connection, right-click its name in the Connections navigator and select REST Services, then Enable REST Services.

    (To drop support for Oracle REST Data Services access to objects in the schema associated with a connection, right-click its name in the Connections navigator and select REST Services, then Drop REST Services.)

  2. Individually enable REST access for the desired objects.

    Table or view level: To enable Oracle REST Data Services access to a specified table or view, right-click its name in the Connections navigator and select Enable REST Services.

For detailed usage information, click the Help button in the wizard or dialog box in SQL Developer.

3.2.5.1 Examples: Accessing Objects Using RESTful Services

This section provides examples of using Oracle REST Data Services queries and other operations against tables and views after you have REST-enabled them.

You can automatically expose table and view objects as RESTful services using SQL Developer. This topic provides examples of accessing these RESTful services.

Tip:

Although these examples illustrate the URL patterns used to access these resources, clients should avoid hard coding knowledge of the structure of these URLs; instead clients should follow the hyperlinks in the resources to navigate between resources. The structure of the URL patterns may evolve and change in future releases.

This topic provides examples of accessing objects using RESTful Services.

3.2.5.1.1 Get Schema Metadata

This example retrieves a list of resources available through the specified schema alias. It shows RESTful services that are created by automatically enabling a table or view, along with RESTful Services that are created by resource modules.

This example retrieves a list of resources available through the specified schema alias.

Pattern: GET http://<HOST>:<PORT>/ords/<SchemaAlias>/metadata-catalog/

Example: GET http://localhost:8080/ords/ordstest/metadata-catalog/

Result:

{
  "items": [
 {
   "name": "EMP",
   "links": [
  {
    "rel": "describes",
    "href": "http://localhost:8080/ords/ordstest/emp/"
  },
  {
    "rel": "canonical",
    "href": "http://localhost:8080/ords/ordstest/metadata-catalog/emp/",
    "mediaType": "application/json"
  }
   ]
 },
 {
   "name": "oracle.examples.hello",
   "links": [
  {
    "rel": "describes",
    "href": "http://localhost:8080/ords/ordstest/examples/hello/"
  },
  {
    "rel": "canonical",
    "href": "http://localhost:8080/ords/ordstest/metadata-catalog/examples/hello/",
    "mediaType": "application/json"
  }
   ]
 }
  ],
  "hasMore": false,
  "limit": 25,
  "offset": 0,
  "count": 2,
  "links": [
 {
   "rel": "self",
   "href": "http://localhost:8080/ords/ordstest/metadata-catalog/"
 },
 {
   "rel": "first",
   "href": "http://localhost:8080/ords/ordstest/metadata-catalog/"
 }
  ]
}

The list of resources includes:

  • Resources representing tables or views that have been REST enabled.

  • Resources defined by resource modules. Note that only resources having a concrete path (that is, not containing any parameters) will be shown. For example, a resource with a path of /module/some/path/ will be shown, but a resource with a path of /module/some/:parameter/ will not be shown.

Each available resource has two hyperlinks:

  • The link with relation describes points to the actual resource.

  • The link with relation canonical describes the resource.

3.2.5.1.2 Get Object Metadata

This example retrieves the metadata (which describes the object) of an individual object. The location of the metadata is indicated by the canonical link relation.

Pattern: GET http://<HOST>:<PORT>/ords/<SchemaAlias>/metadata-catalog/<ObjectAlias>/

Example: GET http://localhost:8080/ords/ordstest/metadata-catalog/emp/

Result:

{
    "name": "EMP",
    "primarykey": [
        "empno"
    ],
    "members": [
        {
            "name": "empno",
            "type": "NUMBER"
        },
        {
            "name": "ename",
            "type": "VARCHAR2"
        },
        {
            "name": "job",
            "type": "VARCHAR2"
        },
        {
            "name": "mgr",
            "type": "NUMBER"
        },
        {
            "name": "hiredate",
            "type": "DATE"
        },
        {
            "name": "sal",
            "type": "NUMBER"
        },
        {
            "name": "comm",
            "type": "NUMBER"
        },
        {
            "name": "deptno",
            "type": "NUMBER"
        }
    ],
    "links": [
        {
            "rel": "collection",
            "href": "http://localhost:8080/ords/ordstest/metadata-catalog/",
            "mediaType": "application/json"
        },
        {
            "rel": "canonical",
            "href": "http://localhost:8080/ords/ordstest/metadata-catalog/emp/"
        },
        {
            "rel": "describes",
            "href": "http://localhost:8080/ords/ordstest/emp/"
        }
    ]
}
3.2.5.1.3 Get Object Data

This example retrieves the data in the object. Each row in the object corresponds to a JSON object embedded within the JSON array

Pattern: GET http://<HOST>:<PORT>/ords/<SchemaAlias>/<ObjectAlias>/

Example: GET http://localhost:8080/ords/ordstest/emp/

Result:

{
 "items": [
  {
   "empno": 7499,
   "ename": "ALLEN",
   "job": "SALESMAN",
   "mgr": 7698,
   "hiredate": "1981-02-20T00:00:00Z",
   "sal": 1600,
   "comm": 300,
   "deptno": 30,
   "links": [
    {
     "rel": "self",
     "href": "http://localhost:8080/ords/ordstest/emp/7499"
    }
   ]
  },
  ...
  {
   "empno": 7934,
   "ename": "MILLER",
   "job": "CLERK",
   "mgr": 7782,
   "hiredate": "1982-01-23T00:00:00Z",
   "sal": 1300,
   "comm": null,
   "deptno": 10,
   "links": [
    {
     "rel": "self",
     "href": "http://localhost:8080/ords/ordstest/emp/7934"
    }
   ]
  }
 ],
 "hasMore": false,
 "limit": 25,
 "offset": 0,
 "count": 13,
 "links": [
  {
   "rel": "self",
   "href": "http://localhost:8080/ords/ordstest/emp/"
  },
  {
   "rel": "edit",
   "href": "http://localhost:8080/ords/ordstest/emp/"
  },
  {
   "rel": "describedby",
   "href": "http://localhost:8080/ords/ordstest/metadata-catalog/emp/"
  },
  {
   "rel": "first",
   "href": "http://localhost:8080/ords/ordstest/emp/"
  }
 ]
}
3.2.5.1.4 Get Table Data Using Paging

This example specifies the offset and limit parameters to control paging of result data.

Pattern: GET http://<HOST>:<PORT>/ords/<SchemaAlias>/<ObjectAlias>/?offset=<Offset>&limit=<Limit>

Example: GET http://localhost:8080/ords/ordstest/emp/?offset=10&limit=5

Result:

{
 "items": [
  {
   "empno": 7900,
   "ename": "JAMES",
   "job": "CLERK",
   "mgr": 7698,
   "hiredate": "1981-12-03T00:00:00Z",
   "sal": 950,
   "comm": null,
   "deptno": 30,
   "links": [
    {
     "rel": "self",
     "href": "http://localhost:8080/ords/ordstest/emp/7900"
    }
   ]
  },
  ...
  {
   "empno": 7934,
   "ename": "MILLER",
   "job": "CLERK",
   "mgr": 7782,
   "hiredate": "1982-01-23T00:00:00Z",
   "sal": 1300,
   "comm": null,
   "deptno": 10,
   "links": [
    {
     "rel": "self",
     "href": "http://localhost:8080/ords/ordstest/emp/7934"
    }
   ]
  }
 ],
 "hasMore": false,
 "limit": 5,
 "offset": 10,
 "count": 3,
 "links": [
  {
   "rel": "self",
   "href": "http://localhost:8080/ords/ordstest/emp/"
  },
  {
   "rel": "edit",
   "href": "http://localhost:8080/ords/ordstest/emp/"
  },
  {
   "rel": "describedby",
   "href": "http://localhost:8080/ords/ordstest/metadata-catalog/emp/"
  },
  {
   "rel": "first",
   "href": "http://localhost:8080/ords/ordstest/emp/?limit=5"
  },
  {
   "rel": "prev",
   "href": "http://localhost:8080/ords/ordstest/emp/?offset=5&limit=5"
  }
 ]
}
3.2.5.1.5 Get Table Data Using Query

This example specifies a filter clause to restrict objects returned.

Pattern: GET http://<HOST>:<PORT>/ords/<SchemaAlias>/<ObjectAlias>/?q=<FilterClause>

Example: GET http://localhost:8080/ords/ordstest/emp/?q={"deptno":{"$lte":20}}

Result:

{
 "items": [
  {
   "empno": 7566,
   "ename": "JONES",
   "job": "MANAGER",
   "mgr": 7839,
   "hiredate": "1981-04-01T23:00:00Z",
   "sal": 2975,
   "comm": null,
   "deptno": 20,
   "links": [
    {
     "rel": "self",
     "href": "http://localhost:8080/ords/ordstest/emp/7566"
    }
   ]
  },
  ...
  {
   "empno": 7934,
   "ename": "MILLER",
   "job": "CLERK",
   "mgr": 7782,
   "hiredate": "1982-01-23T00:00:00Z",
   "sal": 1300,
   "comm": null,
   "deptno": 10,
   "links": [
    {
     "rel": "self",
     "href": "http://localhost:8080/ords/ordstest/emp/7934"
    }
   ]
  }
 ],
 "hasMore": false,
 "limit": 25,
 "offset": 0,
 "count": 7,
 "links": [
  {
   "rel": "self",
   "href": "http://localhost:8080/ords/ordstest/emp/?q=%7B%22deptno%22:%7B%22%24lte%22:20%7D%7D"
  },
  {
   "rel": "edit",
   "href": "http://localhost:8080/ords/ordstest/emp/?q=%7B%22deptno%22:%7B%22%24lte%22:20%7D%7D"
  },
  {
   "rel": "describedby",
   "href": "http://localhost:8080/ords/ordstest/metadata-catalog/emp/"
  },
  {
   "rel": "first",
   "href": "http://localhost:8080/ords/ordstest/emp/?q=%7B%22deptno%22:%7B%22%24lte%22:20%7D%7D"
  }
 ]
}
3.2.5.1.6 Get Table Row Using Primary Key

This example retrieves an object by specifying its identifying key values.

Pattern: GET http://<HOST>:<PORT>/ords/<SchemaAlias>/<ObjectAlias>/<KeyValues>

Where <KeyValues> is a comma-separated list of key values (in key order).

Example: GET http://localhost:8080/ords/ordstest/emp/7839

Result:

{
 "empno": 7839,
 "ename": "KING",
 "job": "PRESIDENT",
 "mgr": null,
 "hiredate": "1981-11-17T00:00:00Z",
 "sal": 5000,
 "comm": null,
 "deptno": 10,
 "links": [
  {
   "rel": "self",
   "href": "http://localhost:8080/ords/ordstest/emp/7839"
  },
  {
   "rel": "edit",
   "href": "http://localhost:8080/ords/ordstest/emp/7839"
  },
  {
   "rel": "describedby",
   "href": "http://localhost:8080/ords/ordstest/metadata-catalog/emp/item"
  },
  {
   "rel": "collection",
   "href": "http://localhost:8080/ords/ordstest/emp/"
  }
 ]
}
3.2.5.1.7 Insert Table Row

This example inserts data into the object. The body data supplied with the request is a JSON object containing the data to be inserted.

If the object has a primary key, then there must be an insert trigger on the object that populates the primary key fields. If the table does not have a primary key, then the ROWID of the row will be used as the item's identifier.

If the object lacks a trigger to assign primary key values, then the PUT operation described in next section,Update/Insert Table Row should be used instead.

Pattern: POST http://<HOST>:<PORT>/ords/<SchemaAlias>/<ObjectAlias>/

Example:

curl -i -H "Content-Type: application/json" -X POST -d "{ \"empno\" :7, \"ename\": \"JBOND\", \"job\":\"SPY\", \"deptno\" :11 }" "http://localhost:8080/ords/ordstest/emp/
Content-Type: application/json
 
{ "empno" :7, "ename": "JBOND", "job":"SPY", "deptno" :11 }

Result:

{
 "empno": 7,
 "ename": "JBOND",
 "job": "SPY",
 "mgr": null,
 "hiredate": null,
 "sal": null,
 "comm": null,
 "deptno": 11,
 "links": [
  {
   "rel": "self",
   "href": "http://localhost:8080/ords/ordstest/emp/7"
  },
  {
   "rel": "edit",
   "href": "http://localhost:8080/ords/ordstest/emp/7"
  },
  {
   "rel": "describedby",
   "href": "http://localhost:8080/ords/ordstest/metadata-catalog/emp/item"
  },
  {
   "rel": "collection",
   "href": "http://localhost:8080/ords/ordstest/emp/"
  }
 ]
}
3.2.5.1.8 Update/Insert Table Row

This example inserts or updates (sometimes called an "upsert") data in the object. The body data supplied with the request is a JSON object containing the data to be inserted or updated.

Pattern: PUT http://<HOST>:<PORT>/ords/<SchemaAlias>/<ObjectAlias>/<KeyValues>

Example:

curl -i -H "Content-Type: application/json" -X PUT -d "{ \"empno\" :7, \"ename\": \"JBOND\", \"job\":\"SPY\", \"deptno\" :11 }" "http://localhost:8080/ords/ordstest/emp/7
Content-Type: application/json
 
{ "empno" :7, "ename": "JBOND", "job":"SPY", "deptno" :11 }

Result:

{
 "empno": 7,
 "ename": "JBOND",
 "job": "SPY",
 "mgr": null,
 "hiredate": null,
 "sal": null,
 "comm": null,
 "deptno": 11,
 "links": [
  {
   "rel": "self",
   "href": "http://localhost:8080/ords/ordstest/emp/7"
  },
  {
   "rel": "edit",
   "href": "http://localhost:8080/ords/ordstest/emp/7"
  },
  {
   "rel": "describedby",
   "href": "http://localhost:8080/ords/ordstest/metadata-catalog/emp/item"
  },
  {
   "rel": "collection",
   "href": "http://localhost:8080/ords/ordstest/emp/"
  }
 ]
}
3.2.5.1.9 Delete Using Filter

This example deletes object data specified by a filter clause.

Pattern: DELETE http://<HOST>:<PORT>/ords/<SchemaAlias>/<ObjectAlias>/?q=<FilterClause>

Example: curl -i -X DELETE "http://localhost:8080/ords/ordstest/emp/?q={"deptno":11}"

Result:

{ 
    "itemsDeleted": 1 
}
3.2.5.1.10 Post by Batch Load

This example inserts object data using the batch load feature. The body data supplied with the request is a CSV file. The behavior of the batch operation can be controlled using the optional query parameters, which are described in Table 3-1.

Pattern: POST http://<HOST>:<PORT>/ords/<SchemaAlias>/<ObjectAlias>/batchload?<Parameters>

Parameters:

Table 3-1 Parameters for batchload

Parameter Description

batchesPerCommit

Sets the frequency for commits. Optional commit points can be set after a batch is sent to the database. The default is every 10 batches. 0 indicates commit deferred to the end of the load. Type: Integer.

batchRows

Sets the number of rows in each batch to send to the database. The default is 50 rows per batch. Type: Integer.

dateFormat

Sets the format mask for the date data type. This format is used when converting input data to columns of type date. Type: String.

delimiter

Sets the field delimiter for the fields in the file. The default is the comma (,).

enclosures

embeddedRightDouble

errors

Sets the user option used to limit the number of errors. If the number of errors exceeds the value specified for errorsMax (the service option) or by errors (the user option), then the load is terminated.

To permit no errors at all, specify 0. To indicate that all errors be allowed (up to errorsMax value), specify UNLIMITED (-1) .

errorsMax

A service option used to limit the number of errors allowed by users. It intended as an option for the service provider and not to be exposed as a user option. If the number of errors exceeds the value specified for errorsMax (the service option) or by errors (the user option), then the load is terminated.

To permit no errors at all, specify 0. To indicate that all errors be allowed, specify UNLIMITED (-1).

lineEnd

Sets the line end (terminator). If the file contains standard line end characters (\r. \r\n or \n), then lineEnd does not need to be specified.

lineMax

Sets a maximum line length for identifying lines/rows in the data stream. A lineMax value will prevent reading an entire stream as a single line when the incorrect lineEnd character is being used. The default is unlimited.

locale

Sets the locale.

responseEncoding

Sets the encoding for the response stream.

responseFormat

Sets the format for response stream. This format determines how messages and bad data will be formatted. Valid values: RAW, SQL.

timestampFormat

Sets the format mask for the time stamp data type. This format is used when converting input data to columns of type time stamp.

timestampTZFormat

Sets the format mask for the time stamp time zone data type. This format is used when converting input data to columns of type time stamp time zone.

truncate

Indicates if and/or how table data rows should be deleted before the load. False (the default) does not delete table data before the load; True causes table data to be deleted with the DELETE SQL statement; Truncate causes table data to be deleted with the TRUNCATE SQL statement.

Example:

POST http://localhost:8080/ords/ordstest/emp/batchload?batchRows=25
Content-Type: text/csv
 
empno,ename,job,mgr,hiredate,sal,comm,deptno
0,M,SPY MAST,,2005-05-01 11:00:01,4000,,11
7,J.BOND,SPY,0,2005-05-01 11:00:01,2000,,11
9,R.Cooper,SOFTWARE,0,2005-05-01 11:00:01,10000,,11
26,Max,DENTIST,0,2005-05-01 11:00:01,5000,,11

Result:

#INFO Number of rows processed: 4
#INFO Number of rows in error: 0
#INFO Elapsed time: 00:00:03.939 - (3,939 ms) 0 - SUCCESS: Load processed without errors

3.2.5.2 Filtering in Queries

This section describes and provides examples of filtering in queries against REST-enabled tables and views.

Filtering is the process of limiting a collection resource by using a per-request dynamic filter definition across multiple page resources, where each page contains a subset of items found in the complete collection. Filtering enables efficient traversal of large collections.

To filter in a query, include the parameter q=FilterObject, where FilterObject is a JSON object that represents the custom selection and sorting to be applied to the resource. For example, assume the following resource:

https://example.com/ords/scott/emp/

The following query includes a filter that restricts the ENAME column to "JOHN":

https://example.com/ords/scott/emp/?q={"ENAME":"JOHN"}
3.2.5.2.1 FilterObject Grammar

The FilterObject must be a JSON object that complies with the following syntax:

FilterObject { orderby , asof, wmembers }

The orderby, asof, and wmembers attributes are optional, and their definitions are as follows:

orderby
  "$orderby": {orderByMembers}
 
orderByMembers
    orderByProperty
    orderByProperty , orderByMembers
 
orderByProperty
    columnName : sortingValue
 
sortingValue
  "ASC"
  "DESC"
  "-1"
  "1"
   -1
   1
   
asof
  "$asof": date
  "$asof": "datechars"
  "$asof": scn
  "$asof": +int
 
wmembers
    wpair
    wpair , wmembers
 
wpair
    columnProperty
    complexOperatorProperty
 
columnProperty
    columnName : string
    columnName : number
    columnName : date
    columnName : simpleOperatorObject
columnName : complexOperatorObject
    columnName : [complexValues]
 
columnName
  "\p{Alpha}[[\p{Alpha}]]([[\p{Alnum}]#$_])*$"
 
complexOperatorProperty
    complexKey : [complexValues]
    complexKey : simpleOperatorObject 
 
complexKey
  "$and"
  "$or"
 
complexValues
    complexValue , complexValues
 
complexValue
    simpleOperatorObject
    complexOperatorObject
    columnObject
 
columnObject
    {columnProperty}
 
simpleOperatorObject
    {simpleOperatorProperty}
 
complexOperatorObject
    {complexOperatorProperty}
 
simpleOperatorProperty
   "$eq" : string | number | date
   "$ne" : string | number | date
   "$lt" :  number | date
   "$lte" : number | date
   "$gt" : number | date
   "$gte" : number | date
   "$instr" : string 
   "$ninstr" : string
   "$like" : string
   "$null" : null
   "$notnull" : null
   "$between" : betweenValue
 
betweenValue
    [null , betweenNotNull]
    [betweenNotNull , null]
    [betweenRegular , betweenRegular]
 
betweenNotNull
    number
    date
    
betweenRegular
    string
    number
    date

Data type definitions include the following:

string 
       JSONString
number
       JSONNumber
date
       {"$date":"datechars"}
scn
       {"$scn": +int}

Where:

datechars is an RFC3339 date format in UTC (Z)
        
 
JSONString
          ""
          " chars "
chars
         char
         char chars
char
         any-Unicode-character except-"-or-\-or-control-character
          \"
          \\
           \/
          \b
          \f
          \n
          \r
          \t
          \u four-hex-digits
 
 
JSONNumber
    int
    int frac
    int exp
    int frac exp
int
    digit
    digit1-9 digits 
    - digit
    - digit1-9 digits
frac
    . digits
exp
    e digits
digits
    digit
    digit digits
e
    e
    e+
    e-
    E
    E+
    E-

The FilterObject must be encoded according to Section 2.1 of RFC3986.

3.2.5.2.2 Examples: FilterObject Specifications

The following are examples of operators in FilterObject specifications.

ORDER BY property ($orderby)

Order by with literals
 
{
 "$orderby": {"SALARY":  "ASC","ENAME":"DESC"}
}
 
Order by with numbers
 
{
 "$orderby": {"SALARY":  -1,"ENAME":  1}
}
 

ASOF property ($asof)
 
With SCN (Implicit)
 
{
  "$asof": 1273919
}
 
With SCN (Explicit)
 
{
  "$asof": {"$scn": "1273919"}
}
 
With Date (Implicit)
 
{
  "$asof": "2014-06-30T00:00:00Z"
}
 
With Date (Explicit)
 
{
  "$asof": {"$date": "2014-06-30T00:00:00Z"}
}
 
 
EQUALS operator ($eq)
 
(Implicit and explicit equality supported._
 
Implicit (Support String and Dates too)
 
{
 "SALARY": 1000
}
 
Explicit
 
{
 "SALARY": {"$eq": 1000}
}
 
Strings
 
{
 "ENAME": {"$eq":"SMITH"}
}
 
Dates
 
{
  "HIREDATE": {"$date": "1981-11-17T08:00:00Z"}
}
 
 
NOT EQUALS operator ($ne)
 
Number
 
{
 "SALARY": {"$ne": 1000}
}
 
String
 
{
 "ENAME": {"$ne":"SMITH"}
}
 
Dates
 
{
  "HIREDATE": {"$ne": {"$date":"1981-11-17T08:00:00Z"}}
}
 
 
LESS THAN operator ($lt)
(Supports dates and numbers only)
 
Numbers
 
{
  "SALARY": {"$lt": 10000}
}
 
Dates
 
{
  "SALARY": {"$lt": {"$date":"1999-12-17T08:00:00Z"}}
}
 
LESS THAN OR EQUALS operator ($lte)
(Supports dates and numbers only)
 
Numbers
 
{
  "SALARY": {"$lte": 10000}
}
 
Dates
 
{
  "HIREDATE": {"$lte": {"$date":"1999-12-17T08:00:00Z"}}
}
 
GREATER THAN operator ($gt)
(Supports dates and numbers only)
 
Numbers
 
{
  "SALARY": {"$gt": 10000}
}
 
Dates
 
{
  "SALARY": {"$gt": {"$date":"1999-12-17T08:00:00Z"}}
}
 
 
GREATER THAN OR EQUALS operator ($gte)
(Supports dates and numbers only)
 
Numbers
 
{
  "SALARY": {"$gte": 10000}
}
 
Dates
 
{
  "HIREDATE": {"$gte": {"$date":"1999-12-17T08:00:00Z"}}
}
 

In string operator ($instr)
(Supports strings only)
 
{
  "ENAME": {"$instr":"MC"}
}
 
 
Not in string operator ($ninstr)
(Supports strings only)
 
{
  "ENAME": {"$ninstr":"MC"}
}
 
 
#### LIKE operator ($like)
(Supports strings. Eescape character not supported to try to match expressions with _ or % characters.)
 
{
  "ENAME": {"$like":"AX%"}
}
 
 
#### BETWEEN operator ($between)
(Supports string, dates, and numbers)
 
Numbers
 
{
  "SALARY": {"$between": [1000,2000]}
}
 
Dates
 
{
  "SALARY": {"$between": [{"$date":"1989-12-17T08:00:00Z"},{"$date":"1999-12-17T08:00:00Z"}]}
}
 
Strings
 
{
  "ENAME": {"$between": ["A","C"]}
}
 
Null Ranges ($lte equivalent) 
(Supported by numbers and dates only)

{
  "SALARY": {"$between": [null,2000]}
}
 
Null Ranges ($gte equivalent)
(Supported by numbers and dates only)

{
  "SALARY": {"$between": [1000,null]}
}
 
 
#### NULL operator ($null)
 
{
  "ENAME": {"$null": null}
}
 
#### NOT NULL operator ($notnull)
 
{
  "ENAME": {"$notnull": null}
}
 
 
#### AND operator ($and)
(Supports all operators, including $and and $or)
 
Column context delegation
(Operators inside $and will use the closest context defined in the JSON tree.)

{
  "SALARY": {"$and": [{"$gt": 1000},{"$lt":4000}]}
}
 
Column context override
(Example: salary greater than 1000 and name like S%) 

{
  "SALARY": {"$and": [{"$gt": 1000},{"ENAME": {"$like":"S%"}} ] }
}
 
Implicit and in columns
 
```
{
  "SALARY": [{"$gt": 1000},{"$lt":4000}] 
}
```
 
High order AND
(All first columns and or high order operators -- $and and $ors -- defined at the first level of the JSON will be joined and an implicit AND)
(Example: Salary greater than 1000 and name starts with S or T)
 
{
  "SALARY": {"$gt": 1000}, 
  "ENAME": {"$or": [{"$like":"S%"}, {"$like":"T%"}]} 
}
 
Invalid expression (operators $lt and $gt lack column context)
 
{
   "$and": [{"$lt": 5000},{"$gt": 1000}]
}
 
Valid alternatives for the previous invalid expression
 
{
   "$and": [{"SALARY": {"$lt": 5000}}, {"SALARY": {"$gt": 1000}}]
}
 
{
   "SALARY": [{"$lt": 5000},{"$gt": 1000}]
}
 
{
   "SALARY": {"$and": [{"$lt": 5000},{"$gt": 1000}]}
}


OR operator ($or)
(Supports all operators including $and and $or)
 
Column context delegation
(Operators inside $or will use the closest context defined in the JSON tree)

{
  "ENAME": {"$or": [{"$eq":"SMITH"},{"$eq":"KING"}]}
}
 
Column context override
(Example: name starts with S or salary greater than 1000)

{
  "SALARY": {"$or": [{"$gt": 1000},{"ENAME": {"$like":"S%"}} ] }
}

3.2.5.3 Auto PL/SQL

This section explains how PL/SQL is made available through HTTP(S) for Remote Procedure call (RPC).

The auto PL/SQL feature uses a standard to provide consistent encoding and data transfer in a stateless web service environment. Using this feature, you can enable Oracle Database stored PL/SQL functions and procedures at package level through ORDS, similar to how you enable the views and tables.

Auto Enabling PL/SQL Subprograms

ORDS supports auto enabling of the following PL/SQL objects, based on their catalog object identifier:
  • PL/SQL Procedure

  • PL/SQL Function

  • PL/SQL Package

The functions, and procedures within the PL/SQL package cannot be individually enabled as they are named objects within a PL/SQL package object. Therefore, the granularity level enables the objects at the package level. This granularity level enables to expose all of its public functions and procedures.

If you want to only enable a subset of functions and procedures, then you must create a separate delegate package and enable it to expose only that subset of functions and procedures.

Note:

Overloaded package functions and procedures are not supported.
3.2.5.3.1 Method and Content Type Supported for Auto Enabling PL/SQL Objects

This section discusses the method and content-type supported by this feature.

The auto enabling of the PL/SQL Objects feature supports POST as the HTTP method. In POST method, input parameters are encoded in the payload and output parameters are decoded from the response.

Note:

The standard data CRUD to HTTP method mappings are not applicable as this feature provides an RPC-style interaction.

The content-type supported is application/json.

3.2.5.3.2 Auto-Enabling the PL/SQL Objects

This section explains how to auto-enable the PL/SQL objects through ORDS.

You can enable the PL/SQL objects in one of the following ways:
3.2.5.3.2.1 Auto-Enabling Using the ORDS PL/SQL API

You can enable a PL/SQL object using the ORDS PL/SQL API.

To enable the PL/SQL package, use the ORDS PL/SQL API as shown in following sample code snippet:

BEGIN
  ords.enable_object(
    p_enabled => TRUE,
    p_schema => 'MY_SCHEMA',
    p_object => 'MY_PKG',
    p_object_type => 'PACKAGE',
    p_object_alias => 'my_pkg',
    p_auto_rest_auth => FALSE);
   commit;
END;
/

Example 3-1 Enabling the PL/SQL Function

To enable the PL/SQL function, use the ORDS PL/SQL API as shown in following sample code snippet:

BEGIN
  ords.enable_object(
    p_enabled => TRUE,
    p_schema => 'MY_SCHEMA',
    p_object => 'MY_FUNC',
    p_object_type => 'FUNCTION',
    p_object_alias => 'my_func',
    p_auto_rest_auth => FALSE);

    commit;
END;
/

Example 3-2 Enabling the PL/SQL Procedure

To enable the PL/SQL procedure, use the ORDS PL/SQL API as shown in following sample code snippet:

BEGIN
  ords.enable_object(
    p_enabled => TRUE,
    p_schema => 'MY_SCHEMA',
    p_object => 'MY_PROC',
    p_object_type => 'PROCEDURE',
    p_object_alias => 'my_proc',
    p_auto_rest_auth => FALSE);

    commit;
END;
/
3.2.5.3.2.2 Auto-Enabling the PL/SQL Objects Using SQL Developer

This section describes how to enable the PL/SQL objects using SQL Developer 4.2 and above.

To enable the PL/SQL objects (for example, package) using SQL Developer, perform the following steps:

Note:

You can now enable, packages, functions and procedures. However, the granularity of enabling is either at the whole package level, standalone function level, or at the standalone procedure level.
  1. In SQL Developer, right-click on a package as shown in the following figure:

    Figure 3-1 Selecting the Enable REST Service Option

    Description of Figure 3-1 follows
    Description of "Figure 3-1 Selecting the Enable REST Service Option"
  2. Select Enable RESTful Services to display the following wizard page:

    Figure 3-2 Auto Enabling the PL/SQL Package Object

    Description of Figure 3-2 follows
    Description of "Figure 3-2 Auto Enabling the PL/SQL Package Object"
    • Enable object: Enable this option (that is, enable REST access for the package).

    • Object alias: Accept registry_pkg for the object alias.

    • Authorization required: For simplicity, disable this option.

    • On the RESTful Summary page of the wizard, click Finish.

3.2.5.3.3 Generating the PL/SQL Endpoints

HTTP endpoints are generated dynamically per request for the enabled database objects. ORDS uses the connected database catalog to generate the endpoints using a query.

The following rules apply for all the database objects for generating the HTTP endpoints:

  • All names are converted to lowercase

  • An endpoint is generated if it is not already allocated

Stored Procedure and Function Endpoints

The function or procedure name is generated into the URL in the same way as tables and views in the same namesspace.

Example 3-3 Generating an Endpoint for the Stored Procedure

CREATE OR REPLACE PROCEDURE MY_SCHEMA.MY_PROC IS
BEGIN
  NULL;
END;
Following endpoint is generated:
http://localhost:8080/ords/my_schema/my_proc/

Example 3-4 Package Procedure and Function Endpoints

The package, function, and procedure endpoints are generated with package name as a parent. Endpoints for functions and procedures that are not overloaded or where the lowercase name is not already in use are generated.

If you have a package, MY_PKG as defined in the following code snippet:

CREATE OR REPLACE  PACKAGE MY_SCHEMA.MY_PKG AS
  PROCEDURE MY_PROC;
  FUNCTION MY_FUNC RETURN VARCHAR2;
  PROCEDURE MY_PROC2;
  PROCEDURE "my_proc2";
  PROCEDURE MY_PROC3(P1 IN VARCHAR);
  PROCEDURE MY_PROC3(P2 IN NUMBER);
END MY_PKG;
Then the following endpoints are generated:
http://localhost:8080/ords/my_schema/my_pkg/MY_PROC
http://localhost:8080/ords/my_schema/my_pkg/MY_FUNC

Note:

Endpoints for the procedure my_proc2 is not generated because its name is not unique when the name is converted to lowercase, and endpoints for the procedure my_proc3 is not generated because it is overloaded.
3.2.5.3.4 Resource Input Payload

The input payload is a JSON document with values adhering to the REST standard.

The payload should contain a name/value pair for each IN or IN OUT parameter as shown in the following code snippet:

{
  "p1": "abc",
  "p2": 123,
  "p3": null
}

Note:

Where there are no IN or IN OUT parameters, an empty JSON body is required as shown in the following code snippet:
{

}
ORDS uses the database catalog metadata to unmarshal the JSON payload into Oracle database types, which is ready to be passed to the database through JDBC.
3.2.5.3.5 Resource Payload Response

When the PL/SQL object is executed successfully, it returns a JSON body.

The JSON body returned, contains all OUT and IN OUT output parameter values. ORDS uses the database catalog metadata to marshal the execution of the result back into JSON as shown in the following code snippet:

{
  "p3" : "abc123",
  "p4" : 1
}
Where there are no OUT or IN OUT parameters, an empty JSON body is returned as shown in the following code snippet:
{

}
3.2.5.3.6 Function Return Value

The return value of functions do not have an associated name.

As the return value of functions do not have an associated name, the name "~ret" is used as shown in the following code snippet:
{
  "~ret" : "abc123"
}

3.2.6 Manually Creating RESTful Services Using SQL and PL/SQL

This section describes how to manually create RESTful Services using SQL and PL/SQL and shows how to use a JSON document to pass parameters to a stored procedure in the body of a REST request.

3.2.6.1 About Oracle REST Data Services Mechanisms for Passing Parameters

This section describes the main mechanisms that Oracle REST Data Services supports for passing parameters using REST HTTP to handlers that are written by the developer:
  • Using JSON to Pass Parameters

    You can use JSON in the body of REST requests, such as the POST or PUT method, where each parameter is a JSON name/value pair.

  • Using Route Patterns to Pass Parameters

    You can use route patterns for required parameters in the URI to specify parameters for REST requests such as the GET method, which does not have a body, and in other special cases.

  • Using Query Strings for Optional Parameters

    You can use query strings for optional parameters in the URI to specify parameters for REST requests, such as the GET method, which does not have a body, and in other special cases.

Prerequisite Setup Tasks To Be Completed Before Performing Tasks for Passing Parameters

This prerequisite setup information assumes you have completed steps 1 and 2 in Getting Started with RESTful Services section, where you have REST-enabled the ordstest schema and emp database table (Step 1) and created and tested the RESTful service from a SQL query (Step 2). You must complete these two steps before performing the tasks about passing parameters described in the subsections that follow.

3.2.6.1.1 Using JSON to Pass Parameters

This section shows how to use a JSON document to pass parameters to a stored procedure in the body of a REST request, such as POST or PUT method, where each parameter is a name/value pair. This operation performs an update on a record, which in turn returns the change to the record as an OUT parameter.

Perform the following steps:

  1. Note:

    The following stored procedure performs an update on an existing record in the emp table to promote an employee by changing any or all of the following: job, salary, commission, department number, and manager. The stored procedure returns the salary change as an OUT parameter.

    create or replace procedure promote ( l_empno IN number,  l_job IN varchar2,
               l_mgr IN number, l_sal IN number,  l_comm IN number,  l_deptno IN number,
               l_salarychange OUT number)
            is
                oldsalary   number;
            begin
                select nvl(e.sal, 0)into oldsalary FROM emp e
                        where e.empno = l_empno;
                update emp e set
                    e.job = nvl(l_job, e.job),
                    e.mgr = nvl(l_mgr, e.mgr),
                    e.sal = nvl(l_sal, e.sal),
                    e.comm = nvl(l_comm, e.comm),
                    e.deptno = nvl(l_deptno, e.deptno)
                            where e.empno = l_empno;
                l_salarychange := nvl(l_sal, oldsalary) - oldsalary;
            end;
    
    As a privileged ordstest user, connect to the ordstest schema and create the promote stored procedure.
  2. Perform the following steps to setup a handler for a PUT request on the emp resource to pass parameters in the body of the PUT method in a JSON document to the promote stored procedure.
    1. Using Oracle SQL Developer, in the REST Development section, right click on the emp template and select Add Handler for the PUT method.
    2. In the Create Resource Handler dialog, click the green plus symbol to add the MIME type application/json and then click Apply to send it a JSON document in the body of the PUT method.
    3. Using the SQL Worksheet, add the following anonymous PL/SQL block: begin promote (:l_empno, :l_job, :l_mgr, :l_sal, :l_comm, :l_deptno, :l_salarychange); end; as shown in the following figure.

      Figure 3-3 Adding an Anonymous PL/SQL Block to the Handler for the PUT Method

      Description of Figure 3-3 follows
      Description of "Figure 3-3 Adding an Anonymous PL/SQL Block to the Handler for the PUT Method"
    4. Click the Parameters tab to set the Bind Parameter as l_salarychange , the Access Method as an OUT parameter, the Source Type as RESPONSE, and Data Type as INTEGER as shown in the following figure. This is the promote procedure’s output which is an integer value equal to the change in salary in a JSON name/value format.

      Figure 3-4 Setting the Bind Parameter l_salarychange to Pass for the PUT Method

      Description of Figure 3-4 follows
      Description of "Figure 3-4 Setting the Bind Parameter l_salarychange to Pass for the PUT Method"
    5. Click the Details tab to get the URL to call as shown in the Examples section of the following figure. Copy this URL to your clipboard.

      Figure 3-5 Obtaining the URL to Call from the Details Tab

      Description of Figure 3-5 follows
      Description of "Figure 3-5 Obtaining the URL to Call from the Details Tab"
    6. Right click on the test module to upload the module. Do not forget this step.
  3. To test the RESTful service, execute the following cURL command in the command prompt:curl -i -H "Content-Type: application/json" -X PUT -d "{ \"l_empno\" : 7499, \"l_sal\" : 9999, \"l_job\" : \"Director\", \"l_comm\" : 300}

    Note:

    You can also use any REST client available to test the RESTful service.
    The cURL command returns the following response:
    HTTP/1.1 200 OK 
    Content-Type: application/json Transfer-Encoding: chunked 
    {"salarychange":8399}
    
  4. In SQL Developer SQL Worksheet, perform the following SELECT statement on the emp table: SELECT * from emp to see that the PUT method was executed, then select the Data tab to display the records for the EMP table.

    Figure 3-6 Displaying the Results from a SQL Query to Confirm the Execution of the PUT Method

    Description of Figure 3-6 follows
    Description of "Figure 3-6 Displaying the Results from a SQL Query to Confirm the Execution of the PUT Method"

Note:

  • All parameters are optional. If you leave out a name/value pair for a parameter in your JSON document, the parameter is set to NULL

  • The name/value pairs can be arranged in any order in the JSON document. JSON allows much flexibility in this regard in the JSON document.

  • Only one level of JSON is supported. You can not have nested JSON objects or arrays.

3.2.6.1.2 Using Route Patterns to Pass Parameters
This section describes how to use route patterns in the URI to specify parameters for REST requests, such as with the GET method, which does not have a body.

First create a GET method handler for a query on the emp table that has many bind variables. These steps use a route pattern to specify the parameter values that are required.

Perform the following steps to use a route pattern to send a GET method with some required parameter values:

  1. In SQL Developer, right click on the test module and select Add Template to create a new template that calls emp; however, in this case the template definition includes a route pattern for the parameters or bind variables that is included in the URI rather than in the body of the method. To define the required parameters, use a route pattern by specifying a /: before the job and deptno parameters. For example, for the URI pattern, enter: emp/:job/:deptno as shown in the following figure.

    Figure 3-7 Creating a Template Definition to Include a Route Pattern for Some Parameters or Bind Variables

    Description of Figure 3-7 follows
    Description of "Figure 3-7 Creating a Template Definition to Include a Route Pattern for Some Parameters or Bind Variables"
  2. Click Next to go to REST Data Services — Step 2 of 3, and click Next to go to REST Data Services — Step 3 of 3, then click Finish to complete the template.
  3. Right click on the emp/:job/:deptno template and select Add Handler for the GET method.
  4. Right click on the GET method to open the handler.
  5. Add the following query to the SQL Worksheet: select * from emp e where e.job = :job and e.deptno = :deptno and e.mgr = NVL (:mgr, e.mgr) and e.sal = NVL (:sal, e.sal); as also shown in the following figure.

    Figure 3-8 Adding a SQL Query to the Handler

    Description of Figure 3-8 follows
    Description of "Figure 3-8 Adding a SQL Query to the Handler"
  6. Click the Details tab to get the URL to call. Copy this URL to your clipboard.
  7. Right click on the test module to upload the module. Do not forget this step.
  8. Test the REST endpoint. In a web browser enter the URL:http://localhost:8080/ords/ordstest/test/emp/SALESMAN/30 as shown in the following figure.

    Figure 3-9 Using Browser to Show the Results of Using a Route Pattern to Send a GET Method with Some Required Parameter Values

    Description of Figure 3-9 follows
    Description of "Figure 3-9 Using Browser to Show the Results of Using a Route Pattern to Send a GET Method with Some Required Parameter Values"

The query returns 3 records for the salesmen named Ward, Martin, and Turner.

See Also:

To learn more about Route Patterns see this document in the ORDS distribution at docs/javadoc/plugin-api/route-patterns.html and this document Oracle REST Data Services Route Patterns

3.2.6.1.3 Using Query Strings for Optional Parameters

This section describes how to use query strings in the URI to specify parameters for REST requests like the GET method, which does not have a body. You can use query strings for any of the other optional bind variables in the query as you choose.

The syntax for using query strings is: ?parm1=value1&parm2=value2 … &parmN=valueN.

For example, to further filter the query: http://localhost:8080/ords/ordstest/test/emp/SALESMAN/30, to use a query string to send a GET method with some parameter name/value pairs, select employees whose mgr (manager) is 7698 and whose sal (salary) is 1500 by appending the query string ?mgr=7698&sal=1500 to the URL as follows: http://localhost:8080/ords/ordstest/test/emp/SALESMAN/30?mgr=7698&sal=1500.

To test the endpoint, in a web browser enter the following URL: http://localhost:8080/ords/ordstest/test/emp/SALESMAN/30?mgr=7698&sal=1500 as shown in the following figure:

Figure 3-10 Using Browser to Show the Results of Using a Query String to Send a GET Method with Some Parameter Name/Value Pairs

Description of Figure 3-10 follows
Description of "Figure 3-10 Using Browser to Show the Results of Using a Query String to Send a GET Method with Some Parameter Name/Value Pairs"

The query returns one record for the salesman named Turner in department 30 who has a salary of 1500 and whose manager is 7698.

Note the following points:
  • It is a good idea to URL encode your parameter values. This may not always be required; however, it is the safe thing to do. This prevents the Internet from transforming something, for example, such as a special character in to some other character that may cause a failure. Your REST client may provide this capability or you can search the Internet for the phrase url encoder to find tools that can do this for you.

  • Never put a backslash at the end of your parameter list in the URI; otherwise, you may get a 404 Not Found error.

See Also:

To gain more experience using JSON to pass parameter values, see Lab 4 of the ORDS Oracle By Example (OBE) and Database Application Development Virtual Image.

3.2.6.2 Using SQL/JSON Database Functions with ORDS

This section describes how to use the SQL/JSON database functions available in Oracle Database 12c Release 2 (12.2) to map the nested JSON objects to and from the hierarchical relational tables.

3.2.6.2.1 Inserting Nested JSON Objects into Relational Tables

This section explains how to insert JSON objects with nested arrays into multiple, hierarchical relational tables.

The two key technologies used to implement this functionality are as follows:

  • The :body bind variable that ORDS provides to deliver JSON and other content in the body of POST and other REST calls into PL/SQL REST handlers

  • JSON_TABLE and other SQL/JSON operators provided in Oracle Database 12c Release 2 (12.2)

Some of the advantages of using these technologies for inserting data into relational tables are as follows:

  • Requirements for implementing this functionality are very minimal. For example, installation of JSON parser software is not required

  • You can use simple, declarative code that is easy to write and understand when the JSON to relational mapping is simple

  • Powerful and sophisticated capabilities to handle more complex mappings. This includes:

    • Mechanisms for mapping NULLS and boolean values

    • Sophisticated mechanisms for handling JSON. JSON evolves over time. Hence, the mapping code must be able to handle both the older and newer versions of the JSON documents.

      For example, simple scalar values may evolve to become JSON objects containing multiple scalars or nested arrays of scalar values or objects. SQL/JSON operators that return the scalar value can continue to work even when the simple scalar is embedded within these more elaborate structures. A special mechanism, called the Ordinality Column, can be used to determine the structure from where the value was derived.

See Also:

The following pages for more information on JSON_TABLE and other SQL/JSON operators and on Ordinality Column mechanism:
3.2.6.2.1.1 Usage of the :body Bind Variable

This section provides some useful tips for using the :body bind variable.

Some of the useful tips for using the :body bind variable are as follows:
  • The :body bind variable can be accessed, or de-referenced, only once. Subsequent accesses return a NULL value. So, you must first assign the :body bind variable to the local L_PO variable before using it in the two JSON_Table operations.

  • The :body bind variable is a BLOB datatype and you can assign it only to a BLOB variable.

    Note:

    Since L_PO is a BLOB variable, you must use the FORMAT JSON phrase after the expression in the JSON_TABLE function. section for more information.

    The :body bind variable can be used with other types of data such as image data.

See Also:

3.2.6.2.1.2 Example of JSON Purchase Order with Nested LineItems

This section shows an example that takes the JSON Purchase Order with Nested LineItems and inserts it into a row of the PurchaseOrder table and rows of the LineItem table.

Example 3-5 Nested JSON Purchase Order with Nested LineItems

{"PONumber"        : 1608,
  "Requestor"      : "Alexis Bull",
  "CostCenter"     : "A50",
  "Address"        : {"street"  : "200 Sporting Green",
                       "city"    : "South San Francisco",
                       "state"   : "CA",
                       "zipCode" : 99236,
                       "country" : "United States of America"},
  "LineItems"      : [ {"ItemNumber" : 1,
                         "Part"       : {"Description" : "One Magic Christmas",
                                         "UnitPrice"   : 19.95,
                                         "UPCCode"     : 1313109289},
                         "Quantity"   : 9.0},
                       {"ItemNumber" : 2,
                        "Part"       : {"Description" : "Lethal Weapon",
                                        "UnitPrice"   : 19.95,
                                        "UPCCode"     : 8539162892},
                        "Quantity"   : 5.0}]}'
3.2.6.2.1.3 Table Definitions for PurchaseOrder and LineItems Tables

This section provides definitions for the PurchaseOrder and LineItem tables.

The definitions for the PurchaseOrder and the LineItems tables are as follows:

CREATE TABLE PurchaseOrder (
     PONo NUMBER (5),
     Requestor VARCHAR2 (50),
     CostCenter VARCHAR2 (5),
     AddressStreet VARCHAR2 (50),
     AddressCity VARCHAR2 (50), 
     AddressState VARCHAR2 (2),
     AddressZip VARCHAR2 (10),
     AddressCountry VARCHAR2 (50),
     PRIMARY KEY (PONo));

CREATE TABLE LineItem (
     PONo NUMBER (5),
     ItemNumber NUMBER (10),
     PartDescription VARCHAR2 (50), 
     PartUnitPrice NUMBER (10),
     PartUPCCODE NUMBER (10), 
     Quantity NUMBER (10),
     PRIMARY KEY (PONo,ItemNumber));
3.2.6.2.1.4 PL/SQL Handler Code for a POST Request

This section gives an example PL/SQL handler code for a POST request. The handler code is used to insert a purchase order into a row of the PurchaseOrder table and rows of the LineItem table.

Example 3-6 PL/SQL Handler Code Used for a POST Request

Declare
  L_PO     BLOB;
 
Begin
  L_PO := :body;
 
INSERT INTO PurchaseOrder
      SELECT * FROM json_table(L_PO  FORMAT JSON, '$'
         COLUMNS (
           PONo            Number    PATH '$.PONumber',
           Requestor       VARCHAR2  PATH '$.Requestor',
           CostCenter      VARCHAR2  PATH '$.CostCenter',
           AddressStreet   VARCHAR2  PATH '$.Address.street',
           AddressCity     VARCHAR2  PATH '$.Address.city',
           AddressState    VARCHAR2  PATH '$.Address.state',
           AddressZip      VARCHAR2  PATH '$.Address.zipCode',
           AddressCountry  VARCHAR2  PATH '$.Address.country'));
 
INSERT INTO LineItem 
SELECT * FROM json_table(L_PO  FORMAT JSON, '$'
         COLUMNS (
           PONo  Number PATH '$.PONumber',
           NESTED             PATH '$.LineItems[*]'
             COLUMNS (
               ItemNumber        Number   PATH '$.ItemNumber',
               PartDescription   VARCHAR2   PATH '$.Part.Description',
               PartUnitPrice     Number   PATH '$.Part.UnitPrice',
               PartUPCCode       Number   PATH '$.Part.UPCCode',
               Quantity          Number   PATH '$.Quantity')));
commit;
end;
3.2.6.2.1.5 Creating the REST API Service to Invoke the Handler

This section explains how to create the REST API service to invoke the handler, using the Oracle REST Data Services.

To setup the REST API service, a URI is defined to identify the resource the REST calls will be operating on.  The URI is also used by Oracle REST Data Services to route the REST HTTP calls to specific handlers. The general format for the URI is as follows:

<server>:<port>/ords/<schema>/<module>/<template>/<parameters>

Here, <server>:<port> is where the Oracle REST Data Service is installed. For testing purposes, you can use demo and test in place of module and template respectively in the URI.  Modules are used to group together related templates that define the resources the REST API will be operating upon.

To create the REST API service, use one of the following methods:

  • Use the ORDS PL/SQL API to define the REST service and a handler for the POST insert. Then connect to the jsontable schema on the database server that contains the PurchaseOrder and LineItem tables.

    Note:

    JSON_TABLE and other SQL/JSON operators use single quote so these must be escaped. For example, every single quote (’) must be replaced with double quotes (“).
  • Use the ORDS REST Development pane in SQL Developer to define the REST service.

3.2.6.2.1.6 Defining the REST Service and Handler using ORDS PL/SQL API

This section shows how to define the REST Service and Handler for the POST insert using the Oracle REST Data Services (ORDS) PL/SQL API.

You can alternatively use the ORDS REST development pane in SQL Developer to create the modules, templates and handlers.

BEGIN
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'ORDSTEST',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'ordstest',
      p_auto_rest_auth      => FALSE);    

  ORDS.DEFINE_MODULE(
      p_module_name    => 'demo',
      p_base_path      => '/demo/',
      p_items_per_page =>  25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);      
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'demo',
      p_pattern        => 'test',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'demo',
      p_pattern        => 'test',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_items_per_page =>  0,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => ' 
declare
    L_PO BLOB := :body;
begin

INSERT INTO PurchaseOrder
      SELECT * FROM json_table(L_PO  FORMAT JSON, ''$''
         COLUMNS (
           PONo                  Number           PATH ''$.PONumber'',
           Requestor            VARCHAR2  PATH ''$.Requestor'',
           CostCenter          VARCHAR2   PATH ''$.CostCenter'',
           AddressStreet      VARCHAR2  PATH ''$.Address.street'',
           AddressCity         VARCHAR2  PATH ''$.Address.city'',
           AddressState        VARCHAR2  PATH ''$.Address.state'',
           AddressZip           VARCHAR2  PATH ''$.Address.zipCode'',
           AddressCountry    VARCHAR2  PATH ''$.Address.country''));

INSERT INTO LineItem
SELECT * FROM json_table(L_PO  FORMAT JSON, ''$''
         COLUMNS (
           PONo  Number PATH ''$.PONumber'',
           NESTED                      PATH ''$.LineItems[*]''
             COLUMNS (
               ItemNumber       Number      PATH ''$.ItemNumber'',
               PartDescription  VARCHAR2    PATH ''$.Part.Description'',
               PartUnitPrice      Number     PATH ''$.Part.UnitPrice'',
               PartUPCCode     Number     PATH ''$.Part.UPCCode'',
               Quantity              Number    PATH ''$.Quantity'')));

commit;
end;'
      );


  COMMIT; 
END;
3.2.6.2.2 Generating Nested JSON Objects from Hierachical Relational Data

This section explains how to query the relational tables in hierarchical (parent/child) relationships and return the data in a nested JSON format using the Oracle REST Data Services.

The two key technologies used to implement this functionality are as follows:

  • The new SQL/JSON functions available with Oracle Database 12c Release 2 (12.2). You can use json_objects for generating JSON objects from the relational tables, and json_arrayagg, for generating nested JSON arrays from nested (child) relational tables.

  • The ORDS media source type used for enabling the REST service handler to execute a SQL query that in turn returns the following types of data:

    • The HTTP Content-Type of the data, which in this case is application/json

    • The JSON data returned by the json_object

Some of the advantages of using this approach are as follows:

  • Requirements for implementing this functionality is very minimal. For example, installation of JSON parser software is not required.

  • Simple, declarative coding which is easy to write and understand which makes the JSON objects to relational tables mapping simple.

  • Powerful and sophisticated capabilities to handle more complex mappings. This includes mechanisms for mapping NULLS and boolean values.

    For example, a NULL in the Oracle Database can be converted to either the absence of the JSON element or to a JSON NULL value. The Oracle Database does not store Boolean types but the SQL/JSON functions allow string or numeric values in the database to be mapped to Boolean TRUE or FALSE values.

3.2.6.2.2.1 Example to Generate Nested JSON Objects from the  Hierachical Relational Tables

This section describes how to query or GET the data we inserted into the PurchaseOrder and LineItem relational tables in the form of nested JSON purchase order.

Example 3-7 GET Handler Code using Oracle REST Data Services Query on Relational Tables for Generating a Nested JSON object

SELECT 'application/json', json_object('PONumber' VALUE po.PONo,
       'Requestor' VALUE po.Requestor,
       'CostCenter' VALUE po.CostCenter,
       'Address' VALUE
           json_object('street' VALUE po.AddressStreet,
                 'city' VALUE po.AddressCity,
                 'state' VALUE po.AddressState,
                 'zipCode' VALUE po.AddressZip,
                 'country' VALUE po.AddressCountry),
        'LineItems' VALUE (select json_arrayagg(
            json_object('ItemNumber' VALUE li.ItemNumber,
                   'Part' VALUE 
                     json_object('Description' VALUE li.PartDescription,
                                 'UnitPrice' VALUE li.PartUnitPrice,
                                 'UPCCode' VALUE li.PartUPCCODE),
                   'Quantity' VALUE li.Quantity))
                   FROM LineItem li WHERE po.PONo = li.PONo))
             FROM PurchaseOrder po
               WHERE po.PONo = :id
3.2.6.2.2.2 ORDS PL/SQL API Calls for Defining Template and GET Handler

This section provides an example of ORDS PL/SQL API call for creating a new template in the module created.

Example 3-8 ORDS PL/SQL API Call for Creating a New test/:id Template and GET Handler in the demo Module

Begin
ords.define_template(
 p_module_name => 'demo',
 p_pattern => 'test/:id');
 
ords.define_handler(
 p_module_name => 'demo',
 p_pattern => 'test/:id',
 p_method  => 'GET',
 p_source_type => ords.source_type_media,
 p_source => '
 
   SELECT ''application/json'', json_object(''PONumber'' VALUE po.PONo,
          ''Requestor'' VALUE po.Requestor,
          ''CostCenter'' VALUE po.CostCenter,
          ''Address'' VALUE
              json_object(''street'' VALUE po.AddressStreet,
                          ''city'' VALUE po.AddressCity,
                          ''state'' VALUE po.AddressState,
                          ''zipCode'' VALUE po.AddressZip,
                          ''country'' VALUE po.AddressCountry),
          ''LineItems'' VALUE (select json_arrayagg(
              json_object(''ItemNumber'' VALUE li.ItemNumber,
                     ''Part'' VALUE
                       json_object(''Description'' VALUE li.PartDescription,
                                   ''UnitPrice'' VALUE li.PartUnitPrice,
                                   ''UPCCode'' VALUE li.PartUPCCODE),
                      ''Quantity'' VALUE li.Quantity))
                      FROM LineItem li WHERE po.PONo = li.PONo))
               FROM PurchaseOrder po
                 WHERE po.PONo = :id '
   );
 
Commit;
End;  
3.2.6.2.3 Testing the RESTful Services

This section shows how to test the POST and GET RESTful Services to access the Oracle database and get the results in a JSON format.

This section includes the following topics:

3.2.6.2.3.1 Insertion of JSON Object into the Database

This section shows how to test insertion of JSON purchase order into the database.

URI Pattern: http://<HOST>:<PORT>/ords/<SchemaAlias>/<module>/<template>

Example:

Method: POST

URI Pattern: http://localhost:8080/ords/ordstest/demo/test/

To test the RESTful service, create a file such as po1.json with the following data for PONumber 1608 :
{"PONumber"        : 1608,
 "Requestor"       : "Alexis Bull",
 "CostCenter"      : "A50",
 "Address"         : {"street"  : "200 Sporting Green",
                                  "city"    : "South San Francisco",
                                  "state"   : "CA",
                                  "zipCode" : 99236,
                                  "country" : "United States of America"},
      "LineItems"  : [ {"ItemNumber" : 1,
                          "Part"     : {"Description" : "One Magic Christmas",
                                        "UnitPrice"   : 19.95,
                                        "UPCCode"     : 1313109289},
                                        "Quantity"   : 9.0},
                                        {"ItemNumber" : 2,
                                          "Part"       : {"Description" : "Lethal Weapon",
                                                          "UnitPrice"   : 19.95,
                                                          "UPCCode"     : 8539162892},
                                          "Quantity"   : 5.0}]}'
Then, execute the following cURL command in the command prompt:
curl -i -H "Content-Type: application/json" -X POST -d @po1.json "http://localhost:8080/ords/ordstest/demo/test/"
The cURL command returns the following response:
HTTP/1.1 200 OK 
Transfer-Encoding: chunked
3.2.6.2.3.2 Generating JSON Object from the Database

This section shows the results of a GET method to fetch the JSON object from the database..

Method: GET

URI Pattern: http://<HOST>:<PORT>/ords/<SchemaAlias>/<module>/<template>/<parameters>

Example:

To test the RESTful service, in a web browser, enter the URL http://localhost:8080 /ords/ordstest/demo/test/1608 as shown in the following figure:

Figure 3-11 Generating Nested JSON Objects

Description of Figure 3-11 follows
Description of "Figure 3-11 Generating Nested JSON Objects"

3.2.7 About Working with Dates Using ORDS

ORDS enables developers to create REST interfaces to Oracle Database, Oracle Database 12c JSON Document Store, and Oracle NoSQL Database as quickly and easily as possible. When working with Oracle Database, developers can use the AutoREST feature for tables or write custom modules using SQL and PL/SQL routines for more complex operations.

ORDS uses the RFC3339 standard for encoding dates in strings. Typically, the date format used is dd-mmm-yyyy, for example, 15-Jan-2017. ORDS automatically converts JSON strings in the specified format to Oracle date data types when performing operations such as inserting or updating values in Oracle Database. When converting back to JSON strings, ORDS automatically converts Oracle date data types to the string format.

Note:

Oracle Database supports a date data type while JSON does not support a date data type.

This section includes the following topics:

See Also:

The following page for more information, including details on how time and time zones are handled https://jsao.io/2016/10/working-with-dates-using-ords/

3.2.7.1 About Datetime Handling with Oracle REST Data Services

As data arrives from a REST request, Oracle REST Data Services (ORDS) may parse ISO 8601 strings and convert them to the TIMESTAMP data type in Oracle Database. This occurs with AutoREST (POST and PUT) as well as with bind variables in custom modules. Remember that TIMESTAMP does not support time zone related components, so the DATETIME value is set to the time zone ORDS uses during the conversion process.

When constructing responses to REST requests, ORDS converts DATETIME values in Oracle Database to ISO 8601 strings in Zulu. This occurs with AutoREST (GET) and in custom modules that are mapped to SQL queries (GET). In the case of DATE and TIMESTAMP data types, which do not have time zone related components, the time zone is assumed to be that in which ORDS is running and the conversion to Zulu is made from there.

Here are some general recommendations when working with ORDS for REST (that is, not APEX):
  • Ensure that ORDS uses the appropriate time zone as per the data in the database (for example, the time zone you want dates going into the database).

  • Do not alter NLS settings (that is, the time_zone) mid-stream.

Note that while ISO 8601 strings are mentioned, ORDS actually supports strings. RFC3339 strings are a conformant subset of ISO 8601 strings. The default format returned by JSON.stringify(date) is supported.

WARNING:

It is important to keep the time zone that ORDS uses in sync with the session time zone to prevent issues with implicit data conversion to TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE. ORDS does this automatically by default but developers can change the session time zone with an ALTER SESSION statement.

See Aslo:

RFC3339

3.2.7.2 About Setting the Time Zone that ORDS Uses

When Oracle REST Data Services (ORDS) is started, the JVM it runs in obtains and caches the time zone ORDS uses for various time zone conversions. By default, the time zone is obtained from the operating system (OS), so an easy way to change the time zone ORDS uses is to change the time zone of the OS and then restart ORDS or the application server on which it is running. Of course, the instructions for changing the time zone vary by the operating system.

If for any reason you do not want to use the same time zone as the OS, it is possible to override the default using the Java environment variable Duser.timezone. Exactly how that variable is set depends on whether you are running in standalone mode or in a Java application server. The following topics show some examples.

Standalone Mode

When running ORDS in standalone mode, it is possible to set Java environment variables by specifying them as command line options before the -jar option.

Example 3-9 Setting the Duser.timezone Java Environment Variable in Standalone Mode

The following code example shows how to set the timezone in standalone mode on the command line.

$ java -Duser.timezone=America/New_York -jar ords.war standalone 

Java Application Server — Tomcat 8

In a Java application server, Tomcat 8, and possibly earlier and later versions too, it is possible to set the time zone using the environment variable CATALINA_OPTS. The recommended way to do this is not to modify the CATALINA_BASE/bin/catalina.sh directly, but instead to set environment variables by creating a script named setenv.sh in CATALINA_BASE/bin.

Example 3-10 Setting the Duser.timezone Java Environment Variable in a Java Application Server

The following code example shows the contents of the setenv.sh script for setting the timezone in a Java Application server — Tomcat 8.

CATALINA_TIMEZONE="-Duser.timezone=America/New_York"
CATALINA_OPTS="$CATALINA_OPTS $CATALINA_TIMEZONE

3.2.7.3 Exploring the Sample RESTful Services in Application Express (Tutorial)

Oracle highly recommends to develop ORDS application using SQL Developer because it supports the most recent ORDS releases, that is, 3.0.X. Application Express provides a tutorial that is useful for learning some basic concepts of REST and ORDS. However, the tutorial uses the earlier ORDS releases, that is, 2.0.X. Following are some of the useful tips discussed on how to use the tutorial:

If your Application Express instance is configured to automatically add the sample application and sample database objects to workspaces, then a sample resource module named: oracle.example.hr will be visible in the list of Resource Modules. If that resource module is not listed, then you can click the Reset Sample Data task on the right side of the RESTful Services Page to create the sample resource module.

  1. Click on oracle.example.hr to view the Resource Templates and Resource Handlers defined within the module. Note how the module has a URI prefix with the value: hr/. This means that all URIs serviced by this module will start with the characters hr/.
  2. Click on the resource template named employees/{id}. Note how the template has a URI Template with the value: employees/{id}. This means that all URIs starting with hr/employees/ will be serviced by this Resource Template.

    The HTTP methods supported by a resource template are listed under the resource template. In this case, the only supported method is the GET method.

  3. Click on the GET Resource Handler for hr/employees/{id} to view its configuration.

    The Source Type for this handler is Query One Row. This means that the resource is expected to be mapped to a single row in the query result set. The Source for this handler is:

    select * from emp 
             where empno = :id
    

    Assuming that the empno column is unique, the query should only produce a single result (or no result at all if no match is found for :id). To try it out, press the Test button. The following error message should be displayed:

    400 - Bad Request - Request path contains unbound parameters: id

    If you look at the URI displayed in the browser, it will look something like this:

    https://server:port/ords/workspace/hr/employees/{id}
    

    where:

    • server is the DNS name of the server where Oracle Application Express is deployed

    • port is the port the server is listening on

    • workspace is the name of the Oracle Application Express workspace you are logged into

    Note the final part of the URI: hr/employees/{id}. The error message says that this is not a valid URI, the problem is that you did not substitute in a concrete value for the parameter named {id}. To fix that, press the browser Back button, then click Set Bind Variables.

  4. For the bind variable named :id, enter the value 7369, and press Test.

    A new browser window appears displaying the following JSON (JavaScript Object Notation):

    {
     "empno":7369,
     "ename":"SMITH",
     "job":"CLERK",
     "mgr":7902,
     "hiredate":"1980-12-17T08:00:00Z",
     "sal":800,
     "deptno":20
    }
    

    Note also the URI displayed in the browser for this resource:

    https://server:port/ords/workspace/hr/employees/7369
    

    The {id} URI Template parameter is bound to the SQL :id bind variable, and in this case it has been given the concrete value of 7369, so the query executed by the RESTful Service becomes:

    select * from emp
              where empno = 7369
    

    The results of this query are then rendered as JSON as shown above.

    Tip:

    Reading JSON can be difficult. To make it easier to read, install a browser extension that pretty prints the JSON. For example, Mozilla Firefox and Google Chrome both have extensions:

    Now see what happens when you enter the URI of a resource that does not exist.

  5. On the Set Bind Variables page, change the value of :id from 7369 to 1111, and press Test.

    As before, a new window pops up, but instead of displaying a JSON resource, it displays an error message reading:

    404 - Not Found
    

    This is the expected behavior of this handler: when a value is bound to :id that does not exist in the emp table, the query produces no results and consequently the standard HTTP Status Code of 404 - Not Found is returned.

    So, you have a service that will provide information about individual employees, if you know the ID of an employee, but how do you discover the set of valid employee ids?

  6. Press Cancel to return to the previous page displaying the contents of the Resource Module.
  7. Click on the template named employees/.

    The following steps look at the resource it generates, and later text will help you understand its logic.

  8. Click on the GET handler beneath employees/, and click Test.

    A resource similar to the following is displayed (If you haven't already done so, now would be a good time to install a JSON viewer extension in your browser to make it easier to view the output):

    {         
     "next": 
      {"$ref": 
        "https://server:port/ords/workspace/hr/employees/?page=1"},
     "items": [ 
      {
       "uri": 
        {"$ref": 
          "https://server:port/ords/workspace/hr/employees/7369"},
       "empno": 7369,
       "ename": "SMITH"
      }, 
      {
       "uri": 
        {"$ref": 
          "https://server:port/ords/workspace/hr/employees/7499"},
       "empno": 7499,
       "ename": "ALLEN"
      },
      ...
      {
       "uri": 
        {"$ref": 
          "https://server:port/ords/workspace/hr/employees/7782"},
       "empno": 7782,
       "ename": "CLARK"
      }     
     ]
    }
    

    This JSON document contains a number of things worth noting:

    • The first element in the document is named next and is a URI pointing to the next page of results. (An explanation of how paginated results are supported appears in later steps)

    • The second element is named items and contains a number of child elements. Each child element corresponds to a row in the result set generated by the query.

    • The first element of each child element is named uri and contains a URI pointing to the service that provides details of each employee. Note how the latter part of the URI matches the URI Template: employees/{id}. In other words, if a client accesses any of these URIs, the request will be serviced by the employees/{id} RESTful service previously discussed.

    So, this service addresses the problem of identifying valid employee IDs by generating a resource that lists all valid employee resources. The key thing to realize here is that it does not do this by just listing the ID value by itself and expecting the client to be able to take the ID and combine it with prior knowledge of the employees/{id} service to produce an employee URI; instead, it lists the URIs of each employee.

    Because the list of valid employees may be large, the service also breaks the list into smaller pages, and again uses a URI to tell the client where to find the next page in the results.

    To see at how this service is implemented, continue with the next steps.

  9. Press the Back button in your browser to return to the GET handler definition.

    Note the Source Type is Query, this is the default Source Type, and indicates that the resource can contain zero or more results. The Pagination Size is 7, which means that there will be seven items on each page of the results. Finally, the Source for the handler looks like this:

    select empno "$uri", empno, ename from (
     select emp.*, 
            row_number() over (order by empno) rn 
            from emp
     ) tmp 
     where 
      rn between :row_offset and :row_count
     
    

    In this query:

    • The first line states that you want to return three columns. The first column is the employee id: empno, but aliased to a column name of $uri (to be explained later), the second column is again the employee ID, and the third column is the employee name, ename.

    • Columns in result sets whose first character is $ (dollar sign) are given special treatment. They are assumed to denote columns that must be transformed into URIs, and these are called Hyperlink Columns. Thus, naming columns with a leading $ is a way to generate hyperlinks in resources.

      When a Hyperlink Column is encountered, its value is prepended with the URI of the resource in which the column is being rendered, to produce a new URI. For example, recall that the URI of this service is https://server:port/ords/workspace/hr/employees/. If the value of empno in the first row produced by the this service's query is 7369, then the value of $uri becomes: https://server:port/ords/workspace/hr/employees/7369.

    • JSON does not have a URI data type, so a convention is needed to make it clear to clients that a particular value represents a URI. Oracle REST Data Services uses the JSON Reference proposal, which states that any JSON object containing a member named $ref, and whose value is a string, is a URI. Thus, the column: $uri and its value: https://server:port/ords/workspace/hr/employees/7369 is transformed to the following JSON object:

      {"uri": 
           {"$ref": 
            "https://server:port/ords/workspace/hr/employees/7369"
           }
          } 
      
    • The inner query uses the row_number() analytical function to count the number of rows in the result set, and the outer WHERE clause constrains the result set to only return rows falling within the desired page of results. Oracle REST Data Services defines two implicit bind parameters, :row_offset and :row_count, that always contain the indicies of the first and last rows that should be returned in a given page's results.

      For example, if the current page is the first page and the pagination size is 7, then the value of :row_offset will be 1 and the value of :row_count will be 7.

    To see a simpler way to do both hyperlinks and paged results, continue with the following steps.

  10. Click on the GET handler of the employeesfeed/ resource template.

    Note that the Source Type of this handler is Feed and Pagination Size is 25.

  11. Change the pagination size to 7, and click Apply Changes.

    The Source of the handler is just the following:

    select empno, ename from emp 
                        order by deptno, ename
    

    As you can see, the query is much simpler than the previous example; however, if you click Test, you will see a result that is very similar to the result produced by the previous example.

    • The Feed Source Type is an enhanced version of the Query Source Type that automatically assumes the first column in a result set should be turned into a hyperlink, eliminating the need to alias columns with a name starting with $. In this example, the empno column is automatically transformed into a hyperlink by the Feed Source Type.

    • This example demonstrates the ability of Oracle REST Data Services to automatically paginate result sets if a Pagination Size of greater than zero is defined, and the query does not explicitly dereference the :row_offset or :row_count bind parameters. Because both these conditions hold true for this example, Oracle REST Data Services enhances the query, wrapping it in clauses to count and constrain the number and offset of rows returned. Note that this ability to automatically paginate results also applies to the Query Source Type.

    See Also:

    JSON Reference

3.3 Configuring Secure Access to RESTful Services

This section describes how to configure secure access to RESTful Services

RESTful APIs consist of resources, each resource having a unique URI. A set of resources can be protected by a privilege. A privilege defines the set of roles, at least one of which an authenticated user must possess to access a resource protected by a privilege.

Configuring a resource to be protected by a particular privilege requires creating a privilege mapping. A privilege mapping defines a set of patterns that identifies the resources that a privilege protects.

Topics:

3.3.1 Authentication

Users can be authenticated through first party cookie-based authentication or third party OAuth 2.0-based authentication

Topics:

3.3.1.1 First Party Cookie-Based Authentication

A first party is the author of a RESTful API. A first party application is a web application deployed on the same web origin as the RESTful API. A first party application is able to authenticate and authorize itself to the RESTful API using the same cookie session that the web application is using. The first party application has full access to the RESTful API.

3.3.1.2 Third Party OAuth 2.0-Based Authentication

A third party is any party other than the author of a RESTful API. A third party application cannot be trusted in the same way as a first party application; therefore, there must be a mediated means to selectively grant the third party application limited access to the RESTful API.

The OAuth 2.0 protocol defines flows to provide conditional and limited access to a RESTful API. In short, the third party application must first be registered with the first party, and then the first party (or an end user of the first party RESTful service) approves the third party application for limited access to the RESTful API, by issuing the third party application a short-lived access token.

3.3.1.2.1 Two-Legged and Three-Legged OAuth Flows

Some flows in OAuth are defined as two-legged and others as three-legged.

Two-legged OAuth flows involve two parties: the party calling the RESTful API (the third party application), and the party providing the RESTful API. Two-legged flows are used in server to server interactions where an end user does not need to approve access to the RESTful API. In OAuth 2.0 this flow is called the client credentials flow. It is most typically used in business to business scenarios.

Three-legged OAuth flows involve three parties: the party calling the RESTful API, the party providing the RESTful API, and an end user party that owns or manages the data to which the RESTful API provides access. Three-legged flows are used in client to server interactions where an end user must approve access to the RESTful API. In OAuth 2.0 the authorization code flow and the implicit flow are three-legged flows. These flows are typically used in business to consumer scenarios.

For resources protected by three-legged flows, when an OAuth client is registering with a RESTful API, it can safely indicate the protected resources that it requires access to, and the end user has the final approval decision about whether to grant the client access. However for resources protected by two-legged flows, the owner of the RESTful API must approve which resources each client is authorized to access.

3.3.2 About Privileges for Accessing Resources

A privilege for accessing resources consists of the following data:

  • Name: The unique identifier for the Privilege. This value is required.

  • Label: The name of the privilege presented to an end user when the user is being asked to approve access to a privilege when using OAuth. This value is required if the privilege is used with a three-legged OAuth flow.

  • Description: A description of the purpose of the privilege. It is also presented to the end user when the user is being asked to approve access to a privilege. This value is required if the privilege is used with a three-legged OAuth flow.

  • Roles: A set of role names associated with the privilege. An authenticated party must have at least one of the specified roles in order to be authorised to access resources protected by the privilege. A value is required, although it may be an empty set, which indicates that a user must be authenticated but that no specific role is required to access the privilege.

For two-legged OAuth flows, the third party application (called a client in OAuth terminology) must possess at least one of the required roles.

For three-legged OAuth flows, the end user that approves the access request from the third party application must possess at least one of the required roles.

3.3.3 About Users and Roles for Accessing Resources

A privilege enumerates a set of roles, and users can possess roles. but where are these Roles defined? What about the users that possess these roles? Where are they defined?

A privilege enumerates a set of roles, and users can possess roles. Oracle REST Data Services delegates the task of user management to the application server on which Oracle REST Data Services is deployed. Oracle REST Data Services is able to authenticate users defined and managed by the application server and to identify the roles and groups to which the authenticated user belongs. It is the responsibility of the party deploying Oracle REST Data Services on an application server to also configure the user repository on the application server.

Because an application server can be configured in many ways to define a user repository or integrate with an existing user repository, this document cannot describe how to configure a user repository in an application server. See the application server documentation for detailed information.

3.3.4 About the File-Based User Repository

Oracle REST Data Services provides a a simple file-based user repository mechanism. However, this user repository is only intended for the purposes of demonstration and testing, and is not supported for production use.

See the command-line help for the user command for more information on how to create a user in this repository:

java -jar ords.war help user

Format:

java -jar ords.war user <user> <roles>

Arguments:

  • <user> is the user ID of the user.

  • <roles> is the list of roles (zero or more) that the user has.

3.3.5 Tutorial: Protecting and Accessing Resources

This tutorial demonstrates creating a privilege to protect a set of resources, and accessing the protected resource with the following OAuth features:

  • Client credentials

  • Authorization code

  • Implicit flow

It also demonstrates access the resource using first-party cookie-based authentication.

Topics:

3.3.5.1 OAuth Flows and When to Use Each

This topic explains when to use various OAuth flow features.

Use first party cookie-based authentication when accessing a RESTful API from a web application hosted on the same origin as the RESTful API.

Use the authorization code flow when you need to permit third party web applications to access a RESTful API and the third party application has its own web server where it can keep its client credentials secure. This is the typical situation for most web applications, and it provides the most security and best user experience, because the third party application can use refresh tokens to extend the life of a user session without having to prompt the user to reauthorize the application.

Use the implicit flow when the third party application does not have a web server where it can keep its credentials secure. This flow is useful for third party single-page-based applications. Because refresh tokens cannot be issued in the Implicit flow, the user will be prompted more frequently to authorize the application.

Native mobile or desktop applications should use the authorization code or implicit flows. They will need to display the sign in and authorization prompts in a web browser view, and capture the access token from the web browser view at the end of the authorization process.

Use the client credentials flow when you need to give a third party application direct access to a RESTful API without requiring a user to approve access to the data managed by the RESTful API. The third party application must be a server-based application that can keep its credentials secret. The client credentials flow must not be used with a native application, because the client credentials can always be discovered in the native executable.

3.3.5.2 Assumptions for This Tutorial

This tutorial assumes the following:

  • Oracle REST Data Services is deployed at the following URL: https://example.com/ords/

  • A database schema named ORDSTEST has been enabled for use with Oracle REST Data Services, and its RESTful APIs are exposed under: https://example.com/ords/ordstest/

  • The ORDSTEST schema contains a database table named EMP, which was created as follows:

    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)
      );
    
  • The resources to be protected are located under: https://example.com/ords/ordstest/examples/employees/

3.3.5.3 Steps for This Tutorial

Follow these steps to protect and access a set of resources.

  1. Enable the schema. Connect to the ORDSTEST schema and execute the following PL/SQL statements;

    begin
      ords.enable_schema;
      commit;
    end;
    
  2. Create a resource. Connect to the ORDSTEST schema and execute the following PL/SQL statements:

    begin
    ords.create_service(
          p_module_name => 'examples.employees' ,
          p_base_path  => '/examples/employees/',
          p_pattern =>  '.' ,
          p_items_per_page => 7,
          p_source  =>  'select * from emp order by empno desc');
    commit;
    end;
    

    The preceding code creates the /examples/employees/ resource, which you will protect with a privilege in a later step.

    You can verify the resource by executing following cURL command:

    curl -i https://example.com/ords/ordstest/examples/employees/
    

    The result should be similar to the following (edited for readability):

    Content-Type: application/json
    Transfer-Encoding: chunked
     
    {
     "items":
       [
        {"empno":7934,"ename":"MILLER","job":"CLERK","mgr":7782,"hiredate":"1982-01-23T00:00:00Z","sal":1300,"comm":null,"deptno":10},
        ...
       ],
     "hasMore":true,
     "limit":7,
     "offset":0,
     "count":7,
     "links":
       [
        {"rel":"self","href":"https://example.com/ords/ordstest/examples/employees/"},
        {"rel":"describedby","href":"https://example.com/ords/ordstest/metadata-catalog/examples/employees/"},
        {"rel":"first","href":"https://example.com/ords/ordstest/examples/employees/"},
        {"rel":"next","href":"https://example.com/ords/ordstest/examples/employees/?offset=7"}
       ]
    }
    
  3. Create a privilege. While connected to the ORDSTEST schema, execute the following PL/SQL statements:

    begin
      ords.create_role('HR Administrator');     
     
      ords.create_privilege(
          p_name => 'example.employees',
          p_role_name => 'HR Administrator',
          p_label => 'Employee Data',
          p_description => 'Provide access to employee HR data');
      commit;
    end;
    

    The preceding code creates a role and a privilege, which belong to the ORDSTEST schema.

    • The role name must be unique and must contain printable characters only.

    • The privilege name must be unique and must conform to the syntax specified by the OAuth 2.0 specification, section 3.3 for scope names.

    • Because you will want to use this privilege with the three-legged authorization code and implicit flows, you must provide a label and a description for the privilege. The label and description are presented to the end user during the approval phase of three-legged flows.

    • The values should be plain text identifying the name and purpose of the privilege.

    You can verify that the privilege was created correctly by querying the USER_ORDS_PRIVILEGES view.

    select id,name from user_ords_privileges where name = 'example.employees';
    

    The result should be similar to the following:

    ID    NAME                                                                                                                                                                                                                                                           
    ----- -----------------
    10260 example.employees
    

    The ID value will vary from database to database, but the NAME value should be as shown.

  4. Associate the privilege with resources. While connected to the ORDSTEST schema, execute the following PL/SQL statements:

    begin
     ords.create_privilege_mapping(
          p_privilege_name => 'example.employees',
          p_pattern => '/examples/employees/*');     
      commit;
    end;
    

    The preceding code associates the example.employees privilege with the resource pattern /examples/employees/.

    You can verify that the privilege was created correctly by querying the USER_ORDS_PRIVILEGE_MAPPINGS view.

    select privilege_id, name, pattern from user_ords_privilege_mappings;
    

    The result should be similar to the following:

    PRIVILEGE_ID NAME                 PATTERN          
    ------------ -------------------- ---------------------
    10260        example.employees    /examples/employees/*
    

    The PRIVILEGE_ID value will vary from database to database, but the NAME and PATTERN values should be as shown.

    You can confirm that the /examples/employees/ resource is now protected by the example.employees privilege by executing the following cURL command:

    curl -i https://example.com/ords/ordstest/examples/employees/
    

    The result should be similar to the following (reformatted for readability):

    HTTP/1.1 401 Unauthorized
    Content-Type: text/html
    Transfer-Encoding: chunked
     
    <!DOCTYPE html>
    <html>
    ...
    </html>
    

    You can confirm that the protected resource can be accessed through first party authentication, as follows.

    1. Create an end user. Create a test user with the HR Administrator role, required to access the examples.employees privilege using the file-based user repository. Execute the following command at a command prompt

      java -jar ords.war user "hr_admin" "HR Administrator"
      

      When prompted for the password, enter and confirm it.

    2. Sign in as the end user. Enter the following URL in a web browser:

      https://example.com/ords/ordstest/examples/employees/
      

      On the page indicating that access is denied, click the link to sign in.

      Enter the credentials registered for the HR_ADMIN user, and click Sign In.

      Confirm that the page redirects to https://example.com/ords/ordstest/examples/employees/ and that the JSON document is displayed.

  5. Register the OAuth client. While connected to the ORDSTEST schema, execute the following PL/SQL statements:

    begin 
     oauth.create_client(
          p_name => 'Client Credentials Example',
          p_grant_type => 'client_credentials',
          p_privilege_names => 'example.employees',
          p_support_email => 'support@example.com');
     commit;
    end;
    

    The preceding code registers a client named Client Credentials Example, to access the examples.employees privilege using the client credentials OAuth flow.

    You can verify that the client was registered and has requested access to the examples.employees privilege by executing the following SQL statement:

    select client_id,client_secret from user_ords_clients where name = 'Client Credentials Example';
    

    The result should be similar to the following:

    CLIENT_ID                        CLIENT_SECRET                   
    -------------------------------- ------------------------
    o_CZBVkEMN23tTB-IddQsQ..         4BJXceufbmTki-vruYNLIg.. 
    

    The CLIENT_ID and CLIENT_SECRET values represent the secret credentials for the OAuth client. These values must be noted and kept secure. You can think of them as the userid and password for the client application.

  6. Grant the OAuth client a required role. While connected to the ORDSTEST schema, execute the following PL/SQL statements:

    begin 
     oauth.grant_client_role(
         'Client Credentials Example',
         'HR Administrator');
     commit;
    end;
    

    The preceding code registers a client named Client Credentials Example, to access the examples.employees privilege using the client credentials OAuth flow.

    You can verify that the client was granted the role by executing the following SQL statement:

    select * from user_ords_client_roles where client_name = 'Client Credentials Example';
    

    The result should be similar to the following:

     CLIENT_ID CLIENT_NAME                 ROLE_ID   ROLE_NAME           
    ---------- --------------------------- --------  ----------------------
         10286 Client Credentials Example    10222   HR Administrator   
    
  7. Obtain an OAuth access token using client credentials.

    The OAuth protocol specifies the HTTP request that must be used to create an access token using the client credentials flow[rfc6749-4.4.].

    The request must be made to a well known URL, called the token endpoint. For Oracle REST Data Services the path of the token endpoint is always oauth/token, relative to the root path of the schema being accessed. The token endpoint for this example is:

    https://example.com/ords/ordstest/oauth/token
    

    Execute the following cURL command:

    curl -i --user clientId:clientSecret --data "grant_type=client_credentials" https://example.com/ords/ordstest/oauth/token
    

    In the preceding command, replace clientId with the CLIENT_ID value in USER_ORDS_CLIENTS for Client Credentials Example, and replace clientSecret with the CLIENT_SECRET value shown in USER_ORDS_CLIENTS for Client Credentials Example. The output should be similar to the following:

    HTTP/1.1 200 OK
    Content-Type: application/json
     
    {
     "access_token": "2YotnFZFEjr1zCsicMWpAA",
     "token_type":   "bearer",
     "expires_in":3600
    }
    

    In the preceding output, the access token is of type bearer, and the value is specified by the access_token field. This value will be different for every request. The expires_in value indicates the number of seconds until the access token expires; in this case the token will expire in one hour (3600 seconds).

  8. Access a protected resource using the access token. Execute the following cURL command:

    curl -i -H"Authorization: Bearer accessToken" https://example.com/ords/ordstest/examples/employees/
    

    In the preceding command, replace accessToken with the value of the access_token field shown in the preceding step. The output should be similar to the following:

    Content-Type: application/json
    Transfer-Encoding: chunked
     
    {
     "items":
       [
        {"empno":7934,"ename":"MILLER","job":"CLERK","mgr":7782,"hiredate":"1982-01-23T00:00:00Z","sal":1300,"comm":null,"deptno":10},
        ...
       ],
     "hasMore":true,
     "limit":7,
     "offset":0,
     "count":7,
     "links":
       [
        {"rel":"self","href":"https://example.com/ords/ordstest/examples/employees/"},
        {"rel":"describedby","href":"https://example.com/ords/ordstest/metadata-catalog/examples/employees/"},
        {"rel":"first","href":"https://example.com/ords/ordstest/examples/employees/"},
        {"rel":"next","href":"https://example.com/ords/ordstest/examples/employees/?offset=7"}
       ]
    }
    
  9. Register the client for authorization code. While connected to the ORDSTEST schema, execute the following PL/SQL statements:

    begin
     oauth.create_client(
        p_name => 'Authorization Code Example',
        p_grant_type => 'authorization_code',
        p_owner => 'Example Inc.',
        p_description => 'Sample for demonstrating Authorization Code Flow',
        p_redirect_uri => 'http://example.org/auth/code/example/',
        p_support_email => 'support@example.org',
        p_support_uri => 'http://example.org/support',
        p_privilege_names => 'example.employees'
        );
     commit;
    end;
    

    The preceding code registers a client named Authorization Code Example, to access the examples.employees privilege using the authorization code OAuth flow. For an actual application, a URI must be provided to redirect back to with the authorization code, and a valid support email address must be supplied; however, this example uses fictitious data and the sample example.org web service.

    You can verify that the client is now registered and has requested access to the examples.employees privilege by executing the following SQL statement:

    select id, client_id, client_secret from user_ords_clients where name = 'Authorization Code Example';
    

    The result should be similar to the following:

            ID CLIENT_ID                        CLIENT_SECRET
    ---------- -------------------------------- --------------------------------
         10060 IGHso4BRgrBC3Jwg0Vx_YQ.. GefAsWv8FJdMSB30Eg6lKw.. 
    

    To grant access to the privilege, an end user must approve access. The CLIENT_ID and CLIENT_SECRET values represent the secret credentials for the OAuth client. These values must be noted and kept secure. You can think of them as the userid and password for the client application.

  10. Obtain an OAuth access token using an authorization code. This major step involves several substeps. (You must have already created the HR_ADMIN end user in a previous step.)

    1. Obtain an OAuth authorization code.

      The end user must be prompted (via a web page) to sign in and approve access to the third party application. The third party application initiates this process by directing the user to the OAuth Authorization Endpoint. For Oracle REST Data Services, the path of the authorization endpoint is always oauth/auth, relative to the root path of the schema being accessed. The token endpoint for this example is:

      https://example.com/ords/ordstest/oauth/auth
      

      The OAuth 2.0 protocol specifies that the Authorization request URI must include certain parameters in the query string:

      The response_type parameter must have a value of code.

      The client_id parameter must contain the value of the applications client identifier. This is the client_id value determined in a previous step.

      The state parameter must contain a unique unguessable value. This value serves two purposes: it provides a way for the client application to uniquely identify each authorization request (and therefore associate any application specific state with the value; think of the value as the application's own session identifier); and it provides a means for the client application to protect against Cross Site Request Forgery (CSRF) attacks. The state value will be returned in the redirect URI at the end of the authorization process. The client must confirm that the value belongs to an authorization request initiated by the application. If the client cannot validate the state value, then it should assume that the authorization request was initiated by an attacker and ignore the redirect.

      To initiate the Authorization request enter the following URL in a web browser:

      https://example.com/ords/ordstest/oauth/auth?response_type=code&client_id=cliendId&state=uniqueRandomValue
      

      In the preceding URI, replace clientId with the value of the CLIENT_ID column that was noted previously, and replace uniqueRandromValue with a unique unguessable value. The client application must remember this value and verify it against the state parameter returned as part of the redirect at the end of the authorization flow.

      If the client_id is recognized, then a sign in prompt is displayed. Enter the credentials of the HR_ADMIN end user, and click Sign In; and on the next page click Approve to cause a redirect to redirect URI specified when the client was registered. The redirect URI will include the authorization code in the query string portion of the URI. It will also include the same state parameter value that the client provided at the start of the flow. The redirect URI will look like the following:

      http://example.org/auth/code/example/?code=D5doeTSIDgbxWiWkPl9UpA..&state=uniqueRandomValue
      

      The client application must verify the value of the state parameter and then note the value of the code parameter, which will be used in to obtain an access token.

    2. Obtain an OAuth access token.

      After the third party application has an authorization code, it must exchange it for an access token. The third party application's server must make a HTTPS request to the Token Endpoint. You can mimic the server making this request by using a cURL command as in the following example:

      curl --user clientId:clientSecret --data "grant_type=authorization_code&code=authorizationCode" https://example.com/ords/ordstest/oauth/token
      

      In the preceding command, replace clientId with the value of the CLIENT_ID shown in USER_ORDS_CLIENTS for Authorization Code Example, replace clientSecret with the value of the CLIENT_SECRET shown in USER_ORDS_CLIENTS for Authorization Code Example, and replace authorizationCode with the value of the authorization code noted in a previous step (the value of the code parameter).

      The result should be similar to the following:

      HTTP/1.1 200 OK
      Content-Type: application/json
       
      {
       "access_token": "psIGSSEXSBQyib0hozNEdw..",
       "token_type":   "bearer",
       "expires_in":3600,
       "refresh_token": "aRMg7AdWPuDvnieHucfV3g.."
      }
      

      In the preceding result, the access token is specified by the access_token field, and a refresh token is specified by the refresh_token field. This refresh token value can be used to extend the user session without requiring the user to reauthorize the third party application.

    3. Access a protected resource using the access token.

      After the third party application has obtained an OAuth access token, it can use that access token to access the protected /examples/employees/ resource:

      curl -i -H"Authorization: Bearer accessToken" https://example.com/ords/ordstest/examples/employees/
      

      In the preceding command, accessToken with the value of the access_token field shown in a previous step.

      The result should be similar to the following:

      Content-Type: application/json
      Transfer-Encoding: chunked
       
      {
       "items":
         [
          {"empno":7934,"ename":"MILLER","job":"CLERK","mgr":7782,"hiredate":"1982-01-23T00:00:00Z","sal":1300,"comm":null,"deptno":10},
          ...
         ],
       "hasMore":true,
       "limit":7,
       "offset":0,
       "count":7,
       "links":
         [
          {"rel":"self","href":"https://example.com/ords/ordstest/examples/employees/"},
          {"rel":"describedby","href":"https://example.com/ords/ordstest/metadata-catalog/examples/employees/"},
          {"rel":"first","href":"https://example.com/ords/ordstest/examples/employees/"},
          {"rel":"next","href":"https://example.com/ords/ordstest/examples/employees/?offset=7"}
         ]
      }
      
    4. Extend the session using a refresh token.

      At any time, the third party application can use the refresh token value to generate a new access token with a new lifetime. This enables the third party application to extend the user session at will. To do this, the third party application's server must make an HTTPS request to the Token Endpoint. You can mimic the server making this request by using a cURL command as in the following example:

      curl --user clientId:clientSecret --data “grant_type=refresh_token&refresh_token=refreshToken" https://example.com/ords/ordstest/oauth/token
      

      In the preceding command, replace clientId with the value of the CLIENT_ID shown in USER_ORDS_CLIENTS for Client Credentials Client, replace clientSecret with the value of the CLIENT_SECRET shown in USER_ORDS_CLIENTS for Client Credentials Client, and replace refreshToken with the value of refresh_token obtained in a previous step.

      The result should be similar to the following:

      HTTP/1.1 200 OK
      Content-Type: application/json
       
      {
       "access_token":  "psIGSSEXSBQyib0hozNEdw..",
       "token_type":    "bearer",
       "refresh_token": "aRMg7AdWPuDvnieHucfV3g..",
       "expires_in":    3600
      }
      

      In the preceding result, the access token is specified by the access_token field, a new refresh token is specified by the refresh_token field. This refresh token value can be used to extend the user session without requiring the user to reauthorize the third party application. (Note that the previous access token and refresh token are now invalid; the new values must be used instead.)

  11. Register the client for implicit flow. While connected to the ORDSTEST schema, execute the following PL/SQL statements:

    begin
     oauth.create_client(
        p_name => 'Implicit Example',
        p_grant_type => 'implicit',
        p_owner => 'Example Inc.',
        p_description => 'Sample for demonstrating Implicit Flow',
        p_redirect_uri => 'http://example.org/implicit/example/',
        p_support_email => 'support@example.org',
        p_support_uri => 'http://example.org/support',
        p_privilege_names => 'example.employees'
        );
     commit;
    end;
    

    The preceding code registers a client named Implicit Example to access the examples.employees privilege using the implicit OAuth flow. For an actual application, a URI must be provided to redirect back to with the authorization code, and a valid support email address must be supplied; however, this example uses fictitious data and the sample example.org web service.

    You can verify that the client is now registered and has requested access to the examples.employees privilege by executing the following SQL statement:

    select id, client_id, client_secret from user_ords_clients where name = 'Implicit Example';
    

    The result should be similar to the following:

            ID CLIENT_ID CLIENT_SECRET
    ---------- -------------------------------- --------------------------------
         10062 7Qz--bNJpFpv8qsfNQpS1A.. 
    

    To grant access to the privilege, an end user must approve access.

  12. Obtain an OAuth access token using implicit flow. (You must have already created the HR_ADMIN end user in a previous step.)

    The end user must be prompted (via a web page) to sign in and approve access to the third party application. The third party application initiates this process by directing the user to the OAuth Authorization Endpoint. For Oracle REST Data Services, the path of the authorization endpoint is always oauth/auth, relative to the root path of the schema being accessed. The token endpoint for this example is:

    https://example.com/ords/ordstest/oauth/auth
    

    The OAuth 2.0 protocol specifies that the Authorization request URI must include certain parameters in the query string:

    The response_type parameter must have a value of token.

    The client_id parameter must contain the value of the applications client identifier. This is the client_id value determined in a previous step.

    The state parameter must contain a unique unguessable value. This value serves two purposes: it provides a way for the client application to uniquely identify each authorization request (and therefore associate any application specific state with the value; think of the value as the application's own session identifier); and it provides a means for the client application to protect against Cross Site Request Forgery (CSRF) attacks. The state value will be returned in the redirect URI at the end of the authorization process. The client must confirm that the value belongs to an authorization request initiated by the application. If the client cannot validate the state value, then it should assume that the authorization request was initiated by an attacker and ignore the redirect.

    To initiate the Authorization request enter the following URL in a web browser:

    https://example.com/ords/ordstest/oauth/auth?response_type=token&client_id=cliendId&state=uniqueRandomValue
    

    In the preceding URI, replace clientId with the value of the CLIENT_ID column that was noted previously, and replace uniqueRandromValue with a unique unguessable value. The client application must remember this value and verify it against the state parameter returned as part of the redirect at the end of the authorization flow.

    If the client_id is recognized, then a sign in prompt is displayed. Enter the credentials of the HR_ADMIN end user, and click Sign In; and on the next page click Approve to cause a redirect to redirect URI specified when the client was registered. The redirect URI will include the access token in the query string portion of the URI. It will also include the same state parameter value that the client provided at the start of the flow. The redirect URI will look like the following:

    http://example.org/auth/code/example/#access_token=D5doeTSIDgbxWiWkPl9UpA..&type=bearer&expires_in=3600&state=uniqueRandomValue
    

    The client application must verify the value of the state parameter and then note the value of the access token.

  13. Access a protected resource using an access token. Execute the following cURL command:

    curl -i -H "Authorization: Bearer accessToken" https://example.com/ords/ordstest/examples/employees/
    

    In the preceding command, replace accessToken with the value of the access_token field shown in the preceding step. The output should be similar to the following:

    Content-Type: application/json
    Transfer-Encoding: chunked
     
    {
     "items":
       [
        {"empno":7934,"ename":"MILLER","job":"CLERK","mgr":7782,"hiredate":"1982-01-23T00:00:00Z","sal":1300,"comm":null,"deptno":10},
        ...
       ],
     "hasMore":true,
     "limit":7,
     "offset":0,
     "count":7,
     "links":
       [
        {"rel":"self","href":"https://example.com/ords/ordstest/examples/employees/"},
        {"rel":"describedby","href":"https://example.com/ords/ordstest/metadata-catalog/examples/employees/"},
        {"rel":"first","href":"https://example.com/ords/ordstest/examples/employees/"},
        {"rel":"next","href":"https://example.com/ords/ordstest/examples/employees/?offset=7"}
       ]
    }
    

3.4 About Oracle REST Data Services User Roles

Oracle REST Data Services defines a small number of predefined user roles:

  • RESTful Services - This is the default role associated with a protected RESTful service.

  • OAuth2 Client Developer - Users who want to register OAuth 2.0 applications must have this role.

  • SQL Developer - Users who want to use Oracle SQL Developer to develop RESTful services must have this role.

  • SODA Developer - This is the default role that is required to access the SODA REST API. For more information about this role, see Oracle REST Data Services SODA for REST Developer's Guide.

  • Listener Administrator - Users who want to administrate an Oracle REST Data Services instance through Oracle SQL Developer must have this role. Typically, only users created through the java -jar ords.war user command will have this role.

    Because the Listener Administrator role enables a user to configure an Oracle REST Data Services instance, and therefore has the capability to affect all Application Express workspaces served through that instance, Application Express users are not permitted to acquire the Listener Administrator role.

Topics:

3.4.1 About Oracle Application Express Users and Oracle REST Data Services Roles

By default, Oracle Application Express users do not have any of the Oracle REST Data Services predefined user roles. This means that, by default, Application Express users cannot:

  • Invoke protected RESTful Services

  • Register OAuth 2.0 applications

  • Use Oracle SQL Developer to develop RESTful services.

This applies to all Application Express users, including Application Express developers and administrators. It is therefore important to remember to follow the steps below to add Application Express users to the appropriate user groups, so that they can successfully perform the above actions.

Topics:

3.4.1.1 Granting Application Express Users Oracle REST Data Services Roles

To give an Application Express User any of the roles above, the user must be added to the equivalent Application Express user group. For example, to give the RESTEASY_ADMIN user the RESTful Services role, follow these steps:

  1. Log in to the RESTEASY workspace as a RESTEASY_ADMIN.
  2. Navigate to Administration and then Manage Users and Groups.
  3. Click the Edit icon to the left of the RESTEASY_ADMIN user.
  4. For User Groups, select RESTful Services.
  5. Click Apply Changes.

3.4.1.2 Automatically Granting Application Express Users Oracle REST Data Services Roles

Adding Application Express users to the appropriate user groups can be an easily overlooked step, or can become a repetitive task if there are many users to be managed.

To address these issues, you can configure Oracle REST Data Services to automatically grant Application Express users a predefined set of RESTful Service roles by modifying the defaults.xml configuration file.

In that file, Oracle REST Data Services defines three property settings to configure roles:

  • apex.security.user.roles - A comma separated list of roles to grant ordinary users, that is, users who are not developers or administrators.

  • apex.security.developer.roles - A comma separated list of roles to grant users who have the Developer account privilege. Developers also inherit any roles defined by the apex.security.user.roles setting.

  • apex.security.administrator.roles - A comma separated list of roles to grant users who have the Administrator account privilege. Administrators also inherit any roles defined by the apex.security.user.roles and apex.security.developer.roles settings.

For example, to automatically give all users the RESTful Services privilege and all developers and administrators the OAuth2 Client Developer and SQL Developer roles, add the following to the defaults.xml configuration file:

<!-- Grant all Application Express Users the ability      
        to invoke protected RESTful Services -->
<entry key="apex.security.user.roles">RESTful Services</entry> 
<!-- Grant Application Express Developers and Administrators the ability
        to register OAuth 2.0 applications and use Oracle SQL Developer
        to define RESTful Services --> 
<entry key="apex.security.developer.roles">
   OAuth2 Client Developer, SQL Developer</entry>

Oracle REST Data Services must be restarted after you make any changes to the defaults.xml configuration file.

3.4.2 Controlling RESTful Service Access with Roles

The built-in RESTful Service role is a useful default for identifying users permitted to access protected RESTful services.

However, it will often also be necessary to define finer-grained roles to limit the set of users who may access a specific RESTful service.

Topics:

3.4.2.1 About Defining RESTful Service Roles

A RESTful Service role is an Application Express user group. To create a user group to control access to the Gallery RESTful Service, follow these steps. (

  1. Log in to the RESTEASY workspace as a workspace administrator.

  2. Navigate to Administration and then Manage Users and Groups.

  3. Click the Groups tab.

  4. Click Create User Group.

  5. For Name, enter Gallery Users.

  6. Click Create Group.

3.4.2.2 Associating Roles with RESTful Privileges

After a user group has been created, it can be associated with a RESTful privilege. To associate the Gallery Users role with the example.gallery privilege, follow these steps.

  1. Navigate to SQL Workshop and then RESTful Services.
  2. In the Tasks section, click RESTful Service Privileges.
  3. Click Gallery Access.
  4. For Assigned Groups, select Gallery Users.
  5. Click Apply Changes.

With these changes, users must have the Gallery Users role to be able to access the Gallery RESTful service.

See Also:

The steps here use the image gallery application in Development Tutorial: Creating an Image Gallery as an example.

3.5 Authenticating Against WebLogic Server and GlassFish User Repositories

Oracle REST Data Services can use APIs provided by WebLogic Server and GlassFish to verify credentials (username and password) and to retrieve the set of groups and roles that the user is a member of.

This section walks through creating a user in the built-in user repositories provided by WebLogic Server and GlassFish, and verifying the ability to authenticate against that user.

This document does not describe how to integrate WebLogic Server and GlassFish with the many popular user repository systems such as LDAP repositories, but Oracle REST Data Services can authenticate against such repositories after WebLogic Server or GlassFish has been correctly configured. See your application server documentation for more information on what user repositories are supported by the application server and how to configure access to these repositories.

Topics:

3.5.1 Authenticating Against WebLogic Server

Authenticating a user against WebLogic Server involves the following major steps:

  1. Creating a WebLogic Server User

  2. Verifying the WebLogic Server User

3.5.1.1 Creating a WebLogic Server User

To create a sample WebLogic Server user, follow these steps:

  1. Start WebLogic Server if it is not already running

  2. Access the WebLogic Server Administration Console (typically http://server:7001/console), enter your credentials.

  3. In the navigation tree on the left, click the Security Realms node

  4. If a security realm already exists, go to the next step. If a security realm does not exist, create one as follows:

    1. Click New.

    2. For Name, enter Test-Realm, then click OK.

    3. Click Test-Realm.

    4. Click the Providers tab.

    5. Click New, and enter the following information:

      Name: test-authenticator

      Type: DefaultAuthenticator

    6. Restart WebLogic Server if you are warned that a restart is necessary.

    7. Click Test-Realm.

  5. Click the Users and Groups tab.

  6. Click New, and enter the following information:

    • Name: 3rdparty_dev2

    • Password: Enter and confirm the desired password for this user.

  7. Click OK.

  8. Click the Groups tab.

  9. Click New., and enter the following information:

    • Name: OAuth2 Client Developer (case sensitive)

  10. Click OK.

  11. Click the Users tab.

  12. Click the 3rdparty_dev2 user.

  13. Click the Groups tab.

  14. In the Chosen list, add OAuth2 Client Developer .

  15. Click Save.

You have created a user named 3rdparty_dev2 and made it a member of a group named OAuth2 Client Developer. This means the user will acquire the OAuth2 Client Developer role, and therefore will be authorized to register OAuth 2.0 applications.

Now verify that the user can be successfully authenticated.

3.5.1.2 Verifying the WebLogic Server User

To verify that the WebLogic Server user created can be successfully authenticated, follow these steps:

  1. In your browser, go to a URI in the following format:

    https://server:port/ords/resteasy/ui/oauth2/clients/

  2. Enter the credentials of the 3rdparty_dev2 user, and click Sign In.

The OAuth 2.0 Client Registration page should be displayed, with no applications listed. If this page is displayed, you have verified that authentication against the WebLogic Server user repository is working.

However, if the sign-on prompt is displayed again with the message User is not authorized to access resource, then you made mistake (probably misspelling the Group List value).

3.5.2 Authenticating Against GlassFish

Authenticating a user against GlassFish involves the following major steps:

  1. Creating a GlassFish User

  2. Verifying the GlassFish User

3.5.2.1 Creating a GlassFish User

To create a sample GlassFish user, follow these steps:

  1. Start GlassFish if it is not already running
  2. Access the GlassFish Administration Console (typically http://server:4848); and if you have configured a password, enter your credentials.
  3. Navigate to the Security Configuration pages:
  4. In the navigation tree on the left, expand the Configurations node, and then expand the following nodes: server-config, Security, Realms, file.
  5. Click Manage Users.
  6. Click New, and enter the following information:
    • Name: 3rdparty_dev2

    • Group List: OAuth2 Client Developer (case sensitive)

    • Password: Enter and confirm the desired password for this user.

  7. Click OK.

You have created a user named 3rdparty_dev2 and made it a member of a group named OAuth2 Client Developer. This means the user will acquire the OAuth2 Client Developer role, and therefore will be authorized to register OAuth 2.0 applications.

Now verify that the user can be successfully authenticated.

3.5.2.2 Verifying the GlassFish User

To verify that the WebLogic Server user created in Creating a GlassFish User can be successfully authenticated, follow these steps:

  1. In your browser, go to a URI in the following format:

    https://server:port/ords/resteasy/ui/oauth2/clients/

  2. Enter the credentials of the 3rdparty_dev2 user, and click Sign In.

The OAuth 2.0 Client Registration page should be displayed, with no applications listed. If this page is displayed, you have verified that authentication against the WebLogic Server user repository is working.

However, if the sign-on prompt is displayed again with the message User is not authorized to access resource, then you made mistake (probably misspelling the Group List value).

3.6 Integrating with Existing Group/Role Models

The examples in other sections demonstrate configuring the built-in user repositories of WebLogic Server and GlassFish. In these situations you have full control over how user groups are named. If a user is a member of a group with the exact same (case sensitive) name as a role, then the user is considered to have that role.

However, when integrating with existing user repositories, RESTful service developers will often not have any control over the naming and organization of user groups in the user repository. In these situations a mechanism is needed to map from existing "physical" user groups defined in the user repository to the "logical" roles defined by Oracle REST Data Services and/or RESTful Services.

In Oracle REST Data Services, this group to role mapping is performed by configuring a configuration file named role-mapping.xml.

Topics:

3.6.1 About role-mapping.xml

role-mapping.xml is a Java XML Properties file where each property key defines a pattern that matches against a set of user groups, and each property value identifies the roles that the matched user group should be mapped to. It must be located in the same folder as the defaults.xml configuration file. The file must be manually created and edited.

Consider this example:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
 <entry key="webdevs">RESTful Services</entry>
</properties>

This role mapping is straightforward, stating that any user who is a member of a group named: webdevs is given the role RESTful Services, meaning that all members of the webdevs group can invoke RESTful Services.

A mapping can apply more than one role to a group. For example:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
 <entry key="webdevs">RESTful Services, SQL Developer</entry>
</properties>

This rule gives members of the webdevs group both the RESTful Services and SQL Developer roles.

Topics:

3.6.1.1 Parameterizing Mapping Rules

Having to explicitly map from each group to each role may not be scalable if the number of groups or roles is large. To address this concern, you can parameterize rules. Consider this example:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd"> 
<properties>
 <entry key="{prefix}.webdevs">RESTful Services</entry>
</properties>

This example says that any group name that ends with .webdevs will be mapped to the RESTful Services role. For example, a group named: HQ.webdevs would match this rule, as would a group named: EAST.webdevs.

The syntax for specifying parameters in rules is the same as that used for URI Templates; the parameter name is delimited by curly braces ({}).

3.6.1.2 Dereferencing Parameters

Any parameter defined in the group rule can also be dereferenced in the role rule. Consider this example:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd"> 
<properties>
 <entry key="cn={userid},ou={group},dc=MyDomain,dc=com">{group}</entry>
</properties>

This example maps the organizational unit component of an LDAP distinguished name to a role. It says that the organizational unit name maps directly to a role with same name. Note that it refers to a {userid} parameter but never actually uses it; in effect, it uses {userid} as a wildcard flag.

For example, the distinguished name cn=jsmith,ou=Developers,dc=MyDomain,dc=com will be mapped to the logical role named Developers.

3.6.1.3 Indirect Mappings

To accomplish the desired role mapping, it may sometimes be necessary to apply multiple intermediate rules. Consider this example:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
 <entry key="cn={userid},ou={group},dc=example,dc=com">{group}</entry>
 <entry key="{prefix},ou={group},dc=acquired,dc=com">{group}</entry>
 <entry key="Developers">RESTful Services, SQL Developer</entry>
</properties>

This example maps the organizational unit component of an LDAP distinguished name to some roles. Complicating matters is the fact that users can come from two different organizations, resulting in differing distinguishing name patterns.

  • Users from example.com always have a single common name (CN) identifying their user id, followed by the organizational unit (OU) and the domain name (DC). For example: cn=jsmith,ou=Developers,dc=example,dc=com.

  • Users from acquired.com have varying numbers of common name (CN) prefixes, but the organizational unit is the field you are interested in. For example: cn=ProductDev,cn=abell,ou=Engineering,dc=acquired,dc=com.

  • Both organizations identify software engineers with ou=Developers.

You want to map engineers in both organizations to the RESTful Services and SQL Developer roles.

  • The first rule maps engineers in the example.com organization to the intermediate Developers role.

  • The second rule maps engineers in the acquired.com organization to the intermediate Developers role.

  • The final rule maps from the intermediate Developers role to the RESTful Services and SQL Developer roles.

3.7 Using the Oracle REST Data Services PL/SQL API

Oracle REST Data Services has a PL/SQL API (application programming interface) that you can use as an alternative to the SQL Developer graphical interface for many operations. The available subprograms are included in the following PL/SQL packages:

To use the Oracle REST Data Services PL/SQL API, you must first:

  • Install Oracle REST Data Services in the database that you will use to develop RESTful services.

  • Enable one or more database schemas for REST access.

Topics:

3.7.1 Creating a RESTful Service Using the PL/SQL API

You can create a RESTful service by connecting to a REST-enabled schema and using the ORDS.CREATE_SERVICE procedure.

The following example creates a simple "Hello-World"-type service:

begin
 ords.create_service(
      p_module_name => 'examples.routes' ,
      p_base_path   => '/examples/routes/',
      p_pattern     => 'greeting/:name',
      p_source => 'select ''Hello '' || :name || '' from '' || nvl(:whom,sys_context(''USERENV'',''CURRENT_USER'')) "greeting" from dual');
 commit;
end;
/

The preceding example does the following:

  • Creates a resource module named examples.routes.

  • Sets the base path (also known as the URI prefix) of the module to /examples/routes/.

  • Creates a resource template in the module, with the route pattern greeting/:name.

  • Creates a GET handler and sets its source as a SQL query that forms a short greeting:

    • GET is the default value for the p_method parameter, and it is used here because that parameter was omitted in this example.

    • COLLECTION_FEED is the default value for the p_method parameter, and it is used here because that parameter was omitted in this example

  • An optional parameter named whom is specified.

Related Topics

3.7.2 Testing the RESTful Service

To test the RESTful service that you created, start Oracle REST Data Services if it is not already started:

java -jar ords.war

Enter the URI of the service in a browser. The following example displays a "Hello" greeting to Joe, by default from the current user because no whom parameter is specified.:

http://localhost:8080/ords/ordstest/examples/routes/greeting/Joe

In this example:

  • Oracle REST Data Services is running on localhost and listening on port 8080.

  • Oracle REST Data Services is deployed at the context-path /ords.

  • The RESTful service was created by a database schema named ordstest.

  • Because the URL does not include the optional whom parameter, the :whom bind parameter is bound to the null value, which causes the query to use the value of the current database user (sys_context(''USERENV'',''CURRENT_USER'')) instead.

If you have a JSON viewing extension installed in your browser, you will see a result like the following:

{
 "items": [
  {
   "greeting": "Hello Joe from ORDSTEST"
  }
 ],
 "hasMore": false,
 "limit": 25,
 "offset": 0,
 "count": 1,
 "links": [
  {
   "rel": "self",
   "href": "http://localhost:8080/ords/ordstest/examples/routes/greeting/"
  },
  {
   "rel": "describedby",
   "href": "http://localhost:8080/ords/ordstest/metadata-catalog/examples/routes/greeting/"
  },
  {
   "rel": "first",
   "href": "http://localhost:8080/ords/ordstest/examples/routes/greeting/Joe"
  }
 ]
}

The next example is like the preceding one, except the optional parameter whom is specified to indicate that the greeting is from Jane.

http://localhost:8080/ords/ordstest/examples/routes/greeting/Joe?whom=Jane

This time, the result will look like the following:

{
 "items": [
  {
   "greeting": "Hello Joe from Jane"
  }
 ],
 "hasMore": false,
 "limit": 25,
 "offset": 0,
 "count": 1,
 "links": [
  {
   "rel": "self",
   "href": "http://localhost:8080/ords/ordstest/examples/routes/greeting/"
  },
  {
   "rel": "describedby",
   "href": "http://localhost:8080/ords/ordstest/metadata-catalog/examples/routes/greeting/"
  },
  {
   "rel": "first",
   "href": "http://localhost:8080/ords/ordstest/examples/routes/greeting/Joe"
  }
 ]
}

Notice that in this result, what follows "from" is Jane and not ORDSTEST, because the :whom bind parameter was bound to the Jane value.