Oracle Database offers a native REST API solution. By that I mean, your REST APIs are defined in the database itself, using the language of the database (SQL, PL/SQL, and MLE JavaScript.)
Ideally your developers ARE controlling their REST APIs for versioning, testing, and deployments along with the rest of their application code in their CI/CD processes, as opposed to relying on the database itself to be the source of truth.
However, you may find it necessary to extract all of your REST API definitions from the database. Scenarios such as:
- you are going to uninstall ORDS and want a backup
- your developers are NOT source controlling their APIs and you want a ‘physical’ copy
By the way, these are REAL scenarios. I worked with a customer just two weeks ago who uninstalled ORDS and ‘lost’ all of their REST APIs.
Note: prior to ORDS v25.1, one would login as the ORDS REST API schema and export that schema’s REST definitions, only.
Step 1: Ensure you are running 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.
Step 2: Identify the schemas that have been enabled for ORDS
This is quite simple, with a privileged user, e.g. has been granted the ORDS_ADMINISTRATOR_ROLE, query the DBA_ORDS_SCHEMAS view:
select *
from DBA_ORDS_SCHEMAS;This is the list of SCHEMAS in the database, that have been enabled for REST APIs. It’s possible they do not have any REST APIs, but perhaps they have OAUTH2 clients, which could be used for REST Enabled SQL endpoints.
Step 3: Loop thru these schemas, and invoke 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;The parameter I want to call out here is ‘P_RUNNABLE_AS_ADMIN.’ Or I should say, I want to call out this comment, regarding this parameter:
* @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.
Our SQLcl Projects feature for example allows you to create a distributable package that can create schema objects (and REST APIs) that fall across multiple schemas. This new package makes that quite easy now.
Here is a fairly basic PL/SQL anonymous block, which will print to the console the code for creating all of our ORDS artifacts for all ORDS Enabled schemas in the database.
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;
/There are really only 3 or 4 important lines of code here. Finding the list of users, looping those values thru the ORDS_EXPORT_ADMIN package and EXPORT_SCHEMA function, and then printing the resulting CLOB via DBMS_OUTPUT.
Let’s give it a go!
I have only 2 REST enabled schemas, HR and HRREST. And I’m going to run this through my SYSTEM user account.
Actually, let’s say I forget to run this as an ORDS_ADMINISTRATOR_ROLE account, I’ll see the expected –
(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
Ok, now thru my actual administrator account:
I’ve highlighted in the output that it’s actually running as SYSTEM, and that the result ORDS package calls for defining the ORDS objects are using the ORDS_ADMIN package.
If we scroll down to the bottom, we can see that the 2nd schema, HRREST, is also included.
...
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);
...Our output has only gone to the console, but we could easily wrap this with a SPOOL command to also send it to a file.
Putting it all together.
Ok, let’s do something fun. Let’s uninstall and reinstall ORDS. And let’s ensure that all of our APIs and security bits remain INTACT.
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.
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
select *
from DBA_ORDS_TEMPLATES;
0 rows selected. Ok, let’s ‘restore’ all of our schemas and their ORDS artifacts, all in a single script!
Testing the APIs
I’m not showing this, but I’ve also started ORDS, it’s running on port 8181. So I’m going to login as the HRREST user, and try one of the AutoREST enabled tables.
If HRREST were not REST enabled by the preceding script, I wouldn’t even be able to login as the HRREST user via SQL Developer Web.
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!)
