Anw: Minor update

2025-10-29T10:50:34Z

Minor update

← Older revision		Revision as of 12:50, 29 October 2025
Line 1:		Line 1:
	This is a technical guide for the setup of Oracle ORDS, used for OAUTH2 webservices through Oracle Fusion Middleware		This is a technical guide for the setup of Oracle ORDS, used for OAUTH2 webservices through Oracle Fusion Middleware.

			{{Note}}The majority of the ORDS schema creation is through a WAR file that is installed when the process is initialized.

			The initial build consists of a java unzip, and then using the WAR file to create schemas / deploy objects to the database (logged in as sys) – this WAR file is also the “ORDS java” side and a minor amount of config.

			This guide is intended to be a small introduction to the technical tables and requirements, for support purposes.

	== Pre-requirements ==		== Pre-requirements ==
Line 7:		Line 13:

	==== Users ====		==== Users ====
	* ~~ORDSIMPORT~~		* ords
	* ~~MTS_OWNER~~		* ordsimport
			* mts_owner

	== Overview of Affected Tables ==		== Overview of Affected Tables ==
Line 35:		Line 42:

	== Setting Up ==		== Setting Up ==
			As mts_owner:
	~~---------------------------------------------------------~~
	as mts_owner :
	~~---------------------------------------------------------~~

	create user ords identified by {password} default tablespace data temporary tablespace temp profile default;		create user ords identified by {password} default tablespace data temporary tablespace temp profile default;
	grant ords_administrator_role to ords;		grant ords_administrator_role to ords;
Line 58:		Line 61:
	{{Note}}The grant of execute on the package must be the package that the ORDS handler is going to execute within the MTS_OWNER space. If this is different (or there are several), then this must be changed and added to here.		{{Note}}The grant of execute on the package must be the package that the ORDS handler is going to execute within the MTS_OWNER space. If this is different (or there are several), then this must be changed and added to here.

	~~---------------------------------------------------------~~		as ords:
	as ords :
	~~---------------------------------------------------------~~

	begin		begin
	ords_metadata.ords.enable_schema(		ords_metadata.ords.enable_schema(
Line 73:		Line 73:
	/		/

	~~---------------------------------------------------------~~
	declare		declare
	l_roles_arr owa.vc_arr;		l_roles_arr owa.vc_arr;

Anw: Minor correction

2025-10-29T08:49:25Z

Minor correction

New page

This is a technical guide for the setup of Oracle ORDS, used for OAUTH2 webservices through Oracle Fusion Middleware

== Pre-requirements ==

* PL/SQL Developer.
* TNS Name for the Oracle database.

==== Users ====
* ORDSIMPORT
* MTS_OWNER

== Overview of Affected Tables ==
===Settings for users when editing ORDS===
select * from user_ords_roles;
select * from user_ords_privileges;
select * from user_ords_privilege_roles;
select * from user_ords_privilege_mappings;

=== Creation===
The actual web services

select * from user_ords_modules;
select * from user_ords_services;
select * from user_ords_handlers;
select * from user_ords_templates;
select * from user_ords_schemas;

select * from user_ords_parameters;

===Execution ===
select * from v$session where type = 'USER'
select * from user_ords_clients;
select * from user_ords_client_privileges;
select * from user_ords_client_roles;

== Setting Up ==

---------------------------------------------------------
as mts_owner :
---------------------------------------------------------

create user ords identified by {password} default tablespace data temporary tablespace temp profile default;
grant ords_administrator_role to ords;
grant ords_runtime_role to ords;
grant create session to ords;
grant select any table to ords;

create user ordsimport identified by {password} default tablespace data temporary tablespace temp;

grant create session to ordsimport;
grant ords_runtime_role to ordsimport;
grant select any table to ordsimport;

grant execute on mts_owner.dp_ctms_import to ordsimport;

---------------------------------------------------------

{{Note}}The grant of execute on the package must be the package that the ORDS handler is going to execute within the MTS_OWNER space. If this is different (or there are several), then this must be changed and added to here.

---------------------------------------------------------
as ords :
---------------------------------------------------------

begin
ords_metadata.ords.enable_schema(
p_schema => 'ordsimport',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'import'
);

commit;
end;
/

---------------------------------------------------------
declare
l_roles_arr owa.vc_arr;
l_patterns_arr owa.vc_arr;
begin
l_roles_arr(1) := 'ordsimport_role';
l_patterns_arr(1) := 'ordsimport_pattern/';

ords_metadata.ords.define_privilege (
p_privilege_name => 'ordsimport_priv',
p_roles => l_roles_arr,
p_patterns => l_patterns_arr,
p_label => 'test priviledge',
p_description => null
);

commit;
end;
/

select * from user_ords_privileges;
select * from user_ords_privilege_roles;
select * from user_ords_privilege_mappings

begin
ords_metadata.oauth.create_client(
p_name => 'ordsimport_client',
p_grant_type => 'client_credentials',
p_support_email => 'noreply@email.com',
p_privilege_names => 'ordsimport_priv'
);

commit;
end;
/

select * from user_ords_clients;
select * from user_ords_client_privileges;

begin
ords_metadata.oauth.grant_client_role(
p_client_name => 'ordsimport_client',
p_role_name => 'ordsimport_role'
);
commit;
end;
/

select * from user_ords_client_roles;

== Examples ==
Example Order Create - example uses XML - REST, XML-based order create method/service.

BEGIN
ORDS.define_module(
p_module_name => 'rest-xml-import-order-create',
p_base_path => 'order',
p_items_per_page => 0);

ORDS.define_template(
p_module_name => 'rest-xml-import-order-create',
p_pattern => 'create/');

ORDS.define_handler(
p_module_name => 'rest-xml-import-order-create',
p_pattern => 'create/',
p_method => 'POST',
p_source_type => ORDS.source_type_plsql,
p_source =>
q'[
declare
l_response xmltype;

CHUNK_SIZE constant pls_integer := 8192;
vOffset pls_integer := 1;
DOC1 clob;
vChunk varchar2(CHUNK_SIZE CHAR);

begin

l_response := mts_owner.dp_ctms_import.import_order(ctms_ord => XMLTYPE.createXML(:body_text));
owa_util.mime_header ('application/xml', true);

DOC1 := XMLTYPE.getClobVal(l_response);
loop
vChunk := substr (DOC1, vOffset, CHUNK_SIZE);
exit when vChunk is null;
htp.prn(vChunk);
vOffset := vOffset + length(vChunk);
end loop;

end;
]',
p_items_per_page => 0);

COMMIT;
END;
/

Example handler for LogiNext interface - REST, JSON-based order update method/service.

BEGIN
ORDS.define_module(
p_module_name => 'rest-json-import-order-update',
p_base_path => 'order',
p_items_per_page => 0);

ORDS.define_template(
p_module_name => 'rest-json-import-order-update',
p_pattern => 'update/');

ORDS.define_handler(
p_module_name => 'rest-json-import-order-update',
p_pattern => 'update/',
p_method => 'POST',
p_source_type => ORDS.source_type_plsql,
p_source =>
q'[
declare
l_response json_object_t := json_object_t();

CHUNK_SIZE constant pls_integer := 8192;
vOffset pls_integer := 1;
vData clob := EMPTY_CLOB();
vChunk varchar2(CHUNK_SIZE CHAR);

begin

l_response := mts_owner.dp_loginext.process_inbound(i_message => json_object_t.parse(:body));

owa_util.mime_header ('application/json', true);

vData := l_response.to_clob;
loop
vChunk := substr (vData, vOffset, CHUNK_SIZE);
exit when vChunk is null;
htp.prn(vChunk);
vOffset := vOffset + length(vChunk);
end loop;

exception when others then

l_response := JSON_OBJECT_T.parse('{"status":"exception","sqlcode":"'||sqlcode||'","sqlerrm":"'||sqlerrm||'"}');
htp.p(l_response.stringify);

end;
]',
p_items_per_page => 0);

COMMIT;
END;
/

== Basic Queries ==

select * from user_ords_handlers
where UPPER(source) like '%DP_LOGINEXT%'
[[Category:Technical Guides]]
[[Category:CTMS]]
[[Category:WMS]]
[[Category:Oracle]]

Oracle ORDS Technical Guide - Revision history

Anw: Minor update

Anw: Minor correction