How to REST-Enable a Schema with ORDS.ENABLE_SCHEMA

Before ORDS can publish anything from a database schema, the schema itself has to be REST-enabled. ORDS.ENABLE_SCHEMA does that: it registers the schema with ORDS, gives it the alias that appears in every URL, and decides whether its catalog is public. This guide enables a schema, checks its status in the ORDS data dictionary views, and reads the metadata and OpenAPI catalogs ORDS creates for it.

Before You Start

You need ORDS installed and running against your database, and a schema to work in. The examples use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder of the Oracle Database 26ai code repository on GitHub. The schema comes from Oracle Database 26ai SQL and PL/SQL Book.

ORDS in the examples answers at https://localhost:8443/ords/, and NIMBUS is REST-enabled with the URL alias nimbus. Replace the host and port with your own ORDS address. The curl commands use -k because the test server has a self-signed certificate; leave it out when your certificate is trusted. JSON responses are formatted for reading; ORDS returns them on one line.

Syntax

ords.enable_schema(p_enabled             => true,         -- false disables it again
                   p_schema              => 'SCHEMA',
                   p_url_mapping_type    => 'BASE_PATH',
                   p_url_mapping_pattern => 'alias_in_the_url',
                   p_auto_rest_auth      => false);       -- true: the catalog needs a privilege

Run it as the schema owner and commit. All URLs of the schema then start with https://host:port/ords/alias/.

Enable the Schema

Example:

begin
  ords.enable_schema(p_enabled             => true,
                     p_schema              => 'NIMBUS',
                     p_url_mapping_type    => 'BASE_PATH',
                     p_url_mapping_pattern => 'nimbus',
                     p_auto_rest_auth      => false);
  commit;
end;
/
select parsing_schema, pattern, status from user_ords_schemas;

Output:

PL/SQL procedure successfully completed.

PARSING_SCHEMA    PATTERN    STATUS
_________________ __________ __________
NIMBUS            nimbus     ENABLED

NIMBUS is enabled under the alias nimbus. The alias does not have to match the schema name, which keeps the database user name out of public URLs.

Check the Status

USER_ORDS_SCHEMAS shows how the schema is mapped, and USER_ORDS_ENABLED_OBJECTS lists the tables, views, and PL/SQL objects published from it.

Example:

select parsing_schema, pattern, status, auto_rest_auth from user_ords_schemas;

select parsing_object, object_alias, type, status from user_ords_enabled_objects;

Output:

PARSING_SCHEMA    PATTERN    STATUS     AUTO_REST_AUTH
_________________ __________ __________ _________________
NIMBUS            nimbus     ENABLED    DISABLED

PARSING_OBJECT    OBJECT_ALIAS    TYPE     STATUS
_________________ _______________ ________ __________
LOUNGE_OFFERS     offers          TABLE    ENABLED

One table, LOUNGE_OFFERS, has been published as offers; enabling the schema alone publishes nothing.

The Metadata Catalog

Every REST-enabled schema gets a metadata catalog that lists its published objects, with links to each one.

Example:

curl -k https://localhost:8443/ords/nimbus/metadata-catalog/

Output:

{
    "items": [
        {
            "name": "LOUNGE_OFFERS",
            "links": [
                {
                    "rel": "describes",
                    "href": "https://localhost:8443/ords/nimbus/offers/"
                },
                {
                    "rel": "canonical",
                    "href": "https://localhost:8443/ords/nimbus/metadata-catalog/offers/",
                    "mediaType": "application/json"
                },
                {
                    "rel": "alternate",
                    "href": "https://localhost:8443/ords/nimbus/open-api-catalog/offers/",
                    "mediaType": "application/openapi+json"
                }
            ]
        }
    ],
    "hasMore": false,
    "limit": 25,
    "offset": 0,
    "count": 1,
    "links": [
        {
            "rel": "self",
            "href": "https://localhost:8443/ords/nimbus/metadata-catalog/"
        },
        {
            "rel": "first",
            "href": "https://localhost:8443/ords/nimbus/metadata-catalog/"
        }
    ]
}

The open-api-catalog lists the same objects with links to OpenAPI descriptions, which tools such as Swagger UI and Postman can import.

Example:

curl -k https://localhost:8443/ords/nimbus/open-api-catalog/

Output:

{
    "items": [
        {
            "name": "LOUNGE_OFFERS",
            "links": [
                {
                    "rel": "canonical",
                    "href": "https://localhost:8443/ords/nimbus/open-api-catalog/offers/",
                    "mediaType": "application/openapi+json"
                }
            ]
        }
    ],
    "hasMore": false,
    "limit": 25,
    "offset": 0,
    "count": 1,
    "links": [
        {
            "rel": "self",
            "href": "https://localhost:8443/ords/nimbus/open-api-catalog/"
        },
        {
            "rel": "first",
            "href": "https://localhost:8443/ords/nimbus/open-api-catalog/"
        }
    ]
}

An alias that is not mapped to any schema returns 404 Not Found:

Output:

{
    "code": "NotFound",
    "message": "Not Found",
    "type": "tag:oracle.com,2020:error/NotFound",
    "instance": "tag:oracle.com,2020:ecid/TaiGBcRX4AyXIsvcAt3Nug"
}

Protect the Catalog

With p_auto_rest_auth set to TRUE, the catalog requires authorization.

Example:

begin
  ords.enable_schema(p_enabled             => true,
                     p_schema              => 'NIMBUS',
                     p_url_mapping_type    => 'BASE_PATH',
                     p_url_mapping_pattern => 'nimbus',
                     p_auto_rest_auth      => true);
  commit;
end;
/

A call to /ords/nimbus/metadata-catalog/ without credentials now returns 401 Unauthorized, while objects enabled without authorization, such as offers, still answer.

Setting p_auto_rest_auth back to FALSE does not remove the privilege ORDS created for the catalog, so the catalog stays protected. Delete that privilege to open it again:

Example:

-- switching p_auto_rest_auth back to FALSE leaves this privilege in place
select name, label from user_ords_privileges
where  name = 'oracle.dbtools.autorest.privilege.NIMBUS';

begin
  ords.delete_privilege(p_name => 'oracle.dbtools.autorest.privilege.NIMBUS');
  commit;
end;
/

Output:

NAME                                        LABEL
___________________________________________ _________________________________
oracle.dbtools.autorest.privilege.NIMBUS    NIMBUS metadata-catalog access

PL/SQL procedure successfully completed.

Things to Know

• ORDS caches its metadata for a few seconds, so a change can take a moment to show in responses.
• Choose the alias once: changing it later changes every URL that clients use.
• ORDS.DROP_REST_FOR_SCHEMA removes the schema's REST definitions completely, including modules and privileges; disabling with p_enabled set to FALSE keeps them.

Related Guides

• How to Configure ORDS for Oracle APEX
• Publishing REST Services with ORDS and Oracle APEX

Conclusion

ORDS.ENABLE_SCHEMA registers a schema with ORDS under a URL alias and decides whether its catalog is public. Check the result in USER_ORDS_SCHEMAS, browse the metadata and OpenAPI catalogs, and then publish tables, views, and REST modules from the schema.