2 Developing Oracle REST Data Services Applications
See Also:
If you want to get started quickly, you can try the tutorial in Oracle REST Data Services Quick Start Guide.
Note:
- Ensure that you have installed and configured both Oracle APEX 4.2 or later, and Oracle REST Data Services 3.0 or later, prior to attempting the examples discussed in this chapter.
- Install the Oracle REST APIs prior to using the Oracle REST APIs for JSON Data Persistence. See Oracle REST Data Services SODA for REST Developer's Guide
- Refer to the Oracle APEX Documentation, if you are new to Oracle APEX.
Topics:
2.1 Introduction to Relevant Software
This section explains some key relevant software for developing applications that use Oracle REST Data Services.
Topics:
Related Topics
2.1.1 Oracle APEX
ORDS makes your APEX applications available to the various application servers like WebLogic Server or Tomcat, through the PL/SQL Gateway feature. It is a fully-supported, no-cost option available with all editions of Oracle Database. Using only a web browser, you can develop and deploy professional applications that are both fast and secure.
2.1.2 REST APIs
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 REST 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.
ORDS provides a built-in web application, SQL Developer Web, which is used to build, test, document, and secure your REST APIs.
2.2 Getting Started with RESTful Services
This section introduces RESTful Services, and provides guidelines and examples for developing applications that use RESTful Services.
Topics:
Related Topics
2.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 theempresource withidof101) and will also match/emp/(matches a request for theempresource, because the :idparameter is annotated with the?modifier, which indicates that theidparameter is optional).For a detailed explanation of route patterns, see
docs\javadoc\plugin-api\route-patterns.html, under<sqldeveloper-install>\ordsand 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 withemployees/, such asemployees/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), andDELETE(remove a resource).
Related Topics
2.2.2 ORDS RESTful Web Services Architecture Diagrams
This section describes the ORDS RESTful web services architecture diagrams.
The following diagram illustrates the relationship between the different components of the ORDS RESTful Web Services architecture:
Figure 2-1 Relationship Between Components of the ORDS RESTful Web Services
Description of "Figure 2-1 Relationship Between Components of the ORDS RESTful Web Services"
The Database Schema is the schema that you have REST-enabled. It can contain
several resource modules. Similarly, a resource module, which is the top-level container
for the REST Services offered by ORDS, can contain several resource templates. The
resource templates are represented by the trailing part of the URL. Every resource
template can contain four resource handlers, namely, GET,
POST, PUT, and DELETE.
https://<HOSTNAME:PORT>/<CONTEXT>/<DATABASE_SCHEMA_ALIAS>/<MODULE_BASE_URI>/<TEMPLATE_URI>/-
HOSTNAME:PORT/CONTEXT: Specifies the address at which ORDS is running. You can also refer to it as the ORDS Base URI. -
DATABASE_SCHEMA_ALIAS: Specifies the name that you provided while REST-enabling your database schema. By default, it is the name of the schema in lowercase. -
MODULE_BASE_URI: Specifies the URI of the module. -
TEMPLATE_URI: Specifies the URI of the template. This value, along with theMODULE_BASE_URI, comprises the ORDS Endpoint URL.
The following diagram illustrates how a GET operation is
performed:
Figure 2-2 Architecture Diagram for a GET Operation
Description of "Figure 2-2 Architecture Diagram for a GET Operation"
https://localhost:8080/ords/hr/api/employees/2.2.3 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.
2.2.4 "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 ...
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-clickindex.htmlfor 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.)
2.2.5 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.
Related Topics
See Also:
curl - command line tool and libraryThe example in this section uses cURL with the services mentioned in Exploring the Sample RESTful Services in APEX (Tutorial)
2.3 Automatic Enabling of Schema Objects for REST Access (AutoREST)
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 lose some flexibility and customizability if you use the AutoREST feature, but it reduces your time and effort to a significant extent. 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.
Note:
This feature is only available for Oracle REST Data Services enabled schemas and not for Oracle APEX workspaces.See Also:
ORDS.ENABLE_OBJECTTo enable Oracle REST Data Services access to one or more specified tables, views, or PL/SQL programs, you can do the following in SQL Developer:
-
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. Once the schema is enabled, you can use that schema or user to login to SQL Developer Web and REST Enable objects in your schema using the web interface.
(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.)
-
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.
- Schema Alias: You can alias the schema in the URIs for your REST APIs. This prevents your API consumers from knowing your database user accounts.
- Authorization Required: This protects the API Catalog endpoints for your schema. If you enable this option, then the requests to the metadata-catalog endpoint on your schema will require authorization.
For detailed usage information, click the Help button in the wizard or dialog box in SQL Developer.
2.3.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.
2.3.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
describespoints to the actual resource. -
The link with relation
canonicaldescribes the resource.
2.3.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/"
}
]
}2.3.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/"
}
]
}2.3.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"
}
]
}2.3.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"
}
]
}2.3.1.6 Get Table Row Using Primary Key
This example retrieves an object by specifying its identifying key values.
Note:
- If a table does not have a primary key, then ORDS uses the ROWID to uniquely address the rows.
- The primary keys are not compatible with a REST interface if they meet any of the following
characteristics:
- End with a period
- Contain // or \\
- Begin with /
- Contains two or more periods in sequence (For example: .., ... )
- Contains any of the following characters: “<”,“>”,“:”,“”",“|”,“?”,“*”,“#”,“; or
,“%”
Requests that contain such primary keys returns HTTP 400 Bad Request as a response. If the primary keys contain any of the preceding incompatible characters, then it is recommended to have a secondary key that does not conflict with the link generation rules.
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/"
}
]
}2.3.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 the POST request can include the primary key value in the body. Or, if the table has an IDENTITY CLAUSE, sequence or trigger, then the primary key column may be omitted. If the table does not have a primary key, then the ROWID of the row is 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/"
}
]
}2.3.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/"
}
]
}2.3.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
}2.3.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 2-1.
Pattern: POST http://<HOST>:<PORT>/ords/<SchemaAlias>/<ObjectAlias>/batchload?<Parameters>
Parameters:
Table 2-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 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 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 |
|
lineMax |
Sets a maximum line length for identifying lines/rows in the data stream. A |
|
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: |
|
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. |
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
2.3.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"}
2.3.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
columnName : sortingNulls
columnName : sortingValues
sortingValues
[sortingValue]
[sortingNulls]
[sortingValue, sortingNulls]
[sortingNulls, sortingValue]
sortingNulls
"NULLS FIRST"
"NULLS LAST"
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.
2.3.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} } Order by with nulls first { "$orderby": {"SALARY": ["ASC", "NULLS FIRST"]} } Order by with nulls last { "$orderby": {"SALARY": ["ASC", "NULLS LAST"]} } 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%"}} ] } }
2.3.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 Oracle REST Data Services, similar to how you enable the views and tables.
Auto Enabling PL/SQL Subprograms
-
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.
Note:
Overloaded package functions and procedures are not supported.2.3.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.
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.
2.3.3.2 Auto-Enabling the PL/SQL Objects
This section explains how to auto-enable the PL/SQL objects through Oracle REST Data Services.
2.3.3.2.1 Auto-Enabling Using the PL/SQL API
You can enable a PL/SQL object using the Oracle REST Data Services PL/SQL API.
To enable the PL/SQL package, use the Oracle REST Data Services 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 2-1 Enabling the PL/SQL Function
To enable the PL/SQL function, use the Oracle REST Data Services 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 2-2 Enabling the PL/SQL Procedure
To enable the PL/SQL procedure, use the Oracle REST Data Services 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;
/2.3.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.-
In SQL Developer, right-click on a package as shown in the following figure:
Figure 2-3 Selecting the Enable REST Service Option
Description of "Figure 2-3 Selecting the Enable REST Service Option" -
Select Enable RESTful Services to display the following wizard page:
Figure 2-4 Auto Enabling the PL/SQL Package Object
Description of "Figure 2-4 Auto Enabling the PL/SQL Package Object"-
Enable object: Enable this option (that is, enable REST access for the package).
-
Object alias: Accept
registry_pkgfor the object alias. -
Authorization required: For simplicity, disable this option.
-
On the RESTful Summary page of the wizard, click Finish.
-
2.3.3.3 Generating the PL/SQL Endpoints
HTTP endpoints are generated dynamically per request for the enabled database objects. Oracle REST Data Services 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 2-3 Generating an Endpoint for the Stored Procedure
CREATE OR REPLACE PROCEDURE MY_SCHEMA.MY_PROC IS
BEGIN
NULL;
END;http://localhost:8080/ords/my_schema/my_proc/Example 2-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;http://localhost:8080/ords/my_schema/my_pkg/MY_PROC
http://localhost:8080/ords/my_schema/my_pkg/MY_FUNCNote:
Endpoints for the proceduremy_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.
2.3.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:{
}2.3.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. Oracle REST Data Services 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
}{
}2.3.4 Support for JSON-Relational Duality View
ORDS supports AutoREST enabling of JSON-relational duality view functionality. This functionality is supported only with Oracle Database 23c or later.
JSON-relational duality view is a revolutionary Oracle Database feature that combines the benefits of relational databases and NoSQL JSON document stores. This feature allows the storage of normalized data in relational tables while exposing it to applications in JSON. Multiple JSON-relational duality views can be created on the same relational data to address different use cases. In other words, the same relational data can have different JSON representations.
2.3.4.1 Table AutoREST Versus JSON-Relational Duality View AutoREST
A JSON-relational duality view is classified as a VIEW in Oracle Database, so it can be AutoRest enabled like any relational view. This section provides a comparison between the AutoREST functionality of JSON-relational duality views with relational tables:
- Exposes the same set of endpoints and methods
(
GET,PUT,POST,DELETE, andHEAD) - Uses the same comma-separated primary key identifier format as that of the associated root table
- Supports the same
Read,Create,Upsert, orDeletesemantics - Generates the same HTTP
If-None-Matchheader ETag digest, where multiple items are processed. - Injects the links hyperlinks field into the response payload
- Supported only with Oracle Database 23c or later
- Passes the JSON payload directly between the request or response and the JSON-relational duality view DATA column.
- Uses the JSON-relational duality view ETag value for HTTP
If-MatchandIf-None-Matchheader conditional matching, where a single item is processed (GET,PUT, andDELETEmethods). - Uses the SODA extended Query by Example (QBE) syntax for rich filtering and ordering
- Uses a JSON-friendly
batchloadformat
2.3.4.2 Support for Enhanced ETag Matching
Oracle REST Data Services (ORDS) integrates with the JSON-relational duality view ETag feature to support optimistic locking and client caching.
HTTP ETag Matching
ORDS uses the JSON-relational duality view generated ETag instead of its
own digest value when evaluating matching headers for single item operations such a
GET (If-None-Match) and
PUT/DELETE (If-Match).
Match Header
|
HTTP False Response
|
Header Example |
|---|---|---|
If-None-Match |
304 – "Not Modified" |
If-None-Match:
"536001F31A8718819AEEF28EC20D8677" |
If-Match |
412 – "Precondition Failed" |
If-Match:
"536001F31A8718819AEEF28EC20D8677" |
Note:
The double-quotes around the ETag value are mandatory.
Database ETag Matching
The Oracle Database also performs ETag matching for
UPDATE operations where an ETag is available in the
_metadata object of the request payload, otherwise this field
is ignored in all other cases.
| Content Example | HTTP 'False' Response |
|---|---|
|
412 – "Precondition Failed" |
2.3.4.3 Enhanced JSON QBE (Query by Example) Filtering
Oracle REST Data Services (ORDS) exposes the same QBE filtering syntax that Simple Oracle Document Access (SODA) uses, providing the user with a roburst set of JSON operators and functionality that are more appropriate for processing JSON.
Although, the syntax currently only applies to JSON-relational duality
views, it is specified in the q URL parameter, similar to the
relational tables and views.
The following example filters the content of the race_dv
JSON-relational duality view, where the points field is greater than
40:
curl http://localhost:8080/ords/janus/race_dv/?q={"points":{"$gt":40}}The following example adds ordering on the points field to
the preceding example:
curl http://localhost:8080/ords/janus/race_dv/?q={"$query":{"points":{"$gt":40}},"$orderby":[{"path":"points","datatype":"number"}]}See Also:
Simple Oracle Document Access (SODA)2.3.4.4 Enhanced JSON Batch Loading
DATA column is mapped
directly to the request payload, the same approach should be applied to batch loading.
Therefore, ORDS provides an optimized batchload endpoint that accepts
one of the following JSON content types:
| Header Content-Type | Description |
|---|---|
application/json |
Freely formatted JSON array of JSON documents
payload. For example:
|
application/json;
boundary=LF |
Linefeed delimited list of JSON documents. Payload
example:
|
Each JSON document is passed to the ORDS batch load service as a row and can be fine-tuned with the query parameters in the same way as in any table.
For example, batchesPerCommit, batchRows, and
truncate can be used to optimize the batch loading process.
The following example shows the batch loading of the
points_dv JSON-relational duality view in batches of 25 rows of
JSON document:
curl -i -X POST --data-binary @points.json -H "Content-Type: application/json"
http://localhost:8080/ords/ordstest/points_dv/batchload?batchRows=25HTTP/1.1 200 OK
...
#INFO Number of rows processed: 2
#INFO Number of rows in error: 0
#INFO Last row processed in final committed batch: 2
SUCCESS: Processed without errors
2.4 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.
2.4.1 About Oracle REST Data Services Mechanisms for Passing Parameters
-
You can use JSON in the body of REST requests, such as the
POSTorPUTmethod, 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
GETmethod, 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
GETmethod, 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.
Related Topics
2.4.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:
- Perform the following steps to setup a handler for a
PUTrequest on theempresource to pass parameters in the body of thePUTmethod in a JSON document to thepromotestored procedure.- 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 2-5 Adding an Anonymous PL/SQL Block to the Handler for the PUT Method
Description of "Figure 2-5 Adding an Anonymous PL/SQL Block to the Handler for the PUT Method" - Click the Parameters tab to set the Bind Parameter as
l_salarychange, the Access Method as anOUTparameter, the Source Type asRESPONSE, and Data Type asINTEGERas 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 2-6 Setting the Bind Parameter l_salarychange to Pass for the PUT Method
Description of "Figure 2-6 Setting the Bind Parameter l_salarychange to Pass for the PUT Method" - 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 2-7 Obtaining the URL to Call from the Details Tab
Description of "Figure 2-7 Obtaining the URL to Call from the Details Tab"
- Using the SQL Worksheet, add the following anonymous PL/SQL block:
- In SQL Developer SQL Worksheet, perform the following
SELECTstatement on theemptable:SELECT * from empto see that thePUTmethod was executed, then select the Data tab to display the records for theEMPtable.Figure 2-8 Displaying the Results from a SQL Query to Confirm the Execution of the PUT Method
Description of "Figure 2-8 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.
2.4.1.2 Using Route Patterns to Pass Parameters
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:
- 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 thejobanddeptnoparameters. For example, for the URI pattern, enter:emp/:job/:deptnoas shown in the following figure.Figure 2-9 Creating a Template Definition to Include a Route Pattern for Some Parameters or Bind Variables
Description of "Figure 2-9 Creating a Template Definition to Include a Route Pattern for Some Parameters or Bind Variables" - 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 2-10 Adding a SQL Query to the Handler
Description of "Figure 2-10 Adding a SQL Query to the Handler" - Test the REST endpoint. In a web browser enter the URL:
http://localhost:8080/ords/ordstest/test/emp/SALESMAN/30as shown in the following figure.Figure 2-11 Using Browser to Show the Results of Using a Route Pattern to Send a GET Method with Some Required Parameter Values
Description of "Figure 2-11 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 Oracle REST Data Services distribution at docs/javadoc/plugin-api/route-patterns.html and this document Oracle REST Data Services Route Patterns
2.4.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 2-12 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 2-12 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.
-
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 encoderto 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 Founderror.
2.4.2 Using SQL/JSON Database Functions
This section describes how to use the SQL/JSON database functions available in Oracle Database 19c Release or later to map the nested JSON objects to and from the hierarchical relational tables.
This section includes the following topics:
2.4.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
:bodybind variable that Oracle REST Data Services 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 21c
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.
-
2.4.2.1.1 Usage of the :body Bind Variable
This section provides some useful tips for using the :body bind variable.
:body bind variable are
as follows:
-
The
:bodybind variable can be accessed, or de-referenced, only once. Subsequent accesses return a NULL value. So, you must first assign the:bodybind variable to the localL_POvariable before using it in the two JSON_Table operations. -
The
:bodybind variable is a BLOB datatype and you can assign it only to a BLOB variable.Note:
SinceL_POis a BLOB variable, you must use theFORMAT JSONphrase after the expression in the JSON_TABLE function. section for more information.The
:bodybind variable can be used with other types of data such as image data. - The
:body_textbind variable is a CLOB datatype and you can assign it only to a CLOB variable. - If you use either
:bodyor:body_text, then you cannot reference individual JSON attributes through the ORDS:bindvariables.
See Also:
Database SQL Language Reference2.4.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 2-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}]}'2.4.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));
2.4.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 2-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;2.4.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 Oracle REST Data Services PL/SQL API to define the REST service and a handler for the POST insert. Then connect to the
jsontableschema 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 Oracle REST Data Services, REST Development pane in SQL Developer to define the REST service.
2.4.2.1.6 Defining the REST Service and Handler using PL/SQL API
This section shows how to define the REST Service and Handler for the POST insert using the Oracle REST Data Services PL/SQL API.
You can alternatively use the Oracle REST Data Services 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;2.4.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:
-
SQL/JSON functions are available with Oracle Database. You can use
json_objectsfor generating JSON objects from the relational tables, andjson_arrayagg, for generating nested JSON arrays from nested (child) relational tables. -
The Oracle REST Data Services 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.
2.4.2.2.1 Bypassing JSON Generation for Relational Data
This section describes and provides solutions for handling responses that are already in a JSON format.
ORDS auto-formats your SQL or PL/SQL results and response to a JSON format before returning to your application. However, in some cases, the complete response body or part of it is already in a JSON format. Following are two such use cases:
Use Case 1: When the response is already in a JSON format
Figure 2-13 Complete Response Body in JSON Format
Description of "Figure 2-13 Complete Response Body in JSON Format"
Select 'application/json',
upper(json_doc)
from json_playThe Media resource in this case is application/json and the browser handles it similar to a BLOB or a PDF.
Use Case 2: One or more columns of the response is already in a JSON format.
If one or more columns are in a JSON format, then such columns in the source query need to be aliased to indicate that the attribute must not be converted to a JSON format.
Select id,
jsons "{}jsons"
from table_with_jsonThe alias text is used to name the nested JSON document attribute.
2.4.2.2.2 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 2-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 = :id2.4.2.2.3 PL/SQL API Calls for Defining Template and GET Handler
This section provides an example of Oracle REST Data Services PL/SQL API call for creating a new template in the module created.
Example 2-8 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; 2.4.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:
2.4.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/
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}]}'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: chunked2.4.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 2-14 Generating Nested JSON Objects
Description of "Figure 2-14 Generating Nested JSON Objects"
2.5 About Working with Dates Using Oracle REST Data Services
Oracle REST Data Services enables developers to create REST interfaces to Oracle Database, Oracle Database 12c JSON Document Store 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.
Oracle REST Data Services uses the RFC3339 standard for encoding dates in strings. Typically, the date format used is dd-mmm-yyyy, for example, 15-Jan-2017. Oracle REST Data Services 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, Oracle REST Data Services 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:
2.5.1 About Datetime Handling with Oracle REST Data Services
As data arrives from a REST request, Oracle REST Data Services 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 Oracle REST Data Services uses during the conversion process.
When constructing responses to REST requests, Oracle REST Data Services 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 Oracle REST Data Services is running and the conversion to Zulu is made from there.
-
Ensure that Oracle REST Data Services 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, Oracle REST Data Services 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 Oracle REST Data Services 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. Oracle REST Data Services does this automatically by default but developers can change the session time zone with an ALTER SESSION statement.
See Aslo:
Internet Date/Time Format2.5.2 About Setting the Time Zone
When Oracle REST Data Services is started, the JVM it runs in obtains and caches the
time zone Oracle REST Data Services uses for various time zone conversions. By default,
the time zone is set to UTC when running ORDS in standalone. This can be overridden by
setting the environment variable JVM_TIMEZONE before running the
ords serve command. 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 Oracle REST Data Services in standalone mode, it is possible to set Java environment variables by specifying them as command line options before the -jar option.
Example 2-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 2-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_TIMEZONE2.5.3 Exploring the Sample RESTful Services in APEX (Tutorial)
Oracle highly recommends to develop Oracle REST Data Services application using SQL Developer Web because it supports the most recent Oracle REST Data Services releases, that is, 3.0.X. APEX provides a tutorial that is useful for learning some basic concepts of REST and Oracle REST Data Services. However, the tutorial uses the earlier Oracle REST Data Services releases, that is, 2.0.X. Following are some of the useful tips discussed on how to use the tutorial:
If your APEX 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.
2.6 Creating RESTful Web Services Using Database Actions
You can create RESTful web services using the Modules, Templates and Handlers pages available in Database Actions.
See Also:
Creating RESTful Web Services2.7 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 or can be provided as a scope in a valid JWT bearer token.
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:
2.7.1 Authentication
Users can be authenticated through first party cookie-based authentication or third party OAuth 2.0-based authentication
Topics:
2.7.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.
2.7.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.
2.7.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 of which resources each client is authorized to access.
Additionally, ORDS supports integration with Identity Providers that can issue JWT access tokens to the party calling the RESTful API for the purposes of accessing the RESTful API. A JWT Profile can be created for a REST-Enabled Schema to define how to validate JWT bearer tokens.
2.7.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.
Related Topics
2.7.3 About Users and Roles for Accessing Resources
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. The user responsible for deploying Oracle REST Data Services on an application server must 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.
2.7.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:
ords config user --help
Format:
ords config user add <name> <roles>
Example:
ords config user add ords_dev "SQL Developer"
Arguments:
-
<user>is the user ID of the user. -
<roles>is the list of roles that the user has. Use a comma to separate multiple roles in the list.
Related Topics
2.7.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:
2.7.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.
2.7.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/
2.7.5.3 Steps for This Tutorial
Follow these steps to protect and access a set of resources.
-
Enable the schema. Connect to the ORDSTEST schema and execute the following PL/SQL statements;
begin ords.enable_schema; commit; end;
-
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"} ] } -
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.
-
-
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.employeesprivilege 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 theexample.employeesprivilege 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.
-
Create an end user. Create a test user with the HR Administrator role, required to access the
examples.employeesprivilege using the file-based user repository. Execute the following command at a command promptords config user add hr_admin "HR Administrator"
When prompted for the password, enter and confirm it.
-
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.
-
-
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 theexamples.employeesprivilege using the client credentials OAuth flow.You can verify that the client was registered and has requested access to the
examples.employeesprivilege 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.
-
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 theexamples.employeesprivilege 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 -
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
clientIdwith the CLIENT_ID value in USER_ORDS_CLIENTS forClient Credentials Example, and replaceclientSecretwith the CLIENT_SECRET value shown in USER_ORDS_CLIENTS forClient 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 theaccess_tokenfield. This value will be different for every request. Theexpires_invalue indicates the number of seconds until the access token expires; in this case the token will expire in one hour (3600 seconds). -
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
accessTokenwith the value of theaccess_tokenfield 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"} ] } -
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 theexamples.employeesprivilege 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 sampleexample.orgweb service.You can verify that the client is now registered and has requested access to the
examples.employeesprivilege 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.
-
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.)
-
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_typeparameter must have a value ofcode.The
client_idparameter must contain the value of the applications client identifier. This is theclient_idvalue determined in a previous step.The
stateparameter 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. Thestatevalue 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
clientIdwith the value of the CLIENT_ID column that was noted previously, and replaceuniqueRandromValuewith a unique unguessable value. The client application must remember this value and verify it against thestateparameter returned as part of the redirect at the end of the authorization flow.If the
client_idis 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 samestateparameter 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
stateparameter and then note the value of thecodeparameter, which will be used in to obtain an access token. -
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
clientIdwith the value of the CLIENT_ID shown in USER_ORDS_CLIENTS forAuthorization Code Example, replaceclientSecretwith the value of the CLIENT_SECRET shown in USER_ORDS_CLIENTS forAuthorization Code Example, and replaceauthorizationCodewith the value of the authorization code noted in a previous step (the value of thecodeparameter).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_tokenfield, and a refresh token is specified by therefresh_tokenfield. This refresh token value can be used to extend the user session without requiring the user to reauthorize the third party application. -
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,
accessTokenwith the value of theaccess_tokenfield 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"} ] } -
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
clientIdwith the value of the CLIENT_ID shown in USER_ORDS_CLIENTS forClient Credentials Client, replaceclientSecretwith the value of the CLIENT_SECRET shown in USER_ORDS_CLIENTS forClient Credentials Client, and replacerefreshTokenwith the value ofrefresh_tokenobtained 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_tokenfield, a new refresh token is specified by therefresh_tokenfield. 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.)
-
-
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 Exampleto access theexamples.employeesprivilege 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 sampleexample.orgweb service.You can verify that the client is now registered and has requested access to the
examples.employeesprivilege 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.
-
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_typeparameter must have a value oftoken.The
client_idparameter must contain the value of the applications client identifier. This is theclient_idvalue determined in a previous step.The
stateparameter 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. Thestatevalue 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
clientIdwith the value of the CLIENT_ID column that was noted previously, and replaceuniqueRandromValuewith a unique unguessable value. The client application must remember this value and verify it against thestateparameter returned as part of the redirect at the end of the authorization flow.If the
client_idis 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 samestateparameter 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
stateparameter and then note the value of the access token. -
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
accessTokenwith the value of theaccess_tokenfield 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"} ] }
Related Topics
See Also:
Managing OAuth Clients2.8 JWT Bearer Token Authentication and Authorization Using JWT Profile
ORDS release 23.3 introduces support for JSON Web Token (JWT). JWT bearer tokens enable the ORDS developers to delegate authentication and authorization to any OAuth2-compliant Identity Provider to issue a JWT access token that ORDS can validate to provide access to ORDS protected resources.
ORDS acts as a resource server in a typical OpenID connect or OAuth2 flow, making it convenient for the developers to access the ORDS APIs from their web applications.
- The JWT subject claim as the authenticated user making the request
- The JWT scope claims as the REST-Enabled schemas ORDS privileges that the user has consented to the application using the privileges on their behalf
2.8.1 About JSON Web Tokens (JWTs)
This section introduces you to the JSON Web Tokens.
A JSON Web Token (JWT) is a compact, URL-safe means of representing claims to be transferred between two parties. The claims in a JWT are encoded as a JSON object. ORDS supports the use of any OAuth2-compliant identity providers such as, OCI IAM with Identity Domains, Oracle Identity Cloud Service (IDCS), Auth0, and Okta. If a JWT is required to access a resource, ORDS validates the JWT using a corresponding public verification key provided by the authorization server.
- A header, that identifies the type of token and the cryptographic
algorithm used to generate the signature.
- The header is required to contain the following reserved
claims.
Note:
A claim is a key value pair, where the key is the name of the claim.alg(algorithm)kid(key id)
- The header can optionally contain the following reserved
claims that ORDS takes into account
x5t(x.509 certificate thumbprint)typ(type)
- The header can also contain custom claims with user-defined names.
- The header is required to contain the following reserved
claims.
- A payload containing claims about the identity of the end user, and
the properties of the JWT.
- A payload is required to contain the following reserved
names of the claims:
sub(subject)aud(audience)iss(issuer)iat(issued at)exp(expiration time)
- The payload can optionally contain the following reserved
claims that ORDS takes into account
scopeorscpnbf(not before)
- A payload can also contain custom claims with user-defined names
- A payload is required to contain the following reserved
names of the claims:
- A signature, to validate the authenticity of the JWT (derived by
base64 encoding the header and the payload).
When using JWTs to control access to the target schema APIs or resources, the JWT Profile in the REST-Enabled schema specifies that the reserved claims in the payload of the JWT must have particular values before ORDS considers the JWT to be valid.
ORDS only accepts the following:
alg(algorithm) values ofRS256,RS384andRS512kid(key id) value that can be matched to a corresponding public verification keyx5t(x.509 certificate thumbprint) if present to a corresponding public verification keytyp(type) if present, requires the value to beJWTaud(audience) that matches the target schemas JWT Profile audienceiss(issuer) that matches the target schema JWT Profile issueriat(issued at ) identifies the time when the JWT was issued and is not be accepted before this time. This claim is used to determine the age of the JWT and enforce the JWT Profile allowed age if it is set.exp(expiration time) identifies the expiration time when or after which the JWT is not accepted for processing.nbf(not before) if present, identifies the time before which the JWT is not accepted for processing.
When a JWT is validated and the payload of JWT contains the scope claim, the ORDS privilege name protecting the resource is verified as being provided in the scope claim before processing.
2.8.2 Prerequisites for JWT Authentication
This section lists the prerequisites for JWT authentication.
Before ORDS can accept authentication and authorization using JWTs:
- An OAuth2-compliant identity provider (for example, OCI IAM with Identity Domains, Oracle Identity Cloud Service (IDCS), Auth0) must have already been set up to issue JWTs for users who are allowed to access the ORDS resources.
- If you want to use custom claims in authorization policies, the identity provider must be set up to add the custom claims to the JWTs that it issues.
See Also:
To validate a JWT using a corresponding public verification key provided by the issuing identity provider:
- the signing algorithm used to generate the signature of JWT must be one
of
RS256,RS384, orRS512 - the public verification key must have a minimum length of 2048 bits and must not exceed 4096 bits
- the public verification key must be specified using the JSON Web Key (JWK) format and ORDS can access it without authentication
The JWK URI
- The URI must be routable from the subnet containing ORDS
- Certain key parameters must be present in the JWKS to verify the signature of the JWT. See Parameters for Verifying JWT Signatures.
- By default, the JWKS can be up to 10000 bytes in size
2.8.3 Creating an ORDS JWT Profile
This section explains how to create an ORDS JWT Profile.
A JWT Profile can be created within a REST-Enabled schema using the
OAUTH.CREATE_JWT_PROFILE procedure. Alternatively,
OAUTH_ADMIN.CREATE_JWT_PROFILE can be used to create a JWT Profile
in other REST-Enabled schemas as long as the user has the
ORDS_ADMINISTRATOR role.
Note:
Only one JWT Profile can be defined per schema. To update an existing JWT Profile, the existing JWT Profile must be deleted before creating a new one.BEGIN
OAUTH.CREATE_JWT_PROFILE(
p_issuer => 'https://identity.oraclecloud.com/',
p_audience => 'ords/myapplication/api' ,
p_jwk_url =>'https://idcs-10a10a10a10a10a10a10a10a.identity.oraclecloud.com/admin/v1/SigningCert/jwk'
);
COMMIT;
END;
/This JWT Profile specifies the issuer, audience, and the JWK URL.
Additionally, an allowed skew and age can be specified. The
p_issuer must be a non null value and must match the
iss claim in the JWT bearer token. The p_audience
must be a non null value and must match with the aud claim in the JWT
bearer token.
The p_jwk_url must be a non null value starting with
https:// and identify the public verification key provided by the
authorization server in a JSON Web Key (JWK) format.
Note:
A JWT scope claim is a JSON string containing a space-separated list of scopes. A protected ORDS resource is protected with a named ORDS privilege. To access the protected ORDS resource, the JWT scope claim must contain a scope with the same name as the protecting ORDS privilege. The scope of an ORDS privilege are case sensitive.See Also:
OAUTH PL/SQL Package Reference2.8.4 JWT Identity Provider Details
The identity provider that issued the JWT, determines the values that
are allowed to specify for the issuer (iss), and the audience
(aud) claims in the JWT. The identity provider that issued the
JWT also determines the URI from where to retrieve the JSON Web Key Set (JWKS) to
verify the signature of the JWT.
| Identity Provider | Issuer (iss) claim | Audience (aud) Claim | Format of URI from which to Retrieve the JWKS |
|---|---|---|---|
| Okta | https://<your-okta-tenant-name>.com |
Customer-specific. The audience configured for the Authorization Server in the Okta Developer Console. |
|
| IDCS | https://identity.oraclecloud.com/ |
Customer-specific. Refer to "Validating Access Tokens" section in Oracle Identity Cloud Service documentation. |
To obtain the JWKS without logging in to Oracle Identity Cloud Service, refer to "Change Default Settings" in Oracle Identity Cloud Service documentation. |
| OCI IAM with Identity Domains | https://identity.oraclecloud.com |
Customer-specific. See "Managing Applications" section in OCI IAM with Identity Domains documentation. |
https://<tenant-base-url>/admin/v1/SigningCert/jwk |
| Auth0 | https://<your-account-name>.auth0.com/ |
Customer-specific. |
https://<your-account-name>.auth0.com/.well-known/jwks.json |
See Also:
- Validating Access Tokens in Oracle Identity Cloud Service documentation.
- Change Default Settings in Oracle Identity Cloud Service documentation.
- Managing Applications in OCI IAM with Identity Domains documentation.
2.8.4.1 Parameters for Verifying JWT Signatures
This section lists the key parameters required to verify the JWT signatures.
To verify the signature on a JWT, ORDS requires that the key parameters are present in the JWKS returned from an URI.
| Key Parameter | Notes |
|---|---|
kid |
The identifier of the key used to sign the JWT. The value
must match the kid claim in the JWT header. For
example, master_key.
|
kty |
The type of the key used to sign the JWT. Note that RSA is currently the only supported key type. |
n |
The public key modulus. |
e |
The public key exponent. |
alg |
The signing algorithm (if present) must be set to one of
RS256, RS384 or
RS512.
|
2.8.4.2 JWT Scopes and ORDS Privileges
You must configure the identity provider that issued the JWT, so as to provide the scope that matches the desired ORDS privilege. If a resource is protected in ORDS using an ORDS privilege, then that privilege name must be defined as a scope. The scope is then available for the application to request on behalf of the user. The issued JWT must then provide that as a scope claim.
Typically, identity providers allow APIs, resources, or scopes to be defined for a particular audience. For example: ORDS REST-Enabled schema defined API. These APIs, resources, or scopes can then be made available to specific applications or clients. The application can then request access tokens on behalf of an authenticated user for that audience and scope.
More than one scope can be requested and provided in the JWT. The protected ORDS resource is accessible as long as one of the scopes matches the ORDS privilege protecting the resource.
2.8.4.3 JWT Subject
ORDS accepts the subject (sub) claim in a valid JWT bearer token as the unique identifier for the user who consented for the application to access their data.
The value of the subject claim in a valid JWT bearer token is bound to the
:current_user implicit parameter and the REMOTE_IDENT OWA
CGI environment variable.
2.8.5 Making Requests to ORDS Using a JWT Bearer Token
Once a JWT Profile has been created for a REST-Enabled schema, the protected ORDS resources in that schema can be accessed by providing a valid JWT bearer token with the request.
Request to an ORDS protected resource is made from a third party application on behalf of a user. The third party application has configured its authentication using an Identity Provider. The same Identity Provider can be configured to issue JWT access tokens for ORDS. After the third party application has acquired a JWT access token from the Identity Provider, it can include the JWT as a bearer token in requests to ORDS. Third party application can request suitable JWT access tokens with the required scope to access the ORDS resource.
curl -X GET http://localhost:8080/ords/myapplication/api/sales / --header "Authorization: Bearer
$JWT"2.9 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. -
oracle.dbtools.autorest.any.schema- Users who want to access all AutoREST services. -
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. SQL Administrator- This role is for the Database API and is required for the pdb lifecycle management operations.
Topics:
2.9.1 About Oracle APEX Users and Oracle REST Data Services Roles
By default, Oracle APEX users do not have any of the Oracle REST Data Services predefined user roles. This means that, by default, APEX users cannot:
-
Invoke protected RESTful Services
-
Register OAuth 2.0 applications
-
Use Oracle SQL Developer to develop RESTful services.
This applies to all APEX users, including APEX developers and administrators. It is therefore important to remember to follow the steps below to add APEX users to the appropriate user groups, so that they can successfully perform the above actions.
Topics:
2.9.1.1 Granting APEX Users Oracle REST Data Services Roles
To give an APEX User any of the roles above, the user must be added to the
equivalent APEX user group. For example, to give the RESTEASY_ADMIN
user the RESTful Services role, follow these steps:
- Log in to the
RESTEASYworkspace as aRESTEASY_ADMIN. - Navigate to Administration and then Manage Users and Groups.
- Click the Edit icon to the left of the
RESTEASY_ADMINuser. - For User Groups, select
RESTful Services. - Click Apply Changes.
2.9.1.2 Automatically Granting APEX Users Oracle REST Data Services Roles
Adding APEX 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 APEX 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 theDeveloperaccount privilege.Developers also inherit any roles defined by theapex.security.user.rolessetting. -
apex.security.administrator.roles- A comma separated list of roles to grant users who have theAdministratoraccount privilege.Administrators also inherit any roles defined by theapex.security.user.rolesandapex.security.developer.rolessettings.
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.
2.9.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:
2.9.2.1 About Defining RESTful Service Roles
A RESTful Service role is an APEX user group. To create a user group to control access to the Gallery RESTful Service, follow these steps. (
-
Log in to the
RESTEASYworkspace as a workspace administrator. -
Navigate to Administration and then Manage Users and Groups.
-
Click the Groups tab.
-
Click Create User Group.
-
For Name, enter
Gallery Users. -
Click Create Group.
2.9.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.
- Navigate to SQL Workshop and then RESTful Services.
- In the Tasks section, click RESTful Service Privileges.
- Click Gallery Access.
- For Assigned Groups, select
Gallery Users. - Click Apply Changes.
With these changes, users must have the Gallery Users role to be able to access the Gallery RESTful service.
2.10 Authenticating Against WebLogic Server User Repositories
Oracle REST Data Services can use APIs provided by WebLogic Server 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 verifying the ability to authenticate against that user.
This document does not describe how to integrate WebLogic Server with the many popular user repository systems such as LDAP repositories, but Oracle REST Data Services can authenticate against such repositories after WebLogic Server 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:
2.10.1 Authenticating Against WebLogic Server
Authenticating a user against WebLogic Server involves the following major steps:
2.10.1.1 Creating a WebLogic Server User
To create a sample WebLogic Server user, follow these steps:
-
Start WebLogic Server if it is not already running
-
Access the WebLogic Server Administration Console (typically
http://server:7001/console), enter your credentials. -
In the navigation tree on the left, click the Security Realms node
-
If a security realm already exists, go to the next step. If a security realm does not exist, create one as follows:
-
Click New.
-
For Name, enter
Test-Realm, then click OK. -
Click Test-Realm.
-
Click the Providers tab.
-
Click New, and enter the following information:
Name:
test-authenticatorType:
DefaultAuthenticator -
Restart WebLogic Server if you are warned that a restart is necessary.
-
Click Test-Realm.
-
-
Click the Users and Groups tab.
-
Click New, and enter the following information:
-
Name:
3rdparty_dev2 -
Password: Enter and confirm the desired password for this user.
-
-
Click OK.
-
Click the Groups tab.
-
Click New., and enter the following information:
-
Name: OAuth2 Client Developer (case sensitive)
-
-
Click OK.
-
Click the Users tab.
-
Click the 3rdparty_dev2 user.
-
Click the Groups tab.
-
In the Chosen list, add
OAuth2 Client Developer. -
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.
2.10.1.2 Verifying the WebLogic Server User
To verify that the WebLogic Server user created can be successfully authenticated, follow these steps:
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).
2.11 Integrating with Existing Group/Role Models
The examples in other sections demonstrate configuring the built-in user repositories of WebLogic Server. 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:
2.11.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:
2.11.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 ({}).
2.11.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.
2.11.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.comalways 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.comhave 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.comorganization to the intermediateDevelopersrole. -
The second rule maps engineers in the
acquired.comorganization to the intermediateDevelopersrole. -
The final rule maps from the intermediate
Developersrole to theRESTful ServicesandSQL Developerroles.
2.12 Integrating Oracle REST Data Services and WebLogic Server
Oracle REST Data Services (ORDS) recommends that for complex or enterprise user identity integrations, customers can leverage the capabilities of WebLogic server. WebLogic server has a rich and diverse set of capabilities to integrate with existing enterprise identity solutions. When Oracle REST Data Services is deployed on the WebLogic server, it can leverage the capabilities of WebLogic server to get secure access to ORDS based RESTful Services.
Once ORDS is configured to work with WebLogic server, the WebLogic server can provide the authenticated user identity and roles. Based on the memberships of the user role, ORDS authorizes access to the protected RESTful Services.
2.12.1 Configuring ORDS to Integrate with WebLogic Server
This section explains how to configure ORDS to work with WebLogic server for authentication.
To configure ORDS to work with WebLogic server
authentication, use the --weblogic-auth option as shown in the
following command when you are generating the deployable ords.war
file:
ords war --weblogic-auth <path for new war
file>.
Specify the --help
option to get help on the ords war command:
ords war --help.
--weblogic-auth option with the ords war command,
the --weblogic-auth option re-configures the web.xml
deployment descriptor in the generated web application file that helps the WebLogic
server to pass any established user identity to ORDS.
Determining the Identity and Roles of the User
After executing the preceding command, the generated web application file must be re-deployed to the WebLogic server.
ORDS uses APIs provided by WebLogic server to retrieve the WLSUser and WLSGroup for the established user identity.
ORDS treats the WLSGroup to be equivalent to the role that the user possesses. For example, if a user or users belongs to a WLSGroup named "Sales Assistant", then ORDS considers such user to have a role named "Sales Assistant".
Retrieving the Authenticated User Information
The user visits the single sign-on login form and obtains a cookie or an access token that asserts the identity and roles. The cookie or the token is then passed to the WebLogic server. The WebLogic server is configured to validate the cookie or token and then map it to a specific user to determine what roles the user possesses. The WebLogic Server performs this operation before passing the request to ORDS. Once ORDS receives the request, it calls the APIs provided by WebLogic server to retrieve the WLSUser and WLSGroup to retrieve the information of the user identity and roles from the WebLogic server.
2.13 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 all the operations. The available subprograms are included in the following PL/SQL packages:
-
ORDS, documented in ORDS PL/SQL Package Reference
-
OAUTH, documented in OAUTH PL/SQL Package Reference
Note:
You must be logged in as the user to the schema that you want to enable or to the ORDS services to be published when using the ORDS package. ORDS is grantedEXECUTE privileges for
public, which means any user can REST enable their
schema and publish REST APIs. You may revoke this
public grant if that is undesirable for your
environments. If you want to work on another schema,
then use the ORDS_ADMIN package, which requires the
ORDS Administrator database role.
-
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:
2.13.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:
-
GETis the default value for thep_methodparameter, and it is used here because that parameter was omitted in this example. -
COLLECTION_FEEDis the default value for thep_methodparameter, and it is used here because that parameter was omitted in this example
-
-
An optional parameter named
whomis specified.
Related Topics
2.13.2 Testing the RESTful Service
To test the RESTful service that you created, start Oracle REST Data Services if it is not already started:
ords -c \path\to\ords\config serve
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.
2.14 Oracle REST Data Services Database Authentication
This section describes how to use the database authentication feature to provide basic authentication for PL/SQL gateway calls.
Database authentication feature is similar to dynamic basic authentication provided by mod-plsql where the user is prompted for the database credentials to authenticate and authorize access to PL/SQL stored procedures.
2.14.1 Installing Sample Database Scripts
This section describes how to install the sample database scripts.
examples\db_auth $ cd sql/
sql $ sql system/<password>
SQLcl: Release Release 18.1.1 Production on Fri Mar 23 14:03:18 2018
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Password? (**********?) ******
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @install <chosen-password>Note:
-
You need to adjust the SQLcl connect string and the user credentials to suit your environment. For this demo scenario, SQLcl connects to the database with service name
orcl -
<chosen-password>is the password you assigned toEXAMPLE_USER1andEXAMPLE_USER2database users. Make a note of this password value for later reference.
-
SAMPLE_PLSQL_APP: A database schema where the protected
SAMPLE_PROCwill be installed. -
EXAMPLE_USER1: A database user granted with execute privilege on
SAMPLE_PLSQL_APP.SAMPLE_PROCprocedure. -
EXAMPLE_USER2: A second database user granted with execute privilege on
SAMPLE_PLSQL_APP.SAMPLE_PROCprocedure.
2.14.2 Enabling the Database Authentication
This section describes how to enable the database authentication feature.
To enable the database authentication feature, do one of the following:
-
For fresh installation of Oracle REST Data Services, update the
/u01/ords/params/ords_paramsproperties file with the following entry:jdbc.auth.enabled=true -
For existing Oracle REST Data Services installation, run the following commands assuming
ords/binis in$PATH, run the following command:ords -c c:\ords\config config --db-pool default set jdbc.auth.enabled trueOutput:
ORDS: Production Release 22.1 on Mon Mar 07 17:01:52 2022 Copyright (c) 2010, 2022, Oracle. All rights reserved. Configuration: /C:/ords/config/ The setting named: jdbc.auth.enabled was set to: true in configuration: default
This setting is applicable to PL/SQL gateway pools (for example, apex.xml ), it does not apply to other pool types such as the ORDS_PUBLIC_USER pool (for example, apex_pu.xml ).
Note:
Thejdbc.auth.enabled setting can be configured per database pool. Alternatively, it can be configured in defaults.xml file so that it is enabled for all pools.
Example 2-11 Setting Enabled for all Pools
jdbc.auth.enabled setting is enabled for all pools.ords $ java -jar ords.war set-property jdbc.auth.enabled true
Mar 23, 2018 2:23:49 PM oracle.dbtools.rt.config.setup.SetProperty execute
INFO: Modified: /tmp/cd/ords/defaults.xml, setting: jdbc.auth.enabled = trueAfter you update the configuration settings, restart the Oracle REST Data Services for the changes to take effect.
2.14.3 Configuring the Request Validation Function
This section describes how to temporarily disable the request validation function.
If you want to invoke only a whitelisted set of stored procedures in the database through the PL/SQL gateway, then you must configure Oracle REST Data Services to use a request validation function (especially when you are using Oracle APEX).
The demo sample procedure used for testing the database authentication feature is not whitelisted, so you must temporarily disable the request validation function.
To disable the request validation function, perform the following steps:
- Navigate to the
<Current Configuration directory>/globaldirectory. - Open the
settings.xmlfile, which stores the Oracle REST Data Services configuration information. -
Look for
security.requestValidationFunctionentry and remove it from the file. -
Save the file.
-
Restart Oracle REST Data Services, if it is already running.
Note:
In production environment, you must use a custom request validation function that whitelists the stored procedures you want to access for your application2.14.4 Testing the Database Authenticated User
This section describes how to test if the database user is authenticated.
Assuming that Oracle REST Data Service is running in a standalone mode on local host and on port 8080, access the following URL in your web browser:
http://localhost:8080/ords/sample_plsql_app.sample_proc
The browser prompts you to enter credentials. Enter example_user1 for user name and enter the password value you noted while installing the sample schema.
The browser displays 'Hello EXAMPLE_USER1!' to demonstrate that the database user was authenticated and the identity of the user was propagated to the database through the OWA CGI variable named REMOTE_USER..
2.14.5 Uninstalling the Sample Database Schema
To uninstall the database schema, run the commands as shown in the following code snippet:
db_auth $ cd sql/
sql $ sql system/<password>
SQLcl: Release Release 18.1.1 Production on Fri Mar 23 14:03:18 2018
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Password? (**********?) ******
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @uninstall2.15 Overview of Pre-hook Functions
This section explains how to use PL/SQL based pre-hook functions that are invoked prior to an Oracle REST Data Services (ORDS) based REST call.
A pre-hook function is typically used to implement application logic that needs to be applied across all REST endpoints of an application. For example a pre-hook enables the following functionality:
- Configure application specific database session state: Configure the session to support a VPD policy.
- Custom authentication and authorization: As the pre-hook is invoked prior to dispatching the REST service, it is used to inspect the request headers and determine the user who is making the request, and also find if that user is authorized to make the request.
- Auditing or metrics gathering: To track information regarding the REST APIs invoked.
2.15.1 Configuring the Pre-hook Function
This section describes how to configure a pre-hook function.
The pre-hook function is configured using
procedure.rest.preHook setting. The value of this
setting must be the name of a stored PL/SQL function.
2.15.2 Using a Pre-hook Function
This section explains how the pre-hook function is used.
A pre-hook must be a PL/SQL function with no arguments and must return a
BOOLEAN value. The function must be executable by the database user
to whom the request is mapped. For example, if the request is mapped to an ORDS enabled
schema, then that schema must be granted the execute privilege on the pre-hook function
(or to PUBLIC).
If the function returns true, then it indicates that the
normal processing of the request must continue. If the function returns
false, then it indicates that further processing of the request
must be aborted.
ORDS invokes a pre-hook function in an OWA (Oracle Web Agent) that is a PL/SQL Gateway Toolkit environment. This means that the function can introspect the request headers and the OWA CGI environment variables, and use that information to drive its logic. The function can also use the OWA PL/SQL APIs to generate a response for the request (for example, in a case where the pre-hook function needs to abort further processing of the request, and provide its own response).
2.15.3 Processing of a Request
The pre-hook function must return true if it determines
that the processing of a request must continue. In such cases, any OWA response produced
by the pre-hook function is ignored (except for cases as detailed in the section Identity Assertion of a User), and the REST service is invoked as usual.
2.15.4 Identity Assertion of a User
This section describes how pre-hook function can make assertions about the identity of the user.
When continuing processing, a pre-hook can make assertions about the identity and the roles assigned to the user who is making the request. This information is used in the processing of the REST service. A pre-hook function can determine this by setting one or both of the following OWA response headers.
X-ORDS-HOOK-USER: Identifies the user making the request, the value is bound to the:current_userimplicit parameter and theREMOTE_IDENTOWA CGI environment variable.X-ORDS-HOOK-ROLES: Identifies the roles assigned to the user. This information is used to determine the authorization of the user to access the REST service. If this header is present thenX-ORDS-HOOK-USERmust also be present.
Note:
X-ORDS-HOOK-USER and X-ORDS-HOOK-ROLES
headers are not included in the response of the REST service. These headers are only
used internally by ORDS to propagate the user identity and roles.
Using these response headers, a pre-hook can integrate with the role based access control model of ORDS. This enables the application developer to build rich integrations with third party authentication and access control systems.
2.15.5 Aborting Processing of a Request
This section explains how the pre-hook function aborts the processing of a request.
If a pre-hook determines that the processing of the REST service should not
continue, then the function must return false value. This value
indicates to ORDS that further processing of the request must not be attempted.
If the pre-hook does not produce any OWA output, then ORDS generates a
403 Forbidden error response page. If the pre-hook produces any OWA
response, then ORDS returns the OWA output as the response. This enables the pre-hook
function to customize the response that client receives when processing of the REST
service is aborted.
2.15.6 Ensuring Pre-hook is Executable
If a schema cannot invoke a pre-hook function, then ORDS generates a
503 Service Unavailable response for any
request against that schema. Since a pre-hook has been configured, it would
not be safe for ORDS to continue processing the request without invoking the
pre-hook function. It is very important that the pre-hook function is
executable by all ORDS enabled schemas. If the pre-hook function is not
executable, then the REST services defined in those schemas will not be
available.
2.15.7 Exceptions Handling by Pre-hook Function
When a pre-hook raises an error condition, for example, when a run-time error occurs,
a NO DATA FOUND exception is
raised. In such cases, ORDS cannot proceed with
processing of the REST service as it would not be
secure. ORDS inteprets any exception raised by the
pre-hook function as a signal that the request is
forbidden and generates a 403
Forbidden response, and does not proceed
with invoking the REST service. Therefore, if the
pre-hook raises an unexpected exception, it forbids
access to that REST service. It is highly
recommended that all pre-hook functions must have a
robust exception handling block so that any
unexpected error conditions are handled
appropriately and do not make REST Services
unavailable.
2.15.8 Pre-hook Function Efficiency
A pre-hook function is invoked for every REST service call. Therefore, the pre-hook function must be designed to be efficient. If a pre-hook function is inefficient, then it has a negative effect on the performance of the REST service call. Invoking the pre-hook involves at least one additional database round trip. It is critical that the ORDS instance and the database are located close together so that the round-trip latency overhead is minimized.
2.15.9 Pre-Hook Examples
This section provides some sample PL/SQL functions that demonstrate different ways in which the pre-hook functionality can be leveraged.
Source code for the examples provided in the following sections is included in the
unzipped Oracle REST Data Services distribution
archive examples/pre_hook/sql
sub-folder.
2.15.9.1 Installing the Examples
This section describes how to install the pre-hook examples.
examples/pre_hook/sql/install.sql script. The following code snippet shows
how to install the examples using Oracle SQLcl command line
interface:pre_hook $ cd examples/pre_hook/sql/
sql $ sql system/<password>
SQLcl: Release Release 18.1.1 Production on Fri Mar 23 14:03:18 2018
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Password? (**********?) ******
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @install <chosen-password>- You need to adjust the SQLcl connect
string and the user credentials to suit your environment. For these demo scenarios, SQLcl
connects to the database with service name
orcl. <chosen-password>is the password you assigned to thePRE_HOOK_TESTdatabase user. Make a note of this password value for later reference.- The
examples/pre_hook/sql/install.sqlcommand creates the following two databases schemas:- The
PRE_HOOK_DEFNSschema where the pre-hook function is defined along with a database table namedcustom_auth_users, where user identities are stored. This table is populated with a single userjoe.bloggs@example.com, whose password is the value assigned for<chosen-password>. - The
PRE_HOOK_TESTSschema where ORDS based REST services that are used to demonstrate the pre-hooks are defined.
- The
2.15.9.1.1 Example: Denying all Access
The simplest pre-hook is one that unilaterally denies access to any REST Service.
false as shown in the following code
snippet:create or replace function deny_all_hook return boolean as
begin
return false;
end;
/
grant execute on deny_all_hook to public;Where:
- The
deny_all_hookpre-hook function always returnsfalsevalue. - Execute privilege is granted to all users. So, any ORDS enabled schema can invoke this function
Configuring ORDS
deny_all_hook pre-hook function, perform the following
steps:
- Locate the folder where the Oracle REST Data Services configuration file is stored.
- Open the
settings.xmlfile and add:<entry key="procedure.rest.preHook">pre_hook_defns.deny_all_hook</entry> - Save the file.
- Restart Oracle REST Data Services.
Try it out
The install script creates an ORDS enabled schema and a REST service which can be
accessed at the following URL (assuming ORDS is deployed on
localhost and listening on port 8080) :
http://localhost:8080/ords/pre_hook_tests/prehooks/userAccess the URL in a browser. You should get a response similar to the following:
403 ForbiddenThis demonstrates that the deny_all_hook pre-hook
function was invoked and it prevented the access to the REST service by returning a
false value.
2.15.9.1.2 Example: Allowing All Access
deny_all_hook pre-hook function to
allow access to all REST service requests as shown in the following code
snippet:create or replace function deny_all_hook return boolean as
begin
return true;
end;
/Try it out
Access the following test URL in a browser:
http://localhost:8080/ords/pre_hook_tests/prehooks/user{
"authenticated_user": "no user authenticated"
}Note:
The REST service executes because the pre-hook function authorized it.Related Topics
2.15.9.1.3 Example: Asserting User Identity
The following code snippet demonstrates how the pre-hook function makes assertions about the user identity and the roles they possess:
create or replace function identity_hook return boolean as
begin
if custom_auth_api.authenticate_owa then
custom_auth_api.assert_identity;
return true;
end if;
custom_auth_api.prompt_for_basic_credentials('Test Custom Realm');
return false;
end;The pre-hook delegates the task of authenticating the user to the
custom_auth_api.authenticate_owa function. If the function
indicates that the user is authenticated, then it invokes the
custom_auth_api.assert_identity procedure to propagate the user
identity and roles to ORDS.
Configuring ORDS
- Locate the folder where the Oracle REST Data Services configuration file is stored.
- Open the
settings.xmlfile and add:<entry key="procedure.rest.preHook">pre_hook_defns.identity_hook</entry></entry> - Save the file.
- Restart Oracle REST Data Services.
Try it out
The install script creates an ORDS enabled schema and a REST service that can be accessed at the following URL (assuming ORDS is deployed on localhost and listening on port 8080):
http://localhost:8080/ords/pre_hook_tests/prehooks/user
Note:
The first time you access the URL, the browser will prompt you to enter your credentials. Enter the user name asjoe.bloggs@example.com and for the password, use the value
you assigned for <chosen-password> when you executed the
install script. Click the link to sign in.
{"authenticated_user":"joe.bloggs@example.com"}2.15.9.2 Uninstalling the Examples
This section explains how to uninstall the examples.
The following code snippet shows how to uninstall the examples:
pre_hook $ cd sql/
sql $ sql system/<password>
SQLcl: Release Release 18.1.1 Production on Fri Mar 23 14:03:18 2018
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Password? (**********?) ******
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @uninstall2.16 Generating Hyperlinks
Oracle REST Data Services (ORDS) provides a mechanism to transform relational result sets into JSON representations, and provides hyperlinks that automatically paginates the result set to allow navigation between the pages of the result set.
For many use cases, it is required to treat certain columns in the result set as hyperlinks. ORDS provides the following simple yet powerful mechanisms for adding hyperlinks to REST resources:
-
Primary Key Hyperlinks: A column with the reserved alias
$.ididentifies the primary key column of a single row in the result set. Such column values are used to form a hyperlink that points to a child resource of the current resource that provides specific details about that particular row in the result set. -
Arbitrary Hyperlinks: A column whose alias starts with the reserved character
$is treated as a hyperlink. The subsequent characters in the column alias indicates the link relation type.
2.16.1 Primary Key Hyperlinks
This section describes how to add primary key hyperlinks.
Typically, when you are modelling a REST API, you need to model the Resource Collection Pattern that enumerates the hyperlinks to the other resources.
EMP table, we can define a service
as shown in the following code
snippet:begin
ords.define_service(
p_module_name => 'links.example',
p_base_path => 'emp-collection/',
p_pattern => '.',
p_source => 'select empno "$.id", empno id, ename employee_name from emp order by empno ename';
commit;
end;- The reserved value '
.' is used for thep_patternvalue. This indicates the path of the resource template in the base path of the resource module,emp-collection/in this example. - The
EMPNOcolumn is aliased as$.id, to produce a hyperlink.
{
"items": [{
"id": 7369,
"employee_name": "SMITH",
"links": [{
"rel": "self",
"href": "http://localhost:8080/ords/ordstest/emp-collection/7369"
}]
},
...
],
"hasMore": false,
"limit": 25,
"offset": 0,
"count": 14,
"links": [{
"rel": "self",
"href": "http://localhost:8080/ords/ordstest/emp-collection/"
}, {
"rel": "describedby",
"href": "http://localhost:8080/ords/ordstest/metadata-catalog/emp-collection/"
}, {
"rel": "first",
"href": "http://localhost:8080/ords/ordstest/emp-collection/"
}]
}
Observe that the value of EMPNO column is concatenated
with the URL of the service to produce a new hyperlink with relation
self. The value is not simply concatenated, it is resolved
using the algorithm specified in RFC3986. Therefore, Oracle REST Data Services
(ORDS) can take the value of the column, and apply the resolution algorithm to
produce a new absolute URL.
See Also:
Section 5 of rfc3986begin
ords.define_template(
p_module_name => 'links.example',
p_pattern => ':id');
ords.define_handler(
p_module_name => 'links.example',
p_pattern => ':id',
p_source_type => ords.source_type_collection_item,
p_source => 'select emp.empno "$.id", emp.* from emp where empno = :id');
commit;
end;
2.16.1.1 Composite Primary Keys
This section describes the support for composite primary keys.
If multiple columns in a query form the primary key of a row, then each of those
columns must be aliased by $.id.N, where N is the position of the
column in the key. ORDS combines such values to form the relative path of the item
URL.
Example:
SELECT
ID1 "$.id.1",
ID2 "$.id.2",
ID3 "$.id.3",
...Related Topics
2.16.2 Arbitrary Hyperlinks
This section describes how to create hyperlinks to point to a resource one level up in the heirarchy.
$ character is treated as a hyperlink. The following example code
snippet shows how an employee resource can provide a hyperlink to their
manager:begin
ords.define_handler(
p_module_name => 'links.example',
p_pattern => ':id',
p_source_type => ords.source_type_collection_item,
p_source => 'select emp.empno "$.id", emp.*, emp.mgr "$related" from emp where empno = :id');commit;end;ORDS treats the column named $related to a hyperlink and
the column value is treated as a path relative to the containing base URI of the
resource. Similar to how $.id column value is transformed into an
absolute URI by applying the algorithm specified in RFC 3986.
See Also:
Section 5.2 of rfc3986.{
"empno": 7369,
"ename": "SMITH",
"job": "CLERK",
"mgr": 7902,
"hiredate": "1980-12-17T00:00:00Z",
"sal": 800,
"comm": null,
"deptno": 20,
"links": [{
"rel": "self",
"href": "http://localhost:8080/ords/ordstest/emp-collection/7369"
}, {
"rel": "describedby",
"href": "http://localhost:8080/ords/ordstest/metadata-catalog/emp-collection/item"
}, {
"rel": "collection",
"href": "http://localhost:8080/ords/ordstest/emp-collection/"
}, {
"rel": "related",
"href": "http://localhost:8080/ords/ordstest/emp-collection/7902"
}]
}
related link points to the manager
resource of the employee. The manager resource in turn has a related link that
points to their manager, and so on up the management chain until you reach employee
number 7839 who is the president of the company and whose mgr
column is null. If the column value is null, then ORDS will not
create a
hyperlink.{
"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-collection/7839"
}, {
"rel": "describedby",
"href": "http://localhost:8080/ords/ordstest/metadata-catalog/emp-collection/item"
}, {
"rel": "collection",
"href": "http://localhost:8080/ords/ordstest/emp-collection/"
}]
}
2.16.2.1 About the related Link Relation
This section explains the use of existing registered link relation types instead of extension link relation types.
As per RFC 8288 Section 2.1.2, any extension link relation must be an URI and not a
simple value. This means that a link relation such as
manager is not a legal link relation according to
the specification. A custom link relation type will reduce interoperability.
If your application uses a non-registered link relation type, then only a
few clients will be able to understand the custom link relation type.
Conversely, if you use registered link relation types, then more clients can
navigate to your link relations. Oracle recommends using existing registered
link relation types instead of extension link relation types.
Related Topics
2.16.2.2 URL Resolution
This section describes how ORDS resolves column values using URI resolution algorithm.
Related Topics
2.16.2.2.1 Child Paths
This section describes how to use the relative paths to refer to the child resources.
select'child/resource'"$related" from dual
https://example.com/ords/some_schema_alias/some/resource,
then the link is as shown in the following code
snippet:{
"rel": "related",
"href": "https://example.com/ords/some_schema_alias/some/child/resource"
}
2.16.2.2.2 Ancestor Paths
This section provides examples to show how ORDS lets you use
../ and ./ syntax to refer to parent paths of the
current resource.
select'../'"$up", './'"$self" from dualhttps://example.com/ords/some_schema_alias/some/collection/, then
the links will be as shown in the following code snippet:
{
"rel": "up",
"href": "https://example.com/ords/some_schema_alias/some/"
},
{
"rel": "self",
"href": "https://example.com/ords/some_schema_alias/some/collection/"
}2.16.2.2.3 Absolute URLs
This section provides examples for the absolute paths.
select'/cool/stuff'"$related", 'https://oracle.com/rest'"$related" from dual
https://example.com/ords/some_schema_alias/some/collection/
the links will be as shown in the following code snippet:
{
"rel": "related",
"href": "https://example.com/cool/stuff"
},
{
"rel": "related",
"href": "https://oracle.com/rest"
}
You can have multiple links for the same link relation.
2.16.2.2.4 Context Root Relative Paths
This section provides example for the context root relative path.
The context root relative path is the URL of the root resource of an ORDS enabled schema.
The following code snippet shows the context root path for the example discussed in the preceding sections:
https://example.com/ords/some_schema_alias/
select'^/another/collection/'"$related"from dual
https://example.com/ords/some_schema_alias/some/collection/, the
link is as shown in the following code snippet:
{
"rel": "related",
"href": "https://example.com/ords/some_schema_alias/another/collection"
}
Any path starting with ^/1 is resolved relative to the context root
path.
2.16.2.2.5 Dynamic Paths
This section describes how you can have dynamic values for the hyperlinks.
emp-collection or
:id resource as shown in the following code
snippet:begin
ords.define_handler(
p_module_name => 'links.example',
p_pattern => ':id',
p_source_type => ords.source_type_collection_item,
p_source => 'select emp.empno "$.id", emp.*, decode(emp.mgr, null, null, '^/managers/' || emp.mgr) "$related" from emp where empno = :id');
commit;
end;-
The value of the
$relatedcolumn is formed from^/managers/:emp.mgrunless the value ofemp.mgrisnull. In such a case, anullvalue is substituted that causes ORDS not to generate the hyperlink.
The following code snippet shows the updated employee resource:
{
"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-collection/7566"
}, {
"rel": "describedby",
"href": "http://localhost:8080/ords/ordstest/metadata-catalog/emp-collection/item"
}, {
"rel": "collection",
"href": "http://localhost:8080/ords/ordstest/emp-collection/"
}, {
"rel": "related",
"href": "http://localhost:8080/ords/ordstest/managers/7839"
}]
}
Note:
Therelated link
now points to the dynamically generated path, that is, to the managers/:id
resource.
2.17 About HTTP Error Responses
ORDS can now generate HTTP error responses in JSON or HTML format. Prior to ORDS release 20.4, only HTML responses were supported. To preserve the backward compatibility, by default, ORDS attempts to automatically determines the best format to render the error responses.
You can configure error.responseFormat setting and force
ORDS to always render the error responses in either HTML or JSON format.
2.17.1 About error.responseFormat
The error.responseFormat setting is a global setting that supports the
following values:
- html - Force all error responses to be in HTML format.
- json - Force all error responses to be in JSON format.
- auto (default value) - Automatically determine most appropriate format for a request.
2.17.1.1 HTML Mode
When error.responseFormat value is set to
html, all the error responses are rendered in HTML
format. This setting can be used to match the behaviour of ORDS 20.3.1 and
prior releases. The HTML format displays properly in web-browsers. However,
for non-human clients, HTML format is verbose and challenging to parse.
2.17.1.2 json Mode
When error.responseFormat value is set to
json, all the error responses are rendered in JSON
format. The JSON format complies with the Problem Details for HTTP APIs
standard. The JSON format is terse, and straightforward for non-human
clients to parse. However, it does not display properly in browsers and is
not user friendly for non-technical users.
2.17.1.3 auto Mode
error.responseFormat is
auto. When this value is configured, ORDS applies
the following rules and automatically chooses the most appropriate format to
use:
- If the client supplies an
Acceptrequest header, whereapplication/jsonorapplication/problem+jsonis the most preferred media type, then the response must be in JSON format. - If the client supplies an
Acceptrequest header wheretext/htmlis the most preferred media type, then the response must be in HTML format. - If the client supplies a
X-Requested-Withheader, then the response must be in JSON format. Presence of this header indicates that the request is initiated from the JavaScript code and so JSON would be the appropriate response format. - If the client supplies an
Originheader, then the response must be in JSON format. Presence of this header indicates that the request is initiated from the JavaScript code and so JSON would be the appropriate response format.- There is one exception to this
rule, if the request method is
POSTand theContent-Typeof the request isapplication/x-www-form-urlencoded, then the response will be in HTML format.
- There is one exception to this
rule, if the request method is
- If the client supplies a
User-Agentheader whose value starts withcurl/, then the response must be in JSON format. cURL is a popular command line tool for making the HTTP requests. The terser JSON format is more readable in a command line environment. If none of the preceding rules apply, then the response will be in HTML format.See Also:
cURL