Oracle REST Data Services API

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/
Admin
Provides the Oracle REST Data Services (ORDS) users the ability to perform Oracle Database...
Tokens:1,096,157
Snippets:10,143
Trust Score:9.7
Update:2 days ago
Show doc for...
Tokens:
Raw
### Install pre-hook examples using SQLcl

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/orddg/developing-REST-applications.html

Shows the commands to install the pre-hook examples from the examples/pre_hook/sql directory using Oracle SQLcl. The output shows the SQLcl version and copyright information.

```bash
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.
```

--------------------------------

### Installation Folder Hierarchy for Oracle REST Data Services Examples

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/orddg/developing-REST-applications.html

Shows the folder structure created when installing Oracle REST Data Services, including the examples folder with soda and getting-started subfolders.

```text
ords
  conf
  docs
  examples
    soda
    getting-started
      ...
```

--------------------------------

### Install ORDS with Basic Connection using --bequeath

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/ordig/installing-and-configuring-oracle-rest-data-services.html

Example of using the ORDS install command with a basic connection and the --bequeath option.

```bash
ords --java-options "-DuseOracleHome=true" --config <PATH TO CONFIG> install --db-pool <POOL NAME> --
bequeath-connect --db-hostname <DB HOST> --db-port <DB PORT> --
db-servicename <DB SERVICENAME> --proxy-user --feature-sdw true 
--log-folder <LOG FOLDER> --password-stdin < <PATH TO FILE>
```

--------------------------------

### SSL Certificate Path Prompt

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/ordig/installing-and-configuring-customer-managed-ords-autonomous-database.html

Example input for the SSL certificate file path during interactive installation.

```text
/path/to/mycert.crt
```

--------------------------------

### Start ORDS Interactive Installation

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/ordig/using-multitenant-architecture-oracle-rest-data-services.html

Command to initiate the interactive installation process for ORDS using a specified configuration folder.

```bash
ords --config <CONFIG_FOLDER> install
```

--------------------------------

### Install ORDS with Custom URL Connection using --bequeath

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/ordig/installing-and-configuring-oracle-rest-data-services.html

Example of using the ORDS install command with a custom URL connection and the --bequeath option.

```bash
ords --java-options "-DuseOracleHome=true" --config <PATH TO CONFIG> install --db-pool <POOL NAME> --
bequeath-connect --db-custom-url <Custom URL> --proxy-user --
feature-sdw true --log-folder <LOG FOLDER> --password-stdin < 
<PATH TO FILE>
```

--------------------------------

### Install ORDS with TNS Connection using --bequeath

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/ordig/installing-and-configuring-oracle-rest-data-services.html

Example of using the ORDS install command with a TNS connection and the --bequeath option.

```bash
ords --java-options "-DuseOracleHome=true" --config <PATH TO CONFIG> install --db-pool <POOL NAME> --
bequeath-connect --db-tns-alias <TNS ALIAS> --db-tns-dir <TNS 
FOLDER> --proxy-user --feature-sdw true --log-folder <LOG 
FOLDER> --password-stdin < <PATH TO FILE>
```

--------------------------------

### SSL Certificate Private Key Path Prompt

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/ordig/installing-and-configuring-customer-managed-ords-autonomous-database.html

Example input for the SSL certificate private key file path during interactive installation.

```text
/path/to/mycert.der
```

--------------------------------

### Install Sample Database Schema

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/orddg/developing-REST-applications.html

Commands to navigate to the SQL directory and execute the installation script using SQLcl.

```bash
 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>
```

--------------------------------

### Get GraphQL Schema Example

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/orddg/graphql-oracle-rest-data-services.html

An example request to retrieve the GraphQL schema for the 'hr' schema.

```http
GET 'http://localhost:8080/ords/hr/_/graphql'
```

--------------------------------

### List configuration settings output

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/ordig/installing-and-configuring-oracle-rest-data-services.html

Example output showing the configuration path, database pool, and a table of settings with their values and sources.

```text
Configuration: /path/to/conf/
 
Database pool: default
 
Setting                        Value                      Source
-------------------         ---------------            ------
db.connectionType           basic                      Pool       
db.hostname                 localhost                  Pool       
db.password                 ******                     Pool Wallet
db.port                     1521                       Pool       
db.servicename              orcl                       Pool       
db.username                 ORDS_PUBLIC_USER           Pool       
feature.sdw                 true                       Pool       
restEnabledSql.active       true                       Pool 
```

--------------------------------

### Interactive installation prompt output

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/ordig/setting-up-a-PL-SQL-Gateway-user.html

Example of the interactive prompts displayed during the ORDS installation process.

```text
...
 
Oracle REST Data Services - Interactive Install
 
  Enter a number to select the database connection type to use
    [1] Basic (host name, port, service name)
    [2] TNS (TNS alias, TNS directory)
    [3] Custom database URL
  Choose [1]: 
  Enter the database host name [localhost]: 
  Enter the database listen port [1521]: 
  Enter the database service name [orcl]: pdb1
  Enter the PL/SQL Gateway database password for EXAMPLE_USER1: 
Confirm password: 
Connecting to database user: EXAMPLE_USER1 url: jdbc:oracle:thin:@//localhost:1521/pdb1
  Provide database user name with administrator privileges.
    Enter the administrator username: sys
  Enter the database password for SYS AS SYSDBA: 
 
Retrieving information.
ORDS is not installed in the database. ORDS installation is required.
 
  Enter a number to update the value or select option A to Accept and Continue
    [1] Connection Type: Basic
    [2] Basic Connection: HOST=localhost PORT=1521 SERVICE_NAME=pdb1
           Administrator User: SYS AS SYSDBA
    [3] Database password for ORDS runtime user (ORDS_PUBLIC_USER): <generate>
    [4] Database password for ORDS gateway user (EXAMPLE_USER1): *****
    [5] ORDS runtime user and schema tablespaces:  Default: SYSAUX Temporary TEMP
    [6] Configure and start ORDS in Standalone Mode: Yes
    [7]    Protocol: HTTP
    [8]       HTTP Port: 8080
    [9]   APEX static resources location: 
    [A] Accept and Continue - Create configuration and Install ORDS in the database
    [Q] Quit - Do not proceed. No changes
  Choose [A]:
```

--------------------------------

### Install Database Schemas for Pre-Hooks

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/orddg/developing-REST-applications.html

Command to execute the installation script for the pre-hook demonstration schemas.

```sql
SQL> @install <chosen-password>
```

--------------------------------

### GET Operation URL Example

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/orddg/developing-REST-applications.html

A sample URL used to perform a GET operation on an employees resource.

```text
https://localhost:8080/ords/hr/api/employees/
```

--------------------------------

### Create a Wallet with SQLcl mkstore

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/ordig/configuring-additional-databases.html

Initializes a new wallet at the specified location using the SQLcl mkstore utility.

```bash
sql /nolog
SQL> mkstore -wrl /tmp/wallet -create
```

--------------------------------

### ORDS Interactive Installation Prompts

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/ordig/using-multitenant-architecture-oracle-rest-data-services.html

Example of the interactive prompts encountered when configuring the database connection for a CDB installation.

```text
Enter a number to select the database connection type to use
    [1] Basic (host name, port, service name)
    [2] TNS (TNS alias, TNS directory)
    [3] Custom database URL
  Choose [1]:
  Enter the database host name [localhost]: myhost
  Enter the database listen port [1521]:    1523
  Enter the database service name [orcl]:   mycdb
```

--------------------------------

### Display help for config command

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/ordig/installing-REST-data-services.html

Example of requesting help for the config command.

```bash
ords config --help
```

--------------------------------

### ORDS Install Command with Bequeath Connection

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/ordig/installing-and-configuring-oracle-rest-data-services.html

Example of a non-interactive ORDS installation command using the --bequeath-connect option.

```bash
ords --config /path/to/myconfig install --db-pool exampledb1 --bequeath-connect --db-only
```

--------------------------------

### ORDS Install Command with Admin User Authentication

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/ordig/installing-and-configuring-oracle-rest-data-services.html

Example of a non-interactive ORDS installation command requiring an administrator user and password file.

```bash
ords --config /path/to/myconfig install --db-pool <pool> --admin-user <username> --db-only --password-stdin < <file>
ords --config /path/to/myconfig install --db-pool <pool> --admin-user ADMIN --db-only --password-stdin < /path/to/special.txt
```

--------------------------------

### Retrieve Table Row via GET

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/orddg/developing-REST-applications.html

Example of a GET request to retrieve a specific row from the 'emp' table using its primary key.

```json
{
 "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/"
  }
 ]
}
```

--------------------------------

### Invoke hello service with parameters

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/orddg/extending-ords-functionality-plugins.html

Demonstrates calling the hello service using a query parameter and a custom HTTP header.

```bash
/ords/javascript-examples/hello?name=Ted

curl --location 'ords/javascript-examples/hello' \
--header 'Agent: Test'
```

```text
Hello Ted
Hello Test
```

--------------------------------

### Display help for config set sub-command

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/ordig/installing-REST-data-services.html

Example of requesting help for the config set sub-command.

```bash
ords config set --help
```

--------------------------------

### Example url-mapping.xml configuration

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/ordig/migration-legacy-commands.html

A sample XML file defining multiple database pools with various base-path and base-url configurations.

```xml
<?xml version="1.0" encoding="UTF-8"?>
<pool-config xmlns="http://xmlns.oracle.com/apex/pool-config">
 <pool name="db1" base-path="/sales" updated="2021-09-23T19:22:19.944Z"/>
 <pool name="db1" base-path="finance/" updated="2021-09-23T19:22:50.189Z"/>
 <pool name="db2" base-url="https://example.com/ords/support" updated="2021-09-23T19:23:04.751Z"/>
 <pool name="db3" base-path="/pls/hr" updated="2021-09-23T19:23:14.832Z"/>
 <pool name="db4" base-path="employee" updated="2021-09-23T19:23:24.252Z"/>
 <pool name="db5" base-url="www.sales.com" updated="2021-09-23T19:23:35.322Z"/>
</pool-config>
```

--------------------------------

### DataView.getBigUint64

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/orddg/third-party-license-information.html

Gets the BigUint64 value at the specified byte offset from the start of the view.

```APIDOC
## DataView.getBigUint64

### Description
Gets the BigUint64 value at the specified byte offset from the start of the view. There is no alignment constraint; multi-byte values may be fetched from any offset.

### Method
getBigUint64

### Parameters
- **byteOffset** (number) - Required - The place in the buffer at which the value should be retrieved.
- **littleEndian** (boolean) - Optional - If false or undefined, a big-endian value should be read.

### Returns
- **bigint** - The BigUint64 value at the specified byte offset.

```

--------------------------------

### Example dads.conf file

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/orddg/migrating-mod_plsql-ords.html

This configuration file demonstrates three locations with basic, basic dynamic, and custom authentication modes using various mod_plsql directives.

```text
# ============================================================================
#                     mod_plsql DAD Configuration File
# ============================================================================
<Location /pls/basic_auth>
  SetHandler pls_handler
  Order deny,allow
  Allow from all
  AllowOverride                     None
  PlsqlDatabaseUsername             PRIVILEGED_USER
  PlsqlDatabasePassword             passwordF0R$0RD5Example
  PlsqlDatabaseConnectString        oracle-ee:1521:ORCLPDB1 ServiceNameFormat
  PlsqlAuthenticationMode           Basic
  PlsqlBeforeProcedure              sample_plsql_app_metadata.beforeProc
  PlsqlAfterProcedure               sample_plsql_app_metadata.afterProc
  PlsqlRequestValidationFunction    sample_plsql_app_metadata.validationFunc
  PlsqlDocumentTablename            privileged_user.doc_table
  PlsqlDocumentProcedure            privileged_user.upload
</Location>
<Location /pls/basic_dynamic_auth>
  SetHandler pls_handler
  Order deny,allow
  Allow from all
  AllowOverride                     None
  PlsqlDatabaseConnectString        oracle-ee:1521:ORCLPDB1 ServiceNameFormat
  PlsqlAuthenticationMode           Basic
  PlsqlBeforeProcedure              sample_plsql_app_metadata.beforeProc
  PlsqlAfterProcedure               sample_plsql_app_metadata.afterProc
  PlsqlRequestValidationFunction    sample_plsql_app_metadata.validationFunc
</location>
<Location /pls/custom_auth>
  SetHandler pls_handler
  Order deny,allow
  Allow from all
  AllowOverride                     None
  PlsqlDatabaseUsername             PRIVILEGED_USER
  PlsqlDatabasePassword             passwordF0R$0RD5Example
  PlsqlDatabaseConnectString        oracle-ee:1521:ORCLPDB1 ServiceNameFormat
  PlsqlAuthenticationMode           CustomOwa
  PlsqlBeforeProcedure              sample_plsql_app_metadata.beforeProc
  PlsqlAfterProcedure               sample_plsql_app_metadata.afterProc
  PlsqlRequestValidationFunction    sample_plsql_app_metadata.validationFunc
</location>
```

--------------------------------

### DataView.getBigInt64

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/orddg/third-party-license-information.html

Gets the BigInt64 value at the specified byte offset from the start of the view.

```APIDOC
## DataView.getBigInt64

### Description
Gets the BigInt64 value at the specified byte offset from the start of the view. There is no alignment constraint; multi-byte values may be fetched from any offset.

### Method
getBigInt64

### Parameters
- **byteOffset** (number) - Required - The place in the buffer at which the value should be retrieved.
- **littleEndian** (boolean) - Optional - If false or undefined, a big-endian value should be read.

### Returns
- **bigint** - The BigInt64 value at the specified byte offset.

```

--------------------------------

### Create Privilege with Roles, Patterns, and Modules

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/orddg/ORDS-reference.html

Example demonstrating the creation of a privilege associated with roles, URL patterns, and a specific module.

```sql
DECLARE
  l_priv_roles owa.vc_arr;
  l_priv_patterns owa.vc_arr;
  l_priv_modules owa.vc_arr;
BEGIN
  l_priv_roles(1) := 'Tickets User';
  l_priv_patterns(1) := '/my/*';
  l_priv_patterns(2) := '/comments/*';
  l_priv_patterns(3) := '/tickets_feed/*';
  l_priv_patterns(4) := '/tickets/*';
  l_priv_patterns(5) := '/categories/*';
  l_priv_patterns(6) := '/stats/*';

  l_priv_modules(1) := 'my.tickets';

  ords.create_role('Tickets User');

  ords.define_privilege(
    p_privilege_name     => 'tickets.privilege',
    p_roles              => l_priv_roles,
    p_patterns           => l_priv_patterns,
    P_modules            => l_priv_modules,
    p_label              => 'Task Ticketing Access',
    p_description        => 'Provides the ability to create, ' || 
                            'update and delete tickets ' || 
                            'and post comments on tickets'
  );
END;
/
```

--------------------------------

### Get getter value from context

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/orddg/third-party-license-information.html

Example of invoking a getter on a provided object instance.

```typescript
let value = context.access.get(instance);
```

--------------------------------

### View ORDS install help

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/ordig/configuring-additional-databases.html

Displays the full help documentation for the ORDS install command.

```bash
ords install --help
```

--------------------------------

### OR operator ($or) - Column context override

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/orddg/developing-REST-applications.html

Example: name starts with S or salary greater than 1000.

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

--------------------------------

### Create Privilege with Roles and Patterns

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/orddg/ORDS-reference.html

Example demonstrating the creation of a privilege associated with roles and URL patterns.

```sql
DECLARE
  l_priv_roles owa.vc_arr;
  l_priv_patterns owa.vc_arr;
BEGIN
  l_priv_roles(1) := 'Tickets User';
  l_priv_patterns(1) := '/my/*';
  l_priv_patterns(2) := '/comments/*';
  l_priv_patterns(3) := '/tickets_feed/*';
  l_priv_patterns(4) := '/tickets/*';
  l_priv_patterns(5) := '/categories/*';
  l_priv_patterns(6) := '/stats/*';

  ords.create_role('Tickets User');

  ords.define_privilege(
    p_privilege_name     => 'tickets.privilege',
    p_roles              => l_priv_roles,
    p_patterns           => l_priv_patterns,
    p_label              => 'Task Ticketing Access',
    p_description        => 'Provides the ability to create, ' || 
                            'update and delete tickets ' || 
                            'and post comments on tickets'
  );
END;
/
```

--------------------------------

### Set up Kerberos Environment Variables

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/ordig/miscellaneous-configuration-options-of-ORDS.html

Define the paths for the Kerberos configuration file and the credential cache.

```bash
export KRB5_CONFIG=<path to krb5.conf>
export KRB5CCNAME=<path to credential cache>
```

--------------------------------

### GET /ords/javascript-examples/now

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/orddg/extending-ords-functionality-plugins.html

An example of inline JavaScript that returns the current UTC time as application/json.

```APIDOC
## GET /ords/javascript-examples/now

### Description
Returns the current UTC time as application/json.

### Method
GET

### Endpoint
/ords/javascript-examples/now

### Response
#### Success Response (200)
- **now** (string) - The current UTC time in ISO 8601 format.

#### Response Example
{
  "now": "2023-08-31T16:08:55.471Z"
}
```

--------------------------------

### Example settings.xml Configuration File

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/ordig/about-REST-configuration-files.html

A sample XML properties file demonstrating the standard format used for ORDS configuration settings.

```xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<entry key="db.connectionType">basic</entry>
<entry key="db.hostname">localhost</entry>
<entry key="db.port">1521</entry>
<entry key="db.servicename">orcl</entry>
<entry key="jdbc.DriverType">thin</entry>
<entry key="jdbc.InitialLimit">3</entry>
<entry key="jdbc.MinLimit">1</entry>
<entry key="jdbc.MaxLimit">10</entry>
<entry key="jdbc.MaxStatementsLimit">10</entry>
<entry key="jdbc.InactivityTimeout">1800</entry>
<entry key="jdbc.statementTimeout">900</entry>
<entry key="jdbc.MaxConnectionReuseCount">1000</entry></properties>
```

--------------------------------

### GET /ords/ordstest/_/par/<par_token>/myprefix/myendpoint/

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/orddg/developing-REST-applications.html

Invokes a pre-authenticated request URL using the relative endpoint and method returned during PAR creation. The example shows a GET request to the pre-authenticated endpoint.

```APIDOC
## [GET] /ords/ordstest/_/par/<par_token>/myprefix/myendpoint/

### Description
Invokes a pre-authenticated request URL using the relative endpoint and method returned during PAR creation. The example shows a GET request to the pre-authenticated endpoint.

### Method
GET

### Endpoint
/ords/ordstest/_/par/<par_token>/myprefix/myendpoint/

### Parameters
#### Path Parameters
- **par_token** (string) - Required - The token part of the pre-authenticated request URL.

### Request Example
curl -i -X GET -H'Content-Type:application/json' http://localhost:8080/ords/ordstest/_/par/<par_token>/myprefix/myendpoint/

### Response
#### Success Response (200)
No response schema is documented in the source.

```

--------------------------------

### ETCD version output

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/ordig/configuring-additional-databases.html

Example output showing the installed version of ETCD and the API version.

```text
etcdctl version: 3.5.17
API version: 3.5
```

--------------------------------

### SQL Loader Options Example

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/orrst/op-data-tools-live-table-feeds-owner-feed_name-get.html

Example configuration for SQL Loader options including formatting and character set settings.

```json
{
    "ignoreblanklines":true,
    "ignoremissingcolumns":true,
    "blankasnull":true,
    "rejectlimit":10000,
    "trimspaces":"lrtrim",
    "characterset":"AL32UTF8",
    "skipheaders":1
}
```

--------------------------------

### Enable Schema Example

Source: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/25.2/orddg/oracle-rest-data-services-administration-pl-sql-package-reference.html

Example demonstrating how to enable the 'tickets' schema for ORDS access.

```plsql
BEGIN
  ORDS_ADMIN.ENABLE_SCHEMA(
    p_schema => 'tickets'
  );
END;
/
```
Tokens:
Raw