How to Export all of your Oracle Database REST APIs

Oracle Database’s native REST API technology is implemented in part by having the RESTful Service definitions stored in the database. These are described using the database’s supported languages, e.g. SQL, PL/SQL, and MLE JavaScript.

Ideally your developers ARE source controlling their REST APIs for versioning, testing, and deployments along with the rest of their application code in their CI/CD processes, versus relying on the database itself to be the source of truth.

Which begs the question – how can we get the database REST APIs exported to code, that can then be backed up, versioned, etc?

This post demonstrates a new feature available in Oracle REST Data Services (ORDS) version 25.1 that allows you to export your API definitions and their associated security components across all of the schemas in your database.

Prior to ORDS version 25.1, you would have needed to login to each and every schema to do an export (example.)

In this post I’ll show you how to identify all of your database schemas currently registered for REST APIs, loop thru those, and export the endpoints to a PL/SQL script.

Our PL/SQL script will take the following steps:

1: Ensure you are on ORDS version 25.1 or higher

ORDS 25.1 added a new PL/SQL interface, as noted here in the ReadMe:

ORDS_EXPORT_ADMIN PL/SQL Package. Users with the ORDS_ADMINISTRATOR_ROLE can now export REST-enabled objects from another REST-enabled schema.

We’ll take a closer look at that package in a few moments.

2: Identify the schemas that have been REST enabled

Using some SQL, we can easily query the DBA_ORDS_VIEW using a database account which has been granted the ORDS_ADMINISTRATOR_ROLE.

select *
  from DBA_ORDS_SCHEMAS;

This is the list of SCHEMAS in the database, that have been enabled for REST APIs. It’s also possible they may only have OAUTH2 clients, which could be used for the ORDS REST Enabled SQL feature, or are being used for the Oracle Database API for MongoDB.

3: Loop these schemas, invoking the EXPORT_SCHEMA function

If we look at the ORDS_EXPORT_ADMIN package specification, which is very easy with our SQL Developer Extension for VS Code, we can see the required parameters and their default values:

/* FUNCTION export_schema(p_schema => 'HR')
        p_schema                  IN VARCHAR2,
        p_include_modules         IN BOOLEAN DEFAULT TRUE,
        p_include_privileges      IN BOOLEAN DEFAULT TRUE,
        p_include_roles           IN BOOLEAN DEFAULT TRUE,
        p_include_oauth           IN BOOLEAN DEFAULT TRUE,
        p_include_rest_objects    IN BOOLEAN DEFAULT TRUE,
        p_include_jwt_profiles    IN BOOLEAN DEFAULT TRUE,
        p_include_enable_schema   IN BOOLEAN DEFAULT TRUE,
        p_export_date             IN BOOLEAN DEFAULT TRUE,
        p_runnable_as_admin       IN BOOLEAN DEFAULT TRUE
        
    ) 
    RETURN CLOB;

A parameter of note is ‘P_RUNNABLE_AS_ADMIN’ –

 * @param p_runnable_as_admin     Dictates whether the script will be generated with ORDS_ADMIN calls (vs ORDS package calls). (DEFAULT TRUE)

This allows for a single user to define the ORDS REST APIs, privileges, roles, enabled objects, and JWT Profiles for any schema in the database, whereas the ORDS package is used to do the same for only the CURRENT_USER.

💡 Tip: If you are versioning your REST APIs and deploying them with CI/CD pipelines, our SQLcl Projects feature allows for these scripts to be maintained for your, automatically. The resulting scripts are generated using the same PL/SQL export function.

Putting it all together: the Script

Here is our basic PL/SQL anonymous block, which prints to the console our code for creating all of our ORDS artifacts for all ORDS Enabled schemas in the database. Running this via SQLcl with a SPOOL thrown in will also put your code into the file of your choice.

set serveroutput on
declare
 ords_code clob;
 current_user varchar2(200);
begin
 select user into current_user from dual;
 for item in
  (select PARSING_SCHEMA from DBA_ORDS_SCHEMAS order by 1)
 loop
  dbms_output.put_line('-- Starting Schema Export');
  dbms_output.put_line('-- SCHEMA Defintion for user: ' || item.PARSING_SCHEMA);
  dbms_output.put_line('-- as exported by: ' || current_user);
  select ORDS_METADATA.ords_export_admin.export_schema(p_schema => item.PARSING_SCHEMA) into ords_code;
  dbms_output.put_line(ords_code);
  dbms_output.put_line('-- End of export for schema: '|| item.PARSING_SCHEMA);
  dbms_output.put_line('/');
  dbms_output.put_line(null);
 end loop;
end;
/

The critical lines in this PL/SQL block are are:

• 7-9 querying the list of enabled for REST schemas and iterating them in the loop
• 13 invoking the export function
• 14 ‘printing’ the function result

A quick demo

I have ORDS 25.1 running with 2 REST enabled schemas, HR and HRREST. I’m going to use a privileged user other than those two accounts to find and export them.

If you were to use an account missing the required role (ORDS_ADMINISTRATOR_ROLE), you would run into this error:

  (select PARSING_SCHEMA from DBA_ORDS_SCHEMAS order by 1)
                              *
ERROR at line 7:
ORA-06550: line 7, column 31:
PL/SQL: ORA-01031: insufficient privileges

Running the same code block via my administrator account:

I’ve highlighted in the VS Code script output that the code block executed as SYSTEM, and that the resulting ORDS package calls for defining the ORDS objects are using ORDS_ADMIN, vs the ORDS package.

Looking at the resulting script output, scrolling towards the bottom, we can observe the 2nd schema, HRREST, is also being included. If my apps were across 10 database accounts, our loop would have found and included all 10 of them. Obviously it’s possible to pick and choose the accounts you want to export, vs grabbing ‘everything,’ simply update the script as needed.

  ...
  ORDS_ADMIN.ENABLE_OBJECT(
      p_schema => 'HR',
      p_enabled => TRUE, 
      p_object => 'CVE_NVD_DV',
      p_object_type => 'VIEW',
      p_object_alias => 'cve_nvd_dv',
      p_auto_rest_auth => FALSE);


COMMIT;

END;
-- End of export for schema: HR
/

-- Starting Schema Export
-- SCHEMA Defintion for user: HRREST
-- as exported by: SYSTEM

-- Generated by ORDS REST Data Services 25.1.0.r1001652
-- Schema: HRREST  Date: Thu Apr 17 09:26:14 2025 
--
        
DECLARE
  l_roles     OWA.VC_ARR;
  l_modules   OWA.VC_ARR;
  l_patterns  OWA.VC_ARR;

BEGIN
  ORDS_ADMIN.ENABLE_SCHEMA(
      p_schema => 'HRREST',
      p_enabled             => TRUE,
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'hrrest',
      p_auto_rest_auth      => FALSE);
...

Tip: Use the SQLcl project command to easily package up your application code and objects from the database, including your REST APIs!

Putting it all together.

Ok, let’s do something fun. We’re going to ‘nuke the site from orbit, just to be sure.’ In other words, let’s uninstall and reinstall ORDS.

And after we’re done, we’ll reload all of our APIs, having lost no services or security mechanisms.

Uninstall ORDS

Jeffreys-Mac-mini:bin thatjeffsmith$ ./ords --config /opt/ords/NEWCONFIG uninstall

ORDS: Release 25.1 Production on Thu Apr 17 21:33:15 2025

Copyright (c) 2010, 2025, Oracle.

Configuration:
  /opt/ords/NEWCONFIG

Oracle REST Data Services - Interactive Uninstall

  Enter a number to select the database pool to use or specify the database connection
    [1] default      jdbc:oracle:thin:@//localhost:1521/freepdb1
    [S] Specify the database connection
  Choose [1]: 
  Provide database user name with administrator privileges.
    Enter the administrator username: sys
  Enter the database password for SYS AS SYSDBA: 
Connecting to database user: SYS AS SYSDBA url: jdbc:oracle:thin:@//localhost:1521/freepdb1

Retrieving information.

Container: FREEPDB1
ORDS contains 2 enabled/disabled schemas:
  HR
  HRREST

  Uninstall ORDS in the database
    [1] Yes
    [2] No
  Choose [2]: 1

Yes, we’re going to say, YES, uninstall ORDS even though we know there are enabled schemas (HR, HRREST) that may have ORDS artifacts, which will be lost when the ORDS_METADATA schema is dropped.

DO NOT IGNORE THIS notification! The installer is telling you that we are about to delete any/all REST API definitions from those two schemas. It’s at this point that you will want to ensure you have them defined somewhere else!

Install ORDS

Jeffreys-Mac-mini:bin thatjeffsmith$ ./ords --config /opt/ords/NEWCONFIG install

ORDS: Release 25.1 Production on Thu Apr 17 21:35:59 2025

Copyright (c) 2010, 2025, Oracle.

Configuration:
  /opt/ords/NEWCONFIG

Oracle REST Data Services - Interactive Install

  Enter a number to select the database pool to upgrade ORDS or create an additional database pool
    [1] default      jdbc:oracle:thin:@//localhost:1521/freepdb1
    [C] Create an additional database pool
  Choose [1]: 
  Provide database user name with administrator privileges.
    Enter the administrator username: sys
  Enter the database password for SYS AS SYSDBA: 

Retrieving information.
2025-04-17T21:36:07.974Z INFO        The log file is defaulted to the current working directory located at /opt/ords/25.1/bin/logs/
2025-04-17T21:36:08.100Z INFO        Installing Oracle REST Data Services version 25.1.0.r1001652 in FREEPDB1
2025-04-17T21:36:09.855Z INFO        ... Verified database prerequisites
2025-04-17T21:36:10.256Z INFO        ... Created Oracle REST Data Services proxy user
2025-04-17T21:36:10.859Z INFO        ... Created Oracle REST Data Services schema
2025-04-17T21:36:12.891Z INFO        ... Granted privileges to Oracle REST Data Services
2025-04-17T21:36:17.868Z INFO        ... Created Oracle REST Data Services database objects
2025-04-17T21:36:41.788Z INFO        Completed installation for Oracle REST Data Services version 25.1.0.r1001652. Elapsed time: 00:00:33.652 

2025-04-17T21:36:41.790Z INFO        Log file written to /opt/ords/25.1/bin/logs/ords_install_2025-04-17_213607_97609.log

Querying DBA_ORDS_TEMPLATES

After ORDS has been installed, we can quickly query one of the DBA_ views to see if there are any APIs defined. We should find, exactly ZERO.

select *
  from DBA_ORDS_TEMPLATES;
  
0 rows selected. 

Putting the API definitions, back into the database with our script

Ok, let’s ‘restore’ all of our schemas and their ORDS artifacts, using the script we generated previously.

There were two schemas previously, and we just executed two separate PL/SQL code blocks, and there were no errors. So we should be good, right?

Testing the APIs

With ORDS running, I’m going to login as one of our REST enabled accounts (HRREST), and test one of the AutoREST enabled tables.

It’s at this point, you may find something has gone wrong. If you are unable to login to SQL Developer Web using your database account, it probably means you have not enabled those schemas for ORDS/REST. And it probably also means the REST API definitions are also missing!

Thankfully I did my homework, and everything is as it should be. The REST workshop shows my 3 REST enabled database objects, and I can invoke a GET request on the ORDS collection, and get my array of objects back.

Summary

So what did we learn here?

• ORDS 25.1 has a new package, ORDS_EXPORT_ADMIN which can get all of your ORDS schema definitions
• We can run this script as a single user, which has the ORDS_ADMINISTRATOR_ROLE to create these definitions in any database that has ORDS installed
• We should be source controlling our REST API definitions, just like any other part of our application stack (and associated source code!)