8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | 24ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Oracle REST Data Services (ORDS) : Export Metadata Using the ORDS_EXPORT_ADMIN Package

The ORDS_EXPORT_ADMIN package was introduced in ORDS version 25.1, allowing us to export ORDS metadata from SQL and PL/SQL.

Related articles.

Setup

In order to run the examples in this article we need to do the following setup.

We create a new test user.

 conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba --drop user testuser1 cascade; create user testuser1 identified by testuser1 default tablespace users quota unlimited on users; grant create session, create table, create type to testuser1;

We create an object in the test user.

 conn testuser1/testuser1@//localhost:1521/freepdb1 create table emp ( empno number(4,0), ename varchar2(10 byte), job varchar2(9 byte), mgr number(4,0), hiredate date, sal number(7,2), comm number(7,2), deptno number(2,0), constraint pk_emp primary key (empno) ); insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20); insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30); insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30); insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20); insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30); insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30); insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10); insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20); insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10); insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30); insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20); insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30); insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20); insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10); commit;

We enable ORDS for our test user and create a service that queries the test table.

 begin ords.enable_schema( p_enabled => true, p_schema => 'TESTUSER1', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'hr', p_auto_rest_auth => false ); commit; end; / begin ords.define_service( p_module_name => 'rest-v1', p_base_path => 'rest-v1/', p_pattern => 'employees/', p_method => 'GET', p_source_type => ords.source_type_collection_feed, p_source => 'select * from emp', p_items_per_page => 0); commit; end; /

EXPORT_SCHEMA Function

The EXPORT_SCHEMA schema function returns a CLOB containing the ORDS metadata for the specified schema. By default the export includes all metadata for the schema.

 conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba set long 1000000 select ords_export_admin.export_schema(p_schema => 'TESTUSER1') as ords_metadata from dual; ORDS_METADATA __________________________________________________________ -- Generated by ORDS REST Data Services 25.1.1.r1411642 -- Schema: TESTUSER1 Date: Fri Jun 20 08:32:11 2025 -- DECLARE l_roles OWA.VC_ARR; l_modules OWA.VC_ARR; l_patterns OWA.VC_ARR; BEGIN ORDS_ADMIN.ENABLE_SCHEMA( p_schema => 'TESTUSER1', p_enabled => TRUE, p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'hr', p_auto_rest_auth => FALSE); ORDS_ADMIN.DEFINE_MODULE( p_schema => 'TESTUSER1', p_module_name => 'rest-v1', p_base_path => '/rest-v1/', p_items_per_page => 0, p_status => 'PUBLISHED', p_comments => NULL); ORDS_ADMIN.DEFINE_TEMPLATE( p_schema => 'TESTUSER1', p_module_name => 'rest-v1', p_pattern => 'employees/', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); ORDS_ADMIN.DEFINE_HANDLER( p_schema => 'TESTUSER1', p_module_name => 'rest-v1', p_pattern => 'employees/', p_method => 'GET', p_source_type => 'json/collection', p_mimes_allowed => NULL, p_comments => NULL, p_source => 'select * from emp'); ORDS_ADMIN.CREATE_ROLE( p_schema => 'TESTUSER1', p_role_name=> 'oracle.dbtools.role.autorest.TESTUSER1'); ORDS_ADMIN.CREATE_ROLE( p_schema => 'TESTUSER1', p_role_name=> 'oracle.dbtools.role.autorest.any.TESTUSER1'); l_roles(1) := 'oracle.dbtools.autorest.any.schema'; l_roles(2) := 'oracle.dbtools.role.autorest.TESTUSER1'; ORDS_ADMIN.DEFINE_PRIVILEGE( p_schema => 'TESTUSER1', p_privilege_name => 'oracle.dbtools.autorest.privilege.TESTUSER1', p_roles => l_roles, p_patterns => l_patterns, p_modules => l_modules, p_label => 'TESTUSER1 metadata-catalog access', p_description => 'Provides access to the metadata catalog of the objects in the TESTUSER1 schema.', p_comments => NULL); l_roles.DELETE; l_modules.DELETE; l_patterns.DELETE; l_roles(1) := 'SODA Developer'; l_patterns(1) := '/soda/*'; ORDS_ADMIN.DEFINE_PRIVILEGE( p_schema => 'TESTUSER1', p_privilege_name => 'oracle.soda.privilege.developer', p_roles => l_roles, p_patterns => l_patterns, p_modules => l_modules, p_label => NULL, p_description => NULL, p_comments => NULL); l_roles.DELETE; l_modules.DELETE; l_patterns.DELETE; COMMIT; END; SQL>

The output can be limited to specific item types using additional Boolean parameters. In this example we limit the output to the module definition.

 select ords_export_admin.export_schema( p_schema => 'TESTUSER1', p_include_modules => true, p_include_privileges => false, p_include_roles => false, p_include_oauth => false, p_include_rest_objects => false, p_include_jwt_profiles => false, p_include_enable_schema => false, p_export_date => false, p_runnable_as_admin => true) as ords_metadata from dual; ORDS_METADATA __________________________________________________________ -- Generated by ORDS REST Data Services 25.1.1.r1411642 -- Schema: TESTUSER1 -- BEGIN ORDS_ADMIN.DEFINE_MODULE( p_schema => 'TESTUSER1', p_module_name => 'rest-v1', p_base_path => '/rest-v1/', p_items_per_page => 0, p_status => 'PUBLISHED', p_comments => NULL); ORDS_ADMIN.DEFINE_TEMPLATE( p_schema => 'TESTUSER1', p_module_name => 'rest-v1', p_pattern => 'employees/', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); ORDS_ADMIN.DEFINE_HANDLER( p_schema => 'TESTUSER1', p_module_name => 'rest-v1', p_pattern => 'employees/', p_method => 'GET', p_source_type => 'json/collection', p_mimes_allowed => NULL, p_comments => NULL, p_source => 'select * from emp'); COMMIT; END; SQL>

SQLcl : REST EXPORT

We can export ORDS metadata directly from SQLcl using the REST EXPORT command, as demonstrated here.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.