REST modules live in the database as ORDS metadata, so moving an API from development to test and production means exporting that metadata as a script and running it in the target schema. SQLcl's REST EXPORT command and the ORDS_EXPORT package both produce such scripts. This guide exports a module, deletes it, and brings it back from the script.
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
-- SQLcl rest export module_name rest export -- every module of the schema -- PL/SQL select ords_export.export_module(p_module_name => 'module_name') from dual;
Both return a PL/SQL script of ORDS.ENABLE_SCHEMA, DEFINE_MODULE, DEFINE_TEMPLATE, and DEFINE_HANDLER calls.
A Module to Move
The status module has one endpoint that confirms the API and the database are working.
Example:
begin
ords.define_service(p_module_name => 'status',
p_base_path => '/status/',
p_pattern => 'ping',
p_method => 'GET',
p_source_type => ords.source_type_query_one_row,
p_source => q'[select 'ok' as status, count(*) as airports from airports]');
commit;
end;
/Example:
curl -k https://localhost:8443/ords/nimbus/status/ping
Output:
{
"status": "ok",
"airports": 22
}Export with SQLcl
REST EXPORT writes the script to the screen; SPOOL saves it to a file.
Example:
spool /tmp/status_module.sql rest export status spool off
The file /tmp/status_module.sql contains:
Output:
-- Generated by SQLcl REST Data Services 26.2.0.0
-- Exported REST Definitions from ORDS Schema Version 26.2.3.r2371104
-- Schema: NIMBUS Date: Tue Oct 06 04:37:41 UTC 2026
--
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);
ORDS.DEFINE_MODULE(
p_module_name => 'status',
p_base_path => '/status/',
p_items_per_page => 25,
p_status => 'PUBLISHED',
p_comments => NULL);
ORDS.DEFINE_TEMPLATE(
p_module_name => 'status',
p_pattern => 'ping',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'status',
p_pattern => 'ping',
p_method => 'GET',
p_source_type => 'json/query;type=single',
p_items_per_page => 0,
p_mimes_allowed => '',
p_comments => NULL,
p_source =>
'select ''ok'' as status, count(*) as airports from airports'
);
COMMIT;
END;Note the spool path: with a relative name, the test's SQLcl wrote the file into its settings folder (~/.sqldeveloper) rather than the current folder, so give a full path.
Export with ORDS_EXPORT
ORDS_EXPORT.EXPORT_MODULE returns the script as a CLOB, which suits automated deployments. For the hr module:
Example:
select ords_export.export_module(p_module_name => 'hr') as hr_module from dual;
Output:
HR_MODULE
___________________________________________________________________
-- Generated by ORDS REST Data Services 26.2.3.r2371104
-- Schema: NIMBUS Date: Tue Oct 06 04:36:07 2026
--
DECLARE
l_roles OWA.VC_ARR;
l_modules OWA.VC_ARR;
l_patterns OWA.VC_ARR;
BEGIN
ORDS.ENABLE_SCHEMA(
p_enabled => TRUE,
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'nimbus',
p_auto_rest_auth => FALSE);
ORDS.DEFINE_MODULE(
p_module_name => 'hr',
p_base_path => '/hr/',
p_items_per_page => 25,
p_status => 'PUBLISHED',
p_comments => NULL);
ORDS.DEFINE_TEMPLATE(
p_module_name => 'hr',
p_pattern => 'employees',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'hr',
p_pattern => 'employees',
p_method => 'GET',
p_source_type => 'json/collection',
p_mimes_allowed => NULL,
p_comments => NULL,
p_source =>
'select employee_id, first_name, last_name, salary
from employees
order by employee_id');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;Neither export includes the hr.read privilege that protects the hr module; export or recreate privileges and roles separately.
Delete and Import
Example:
begin ords.delete_module(p_module_name => 'status'); commit; end; /
The endpoint now returns 404 Not Found. Running the exported script restores the module:
Example:
@/tmp/status_module.sql select name, uri_prefix, status from user_ords_modules where name = 'status';
Output:
PL/SQL procedure successfully completed. NAME URI_PREFIX STATUS _________ _____________ ____________ status /status/ PUBLISHED
Output (curl https://localhost:8443/ords/nimbus/status/ping):
{
"status": "ok",
"airports": 22
}Things to Know
- The script includes ORDS.ENABLE_SCHEMA with the source schema's alias; change it when the target uses another alias.
- Running the script replaces a module of the same name, with all its templates and handlers.
- Keep exported scripts in version control, next to the database code the handlers call.
Related Guides
- How to Create a REST Module, Template, and Handler with ORDS.DEFINE_MODULE
- How to Get DDL with DBMS_METADATA.GET_DDL
Conclusion
ORDS modules move between databases as PL/SQL scripts made by SQLcl's REST EXPORT or ORDS_EXPORT.EXPORT_MODULE. Spool the script to a full path, keep it in version control, run it in the target schema, and move privileges and roles separately.
