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 24.3 Oracle REST Data Services Developer's Guide
  • Changes in Oracle REST Data Services 24.3
• 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 APEX
    • 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.2.6 ORDS RESTful Services and Relevant Specifications
  • 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.3.4 Support for JSON-Relational Duality View
      • 2.3.4.1 Table AutoREST Versus JSON-Relational Duality View AutoREST
      • 2.3.4.2 Support for Enhanced ETag Matching
      • 2.3.4.3 Enhanced JSON QBE (Query by Example) Filtering
      • 2.3.4.4 Enhanced JSON Batch Loading
  • 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 Manually Creating RESTful Services Using Javascript
    • 2.5.1 Allowed JavaScript Structures
    • 2.5.2 Defining the REST Service and JavaScript Handler Using PL/SQL Function
    • 2.5.3 About Executing SQL in Javascript
    • 2.5.4 About Using the Fetch Function
    • 2.5.5 Referencing MLE Environments
  • 2.6 About Working with Dates Using Oracle REST Data Services
    • 2.6.1 About Datetime Handling with Oracle REST Data Services
    • 2.6.2 About Setting the Time Zone
    • 2.6.3 Exploring the Sample RESTful Services in APEX (Tutorial)
  • 2.7 Creating RESTful Web Services Using Database Actions
  • 2.8 Configuring Secure Access to RESTful Services
    • 2.8.1 Authentication
      • 2.8.1.1 First Party Cookie-Based Authentication
      • 2.8.1.2 Third Party OAuth 2.0-Based Authentication
        • 2.8.1.2.1 Two-Legged and Three-Legged OAuth Flows
    • 2.8.2 About Privileges for Accessing Resources
    • 2.8.3 About Users and Roles for Accessing Resources
    • 2.8.4 About the File-Based User Repository
    • 2.8.5 Tutorial: Protecting and Accessing Resources
      • 2.8.5.1 OAuth Flows and When to Use Each
      • 2.8.5.2 Assumptions for This Tutorial
      • 2.8.5.3 Steps for This Tutorial
  • 2.9 JWT Bearer Token Authentication and Authorization Using JWT Profile
    • 2.9.1 About JSON Web Tokens (JWTs)
    • 2.9.2 Prerequisites for JWT Authentication
    • 2.9.3 Creating an ORDS JWT Profile
    • 2.9.4 JWT Identity Provider Details
      • 2.9.4.1 Parameters for Verifying JWT Signatures
      • 2.9.4.2 JWT Scopes and ORDS Privileges
      • 2.9.4.3 JWT Subject
    • 2.9.5 Making Requests to ORDS Using a JWT Bearer Token
  • 2.10 About Oracle REST Data Services User Roles
    • 2.10.1 About Oracle APEX Users and Oracle REST Data Services Roles
      • 2.10.1.1 Granting APEX Users Oracle REST Data Services Roles
      • 2.10.1.2 Automatically Granting APEX Users Oracle REST Data Services Roles
    • 2.10.2 Controlling RESTful Service Access with Roles
      • 2.10.2.1 About Defining RESTful Service Roles
      • 2.10.2.2 Associating Roles with RESTful Privileges
  • 2.11 Authenticating Against WebLogic Server User Repositories
    • 2.11.1 Authenticating Against WebLogic Server
      • 2.11.1.1 Creating a WebLogic Server User
      • 2.11.1.2 Verifying the WebLogic Server User
  • 2.12 Integrating with Existing Group/Role Models
    • 2.12.1 About role-mapping.xml
      • 2.12.1.1 Parameterizing Mapping Rules
      • 2.12.1.2 Dereferencing Parameters
      • 2.12.1.3 Indirect Mappings
  • 2.13 Integrating Oracle REST Data Services and WebLogic Server
    • 2.13.1 Configuring ORDS to Integrate with WebLogic Server
  • 2.14 Using the Oracle REST Data Services PL/SQL API
    • 2.14.1 Creating a RESTful Service Using the PL/SQL API
    • 2.14.2 Testing the RESTful Service
  • 2.15 Oracle REST Data Services Database Authentication
    • 2.15.1 Installing Sample Database Scripts
    • 2.15.2 Enabling the Database Authentication
    • 2.15.3 Configuring the Request Validation Function
    • 2.15.4 Testing the Database Authenticated User
    • 2.15.5 Uninstalling the Sample Database Schema
  • 2.16 Overview of Pre-hook Functions
    • 2.16.1 Configuring the Pre-hook Function
    • 2.16.2 Using a Pre-hook Function
    • 2.16.3 Processing of a Request
    • 2.16.4 Identity Assertion of a User
    • 2.16.5 Aborting Processing of a Request
    • 2.16.6 Ensuring Pre-hook is Executable
    • 2.16.7 Exceptions Handling by Pre-hook Function
    • 2.16.8 Pre-hook Function Efficiency
    • 2.16.9 Using Pre-hook Function with Protected Resources
    • 2.16.10 Pre-Hook Examples
      • 2.16.10.1 Installing the Examples
        • 2.16.10.1.1 Example: Denying all Access
        • 2.16.10.1.2 Example: Allowing All Access
        • 2.16.10.1.3 Example: Asserting User Identity
      • 2.16.10.2 Uninstalling the Examples
  • 2.17 Generating Hyperlinks
    • 2.17.1 Primary Key Hyperlinks
      • 2.17.1.1 Composite Primary Keys
    • 2.17.2 Arbitrary Hyperlinks
      • 2.17.2.1 About the related Link Relation
      • 2.17.2.2 URL Resolution
        • 2.17.2.2.1 Child Paths
        • 2.17.2.2.2 Ancestor Paths
        • 2.17.2.2.3 Absolute URLs
        • 2.17.2.2.4 Context Root Relative Paths
        • 2.17.2.2.5 Dynamic Paths
  • 2.18 About HTTP Error Responses
    • 2.18.1 About error.responseFormat
      • 2.18.1.1 HTML Mode
      • 2.18.1.2 json Mode
      • 2.18.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
  • 6.8 OAUTH.UPDATE_CLIENT_SECRET
  • 6.9 OAUTH.IMPORT_CLIENT
  • 6.10 OAUTH.CREATE_JWT_PROFILE
  • 6.11 OAUTH.DELETE_JWT_PROFILE
• 7 OAUTH_ADMIN PL/SQL Package Reference
  • 7.1 OAUTH_ADMIN.CREATE_JWT_PROFILE
  • 7.2 OAUTH_ADMIN.DELETE_JWT_PROFILE
• 8 Enabling ORDS Database API
  • 8.1 Basic Setup to Enable ORDS Database API
  • 8.2 Advanced Setup to Enable the ORDS Database API
    • 8.2.1 Pluggable Database Lifecycle Management
    • 8.2.2 Disabling PDB Lifecycle Management
  • 8.3 Creating a Default Administrator
  • 8.4 Configuration of Database API Environment Services
  • 8.5 Configuration of Database API with Open Service Broker API Compatible Platforms
• 9 REST-Enabled SQL Service
  • 9.1 REST-Enabled SQL Service Terminology
  • 9.2 Configuring the REST-Enabled SQL Service
  • 9.3 Using cURL with REST-Enabled SQL Service
  • 9.4 Getting Started with the REST-Enabled SQL Service
    • 9.4.1 REST-Enabling the Oracle Database Schema
    • 9.4.2 REST-Enabled SQL Authentication
    • 9.4.3 REST-Enabled SQL Endpoint
  • 9.5 REST-Enabled SQL Service Examples
    • 9.5.1 POST Requests Using application/sql Content-Type
      • 9.5.1.1 Using a Single SQL Statement
      • 9.5.1.2 Using a File with cURL
      • 9.5.1.3 Using Multiple SQL Statements
    • 9.5.2 POST Requests Using application/json Content-Type
      • 9.5.2.1 Using a File with cURL
      • 9.5.2.2 Specifying the Limit Value in a POST Request for Pagination
      • 9.5.2.3 Specifying the Offset Value in a POST Request for Pagination
      • 9.5.2.4 Defining Binds in a POST Request
      • 9.5.2.5 Specifying Batch Statements in a POST Request
    • 9.5.3 Example POST Request with DATE and TIMESTAMP Format
    • 9.5.4 Data Types and Formats Supported
  • 9.6 REST-Enabled SQL Request and Response Specifications
    • 9.6.1 Request Specification
    • 9.6.2 Response Specification
  • 9.7 Supported SQL, SQL*Plus, and SQLcl Statements
    • 9.7.1 Supported SQL Statements
    • 9.7.2 Supported PL/SQL Statements
    • 9.7.3 Supported SQL*Plus Statements
      • 9.7.3.1 Set System Variables
      • 9.7.3.2 Show System Variables
    • 9.7.4 Supported SQLcl Statements
  • 9.8 REST-Enabled SQL Service and MySQL Database
    • 9.8.1 Examples
• 10 GraphQL in Oracle REST Data Services
  • 10.1 GraphQL Terminology
  • 10.2 Enabling GraphQL in Oracle REST Data Services
  • 10.3 Enabling Objects for GraphQL
    • 10.3.1 Accessing Protected REST-Enabled Objects
  • 10.4 Accessing Objects Using GraphQL queries
    • 10.4.1 Getting GraphQL Schema
    • 10.4.2 Simple Query
    • 10.4.3 Join Query
      • 10.4.3.1 Circular Relationships Between Objects
  • 10.5 Examples of Filtering in Queries
    • 10.5.1 Supported Data Types
    • 10.5.2 Filtering by Primary Key
      • 10.5.2.1 Filtering by Composite Primary Key
    • 10.5.3 Where Filter
      • 10.5.3.1 Example: EQUALS (eq) operator
      • 10.5.3.2 Example: Greater than (>) Operator and Date Data Type
      • 10.5.3.3 Example: LIKE (like) operator
      • 10.5.3.4 Example: IN (in) operator
      • 10.5.3.5 Example: NOT (not) Operator
      • 10.5.3.6 Example: AND (and) operator
      • 10.5.3.7 Example: OR (or) operator
      • 10.5.3.8 Example: Where Filter in Children Types
      • 10.5.3.9 Working with Dates/Timestamps Using Filters
  • 10.6 Sorting the Data
    • 10.6.1 Example: Sorting by Multiple Columns
  • 10.7 Keyset Pagination
    • 10.7.1 Example: Pagination with Other Filters
    • 10.7.2 Example: Pagination in Nested Types
  • 10.8 Using Dynamic Arguments in Queries: Variables
  • 10.9 GraphiQL
• 11 Extending ORDS Functionality with Plugins
  • 11.1 Plugin Demonstration Example
  • 11.2 Embedding Graal JavaScript Component
  • 11.3 Plugin Javascript
    • 11.3.1 Example Services Purpose and Use
• 12 Migrating from mod_plsql to ORDS
  • 12.1 Oracle HTTP Server mod_plsql Authentication
  • 12.2 Example Oracle HTTP Server DAD file
  • 12.3 Mapping mod_plsql Settings to ORDS
  • 12.4 Example ORDS Configuration Files
    • 12.4.1 Example Configuration File for Basic Authentication
    • 12.4.2 Example Configuration File for Basic Dynamic Authentication
    • 12.4.3 Example Configuration file for Custom Authentication
  • 12.5 Example ORDS URL Mapping
  • 12.6 Example ORDS Default Configuration
  • 12.7 Oracle REST Data Services Functionality
    • 12.7.1 Basic Authentication
    • 12.7.2 Basic Dynamic Authentication
    • 12.7.3 Custom Authentication
  • 12.8 ORDS Features
    • 12.8.1 Request Validation Function
    • 12.8.2 Pre Process Feature
    • 12.8.3 Post Process Feature
    • 12.8.4 File Upload Feature
    • 12.8.5 Cross-Origin Resource Sharing Feature
    • 12.8.6 Procedure Allow List
      • 12.8.6.1 Configuring ORDS PL/SQL Gateway Allow List
        • 12.8.6.1.1 Authorizing Procedures
        • 12.8.6.1.2 Removing Stored Procedures
        • 12.8.6.1.3 Removing Stored Procedures in Bulk
    • 12.8.7 Monitoring the Allowed Procedures
  • 12.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 Oracle APEX Static Resources with Oracle REST Data Services
• D Third-Party License Information
  • D.1 ANTLR4 Java Runtime 4.13.2
  • D.2 Hack 3.003
  • D.3 Monaco Editor 0.44.0
  • D.4 babel-polyfill 7.20.15
  • D.5 gridstack.js 10.1.0
  • D.6 d3-flame-graph 4.1.3
  • D.7 Dexie 4.0.4
  • D.8 react 18.3.1
  • D.9 react-dom 18.3.1
  • D.10 requirejs 2.3.7
  • D.11 jaxb-runtime 4.0.5
  • D.12 Jetty 10.0.21
  • D.13 jackson-core 2.16.1
  • D.14 Jakarta Servlet 4.0.4
  • D.15 jakarta.inject-api 2.0.1
  • D.16 jQuery UI 1.13.2
  • D.17 jackson-annotations 2.16.1
  • D.18 jackson-databind 2.16.1
  • D.19 jackson-dataformat-xml 2.16.1
  • D.20 graphql-js 16.8.0
  • D.21 graphiql 3.0.4
  • D.22 avsc 5.7.7
  • D.23 D3 7.8.4
  • D.24 long.js 5.2.0
  • D.25 SnappyJS 0.6.1
  • D.26 JavaScript Extension Toolkit (JET) 16.1.4
  • D.27 MongoDB bson 4.10.2
  • D.28 Commons FileUpload 1.5
  • D.29 opentelemetry-java 1.41.0
  • D.30 Google Guava 33.2.1
  • D.31 Eclipse Parsson 1.1.5
  • D.32 xml2js 0.6.2
  • D.33 Jansi 2.4.1
  • D.34 commons-io 2.15.1
  • D.35 Join Monster 4.0.0
  • D.36 SheetJS 0.20.1
  • D.37 graphql-compose 9.0.11
  • D.38 hotkeys-js 3.13.7
  • D.39 swagger-ui 5.17.12
  • D.40 Commons Compress 1.26.0
• Index