DBA Hub

📋Steps in this guide1/3

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.

oracle miscconfigurationintermediate
by OracleDba
14 views
1

Setup

In order to run the examples in this article we need to do the following setup. We create a new test user. We create an object in the test user. We enable ORDS for our test user and create a service that queries the test table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
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;

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;

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;
/
2

EXPORT_SCHEMA Function

The schema function returns a CLOB containing the ORDS metadata for the specified schema. By default the export includes all metadata for the schema. The output can be limited to specific item types using additional Boolean parameters. In this example we limit the output to the module definition.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
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>

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>
3

SQLcl : REST EXPORT

We can export ORDS metadata directly from SQLcl using the command, as demonstrated here. - Oracle REST Data Services (ORDS) : Use SQLcl to Display ORDS Web Service Definitions For more information see: - ORDS_EXPORT_ADMIN PL/SQL Package Reference - Oracle REST Data Services (ORDS) : Use SQLcl to Display ORDS Web Service Definitions - Oracle REST Data Services (ORDS) : All Articles Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!