Table of Contents

• List of Examples
• List of Figures
• List of Tables
• Title and Copyright Information
• Preface
  • Audience
  • Documentation Accessibility
  • Related Documents
  • Conventions
• Changes in Release 22.4 Oracle REST Data Services Developer's Guide
  • Changes in Oracle REST Data Services 22.4
• 1 Introduction to Oracle REST Data Services
  • 1.1 About Oracle REST Data Services
  • 1.2 Features of Oracle REST Data Services
• 2 Developing Oracle REST Data Services Applications
  • 2.1 Introduction to Relevant Software
    • 2.1.1 Oracle Application Express
    • 2.1.2 REST APIs
  • 2.2 Getting Started with RESTful Services
    • 2.2.1 RESTful Services Terminology
    • 2.2.2 ORDS RESTful Web Services Architecture Diagrams
    • 2.2.3 About Request Path Syntax Requirements
    • 2.2.4 "Getting Started" Documents Included in Installation
    • 2.2.5 About cURL and Testing RESTful Services
  • 2.3 Automatic Enabling of Schema Objects for REST Access (AutoREST)
    • 2.3.1 Examples: Accessing Objects Using RESTful Services
      • 2.3.1.1 Get Schema Metadata
      • 2.3.1.2 Get Object Metadata
      • 2.3.1.3 Get Object Data
      • 2.3.1.4 Get Table Data Using Paging
      • 2.3.1.5 Get Table Data Using Query
      • 2.3.1.6 Get Table Row Using Primary Key
      • 2.3.1.7 Insert Table Row
      • 2.3.1.8 Update/Insert Table Row
      • 2.3.1.9 Delete Using Filter
      • 2.3.1.10 Post by Batch Load
    • 2.3.2 Filtering in Queries
      • 2.3.2.1 FilterObject Grammar
      • 2.3.2.2 Examples: FilterObject Specifications
    • 2.3.3 Auto PL/SQL
      • 2.3.3.1 Method and Content Type Supported for Auto Enabling PL/SQL Objects
      • 2.3.3.2 Auto-Enabling the PL/SQL Objects
        • 2.3.3.2.1 Auto-Enabling Using the PL/SQL API
        • 2.3.3.2.2 Auto-Enabling the PL/SQL Objects Using SQL Developer
      • 2.3.3.3 Generating the PL/SQL Endpoints
      • 2.3.3.4 Resource Input Payload
      • 2.3.3.5 Resource Payload Response
      • 2.3.3.6 Function Return Value
  • 2.4 Manually Creating RESTful Services Using SQL and PL/SQL
    • 2.4.1 About Oracle REST Data Services Mechanisms for Passing Parameters
      • 2.4.1.1 Using JSON to Pass Parameters
      • 2.4.1.2 Using Route Patterns to Pass Parameters
      • 2.4.1.3 Using Query Strings for Optional Parameters
    • 2.4.2 Using SQL/JSON Database Functions
      • 2.4.2.1 Inserting Nested JSON Objects into Relational Tables
        • 2.4.2.1.1 Usage of the :body Bind Variable
        • 2.4.2.1.2 Example of JSON Purchase Order with Nested LineItems
        • 2.4.2.1.3 Table Definitions for PurchaseOrder and LineItems Tables
        • 2.4.2.1.4 PL/SQL Handler Code for a POST Request
        • 2.4.2.1.5 Creating the REST API Service to Invoke the Handler
        • 2.4.2.1.6 Defining the REST Service and Handler using PL/SQL API
      • 2.4.2.2 Generating Nested JSON Objects from Hierachical Relational Data
        • 2.4.2.2.1 Bypassing JSON Generation for Relational Data
        • 2.4.2.2.2 Example to Generate Nested JSON Objects from the  Hierachical Relational Tables
        • 2.4.2.2.3 PL/SQL API Calls for Defining Template and GET Handler
      • 2.4.2.3 Testing the RESTful Services
        • 2.4.2.3.1 Insertion of JSON Object into the Database
        • 2.4.2.3.2 Generating JSON Object from the Database
  • 2.5 About Working with Dates Using Oracle REST Data Services
    • 2.5.1 About Datetime Handling with Oracle REST Data Services
    • 2.5.2 About Setting the Time Zone
    • 2.5.3 Exploring the Sample RESTful Services in Application Express (Tutorial)
  • 2.6 Creating RESTful Web Services Using Database Actions
  • 2.7 Configuring Secure Access to RESTful Services
    • 2.7.1 Authentication
      • 2.7.1.1 First Party Cookie-Based Authentication
      • 2.7.1.2 Third Party OAuth 2.0-Based Authentication
        • 2.7.1.2.1 Two-Legged and Three-Legged OAuth Flows
    • 2.7.2 About Privileges for Accessing Resources
    • 2.7.3 About Users and Roles for Accessing Resources
    • 2.7.4 About the File-Based User Repository
    • 2.7.5 Tutorial: Protecting and Accessing Resources
      • 2.7.5.1 OAuth Flows and When to Use Each
      • 2.7.5.2 Assumptions for This Tutorial
      • 2.7.5.3 Steps for This Tutorial
  • 2.8 About Oracle REST Data Services User Roles
    • 2.8.1 About Oracle Application Express Users and Oracle REST Data Services Roles
      • 2.8.1.1 Granting Application Express Users Oracle REST Data Services Roles
      • 2.8.1.2 Automatically Granting Application Express Users Oracle REST Data Services Roles
    • 2.8.2 Controlling RESTful Service Access with Roles
      • 2.8.2.1 About Defining RESTful Service Roles
      • 2.8.2.2 Associating Roles with RESTful Privileges
  • 2.9 Authenticating Against WebLogic Server User Repositories
    • 2.9.1 Authenticating Against WebLogic Server
      • 2.9.1.1 Creating a WebLogic Server User
      • 2.9.1.2 Verifying the WebLogic Server User
  • 2.10 Integrating with Existing Group/Role Models
    • 2.10.1 About role-mapping.xml
      • 2.10.1.1 Parameterizing Mapping Rules
      • 2.10.1.2 Dereferencing Parameters
      • 2.10.1.3 Indirect Mappings
  • 2.11 Integrating Oracle REST Data Services and WebLogic Server
    • 2.11.1 Configuring ORDS to Integrate with WebLogic Server
  • 2.12 Using the Oracle REST Data Services PL/SQL API
    • 2.12.1 Creating a RESTful Service Using the PL/SQL API
    • 2.12.2 Testing the RESTful Service
  • 2.13 Oracle REST Data Services Database Authentication
    • 2.13.1 Installing Sample Database Scripts
    • 2.13.2 Enabling the Database Authentication
    • 2.13.3 Configuring the Request Validation Function
    • 2.13.4 Testing the Database Authenticated User
    • 2.13.5 Uninstalling the Sample Database Schema
  • 2.14 Overview of Pre-hook Functions
    • 2.14.1 Configuring the Pre-hook Function
    • 2.14.2 Using a Pre-hook Function
    • 2.14.3 Processing of a Request
    • 2.14.4 Identity Assertion of a User
    • 2.14.5 Aborting Processing of a Request
    • 2.14.6 Ensuring Pre-hook is Executable
    • 2.14.7 Exceptions Handling by Pre-hook Function
    • 2.14.8 Pre-hook Function Efficiency
    • 2.14.9 Pre-Hook Examples
      • 2.14.9.1 Installing the Examples
        • 2.14.9.1.1 Example: Denying all Access
        • 2.14.9.1.2 Example: Allowing All Access
        • 2.14.9.1.3 Example: Asserting User Identity
      • 2.14.9.2 Uninstalling the Examples
  • 2.15 Generating Hyperlinks
    • 2.15.1 Primary Key Hyperlinks
      • 2.15.1.1 Composite Primary Keys
    • 2.15.2 Arbitrary Hyperlinks
      • 2.15.2.1 About the related Link Relation
      • 2.15.2.2 URL Resolution
        • 2.15.2.2.1 Child Paths
        • 2.15.2.2.2 Ancestor Paths
        • 2.15.2.2.3 Absolute URLs
        • 2.15.2.2.4 Context Root Relative Paths
        • 2.15.2.2.5 Dynamic Paths
  • 2.16 About HTTP Error Responses
    • 2.16.1 About error.responseFormat
      • 2.16.1.1 HTML Mode
      • 2.16.1.2 json Mode
      • 2.16.1.3 auto Mode
• 3 Implicit Parameters
  • 3.1 List of Implicit Parameters
    • 3.1.1 About the :body parameter
    • 3.1.2 About the :body_text Parameter
    • 3.1.3 About the :content_type Parameter
    • 3.1.4 About the :current_user Parameter
    • 3.1.5 About the :status_code Parameter
    • 3.1.6 About the :forward_location Parameter
    • 3.1.7 About the Pagination Implicit Parameters
      • 3.1.7.1 About the :page_offset Parameter
      • 3.1.7.2 About the :page_size Parameter
      • 3.1.7.3 About the :row_offset Parameter
      • 3.1.7.4 About the :row_count Parameter
      • 3.1.7.5 About the :fetch_offset Parameter
      • 3.1.7.6 About the :fetch_size Parameter
      • 3.1.7.7 About Automatic Pagination
      • 3.1.7.8 About Manual Pagination
• 4 ORDS PL/SQL Package Reference
  • 4.1 ORDS.CREATE_ROLE
  • 4.2 ORDS.CREATE_SERVICE
  • 4.3 ORDS.DEFINE_HANDLER
  • 4.4 ORDS.DEFINE_MODULE
  • 4.5 ORDS.DEFINE_PARAMETER
  • 4.6 ORDS.DEFINE_PRIVILEGE
  • 4.7 ORDS.DEFINE_SERVICE
  • 4.8 ORDS.DEFINE_TEMPLATE
  • 4.9 ORDS.DELETE_MODULE
  • 4.10 ORDS.DELETE_PRIVILEGE
  • 4.11 ORDS.DELETE_ROLE
  • 4.12 ORDS.DROP_REST_FOR_SCHEMA
  • 4.13 ORDS.ENABLE_OBJECT
  • 4.14 ORDS.DROP_REST_FOR_OBJECT
  • 4.15 ORDS.ENABLE_SCHEMA
  • 4.16 ORDS.PUBLISH_MODULE
  • 4.17 ORDS.RENAME_MODULE
  • 4.18 ORDS.RENAME_PRIVILEGE
  • 4.19 ORDS.RENAME_ROLE
  • 4.20 ORDS.SET_MODULE_ORIGINS_ALLOWED
  • 4.21 ORDS.SET_URL_MAPPING
  • 4.22 ORDS.SET_SESSION_DEFAULTS
  • 4.23 ORDS.RESET_SESSION_DEFAULTS
  • 4.24 ORDS.SET_PROPERTY
  • 4.25 ORDS.UNSET_PROPERTY
• 5 Oracle REST Data Services Administration PL/SQL Package Reference
  • 5.1 ORDS_ADMIN.CREATE_ROLE
  • 5.2 ORDS_ADMIN.DEFINE_HANDLER
  • 5.3 ORDS_ADMIN.DEFINE_MODULE
  • 5.4 ORDS_ADMIN.DEFINE_PARAMETER
  • 5.5 ORDS_ADMIN.DEFINE_PRIVILEGE
  • 5.6 ORDS_ADMIN.DEFINE_SERVICE
  • 5.7 ORDS_ADMIN.DEFINE_TEMPLATE
  • 5.8 ORDS_ADMIN.DELETE_MODULE
  • 5.9 ORDS_ADMIN.DELETE_PRIVILEGE
  • 5.10 ORDS_ADMIN.DELETE_ROLE
  • 5.11 ORDS_ADMIN.DROP_REST_FOR_SCHEMA
  • 5.12 ORDS_ADMIN.ENABLE_OBJECT
  • 5.13 ORDS_ADMIN.DROP_REST_FOR_OBJECT
  • 5.14 ORDS_ADMIN.ENABLE_SCHEMA
  • 5.15 ORDS_ADMIN.PUBLISH_MODULE
  • 5.16 ORDS_ADMIN.RENAME_MODULE
  • 5.17 ORDS_ADMIN.RENAME_PRIVILEGE
  • 5.18 ORDS_ADMIN.RENAME_ROLE
  • 5.19 ORDS_ADMIN.SET_MODULE_ORIGINS_ALLOWED
  • 5.20 ORDS_ADMIN.SET_URL_MAPPING
  • 5.21 ORDS_ADMIN.ENABLE_HOUSEKEEPING_JOB
  • 5.22 ORDS_ADMIN.DROP_HOUSEKEEPING_JOB
  • 5.23 ORDS_ADMIN.PERFORM_HOUSEKEEPING
  • 5.24 ORDS_ADMIN.SET_SESSION_DEFAULTS
  • 5.25 ORDS_ADMIN.RESET_SESSION_DEFAULTS
  • 5.26 ORDS_ADMIN.PROVISION_ADMIN_ROLE
  • 5.27 ORDS_ADMIN.PROVISION_RUNTIME_ROLE
  • 5.28 ORDS_ADMIN.UNPROVISION_ ROLES
  • 5.29 ORDS_ADMIN.CONFIG_PLSQL_GATEWAY
  • 5.30 ORDS_ADMIN.SET_PROPERTY
  • 5.31 ORDS_ADMIN.SET_PROPERTY
  • 5.32 ORDS_ADMIN.UNSET_PROPERTY
• 6 OAUTH PL/SQL Package Reference
  • 6.1 OAUTH.CREATE_CLIENT
  • 6.2 OAUTH.DELETE_CLIENT
  • 6.3 OAUTH.GRANT_CLIENT_ROLE
  • 6.4 OAUTH.RENAME_CLIENT
  • 6.5 OAUTH.REVOKE_CLIENT_ROLE
  • 6.6 OAUTH.UPDATE_CLIENT
  • 6.7 OAUTH.ROTATE_CLIENT_SECRET
• 7 Enabling ORDS Database API
  • 7.1 Basic Setup to Enable ORDS Database API
  • 7.2 Advanced Setup to Enable the ORDS Database API
    • 7.2.1 Pluggable Database Lifecycle Management
    • 7.2.2 Disabling PDB Lifecycle Management
  • 7.3 Creating a Default Administrator
  • 7.4 Configuration of Database API Environment Services
  • 7.5 Configuration of Database API with Open Service Broker API Compatible Platforms
• 8 REST-Enabled SQL Service
  • 8.1 REST-Enabled SQL Service Terminology
  • 8.2 Configuring the REST-Enabled SQL Service
  • 8.3 Using cURL with REST-Enabled SQL Service
  • 8.4 Getting Started with the REST-Enabled SQL Service
    • 8.4.1 REST-Enabling the Oracle Database Schema
    • 8.4.2 REST-Enabled SQL Authentication
    • 8.4.3 REST-Enabled SQL Endpoint
  • 8.5 REST-Enabled SQL Service Examples
    • 8.5.1 POST Requests Using application/sql Content-Type
      • 8.5.1.1 Using a Single SQL Statement
      • 8.5.1.2 Using a File with cURL
      • 8.5.1.3 Using Multiple SQL Statements
    • 8.5.2 POST Requests Using application/json Content-Type
      • 8.5.2.1 Using a File with cURL
      • 8.5.2.2 Specifying the Limit Value in a POST Request for Pagination
      • 8.5.2.3 Specifying the Offset Value in a POST Request for Pagination
      • 8.5.2.4 Defining Binds in a POST Request
      • 8.5.2.5 Specifying Batch Statements in a POST Request
    • 8.5.3 Example POST Request with DATE and TIMESTAMP Format
    • 8.5.4 Data Types and Formats Supported
  • 8.6 REST-Enabled SQL Request and Response Specifications
    • 8.6.1 Request Specification
    • 8.6.2 Response Specification
  • 8.7 Supported SQL, SQL*Plus, and SQLcl Statements
    • 8.7.1 Supported SQL Statements
    • 8.7.2 Supported PL/SQL Statements
    • 8.7.3 Supported SQL*Plus Statements
      • 8.7.3.1 Set System Variables
      • 8.7.3.2 Show System Variables
    • 8.7.4 Supported SQLcl Statements
  • 8.8 REST-Enabled SQL Service and MySQL Database
    • 8.8.1 Examples
• 9 Migrating from mod_plsql to ORDS
  • 9.1 Oracle HTTP Server mod_plsql Authentication
  • 9.2 Example Oracle HTTP Server DAD file
  • 9.3 Mapping mod_plsql Settings to ORDS
  • 9.4 Example ORDS Configuration Files
    • 9.4.1 Example Configuration File for Basic Authentication
    • 9.4.2 Example Configuration File for Basic Dynamic Authentication
    • 9.4.3 Example Configuration file for Custom Authentication
  • 9.5 Example ORDS URL Mapping
  • 9.6 Example ORDS Default Configuration
  • 9.7 Oracle REST Data Services Functionality
    • 9.7.1 Basic Authentication
    • 9.7.2 Basic Dynamic Authentication
    • 9.7.3 Custom Authentication
  • 9.8 ORDS Features
    • 9.8.1 Request Validation Function
    • 9.8.2 Pre Process Feature
    • 9.8.3 Post Process Feature
    • 9.8.4 File Upload Feature
    • 9.8.5 Cross-Origin Resource Sharing Feature
    • 9.8.6 Procedure Allow List
      • 9.8.6.1 Configuring ORDS PL/SQL Gateway Allow List
        • 9.8.6.1.1 Authorizing Procedures
        • 9.8.6.1.2 Removing Stored Procedures
        • 9.8.6.1.3 Removing Stored Procedures in Bulk
  • 9.9 Modifying Synonyms
• A Setting-up a PL/SQL Gateway User
• B Oracle REST Data Services Database Type Mappings
  • B.1 Oracle Built-in Types
  • B.2 Handling Structural Database Types
  • B.3 Oracle Geospacial Encoding
  • B.4 Enabling Database Mapping Support
• C Troubleshooting Oracle REST Data Services
  • C.1 Enabling Detailed Request Error Messages
  • C.2 ORDS User Defined Service
  • C.3 Configuring Application Express Static Resources with Oracle REST Data Services
• D Third-Party License Information
  • D.1 JavaScript Extension Toolkit (JET) 11.1.6
  • D.2 long.js 5.2.0
  • D.3 gridstack.js 4.2.6
  • D.4 HttpComponents 5.14
  • D.5 commons-io 2.11.0
  • D.6 jackson-module-jaxb-annotations 2.13.4
  • D.7 jackson-annotations 2.13.4
  • D.8 jackson-databind 2.13.4.2
  • D.9 jackson-dataformat-xml 2.13.4
  • D.10 jackson-core 2.13.4
  • D.11 jackson-jr 2.13.4
  • D.12 Jakarta Json Processing API (JSON-P) 2.1.1
  • D.13 MongoDB bson 4.6.1
  • D.14 SnappyJS 0.6.1
  • D.15 Google Guava 31.1
  • D.16 history 5.0.0
  • D.17 Eclipse Yasson 2.0.4
  • D.18 Eclipse Parsson 1.1.1
  • D.19 swagger-ui 3.51.2
  • D.20 Jetty 10.0.12
  • D.21 Javassist 3.29.2-GA
  • D.22 avsc 5.7.3
  • D.23 babel 7.12.1
  • D.24 CodeMirror 5.53.2
  • D.25 Dexie.js 3.0.3
  • D.26 SheetJS 0.17.0
  • D.27 Monaco Editor 0.26.1
• Index