ORDS.sql

I use this to display all ORDS settings that I need to know:

-- Run this part as dba user, until you see another comment about running as ORDS schema user

set echo on

set linesize 240

col object_type format a20

col object_name format a40

col proxy,client format a20

col parsing_schema format a20

col status format a10

col pre_hook format a10

col updated_by format a15

col created_by format a15

col auto_rest_auth format a10

COLUMN name FORMAT A60

COLUMN privilege_name FORMAT A60

COLUMN role_name FORMAT A50

COLUMN name FORMAT A40

COLUMN pattern FORMAT A40

COLUMN name FORMAT A20

col client_name format a20

COLUMN client_name FORMAT A30

COLUMN role_name FORMAT A20

set long 5000

column PARSING_SCHEMA format a20

col name format a60

COLUMN name FORMAT A40

COLUMN uri_prefix FORMAT A80

COLUMN uri_template FORMAT A20

COLUMN source_type FORMAT A30

COLUMN source FORMAT A60

col base_path format a30

col name format a40

col pattern format a20

col source format a20

col source_type format a40

col client_schema format a20

col role_schema  format a20

col schema format a20

-- display ORDS version

select * from ords_metadata.ords_version;

--list proxy users

col proxy format a40

col client format a40

select * from proxy_users where proxy='ORDS_PUBLIC_USER';

--display ORDS enabled schemas

--select * from DBA_ORDS_SCHEMAS;

--display url mapping pattern

--select * from DBA_ORDS_URL_MAPPINGS;

--display enabled schemas and their url mapping patterns

select s.*,m.pattern,type from DBA_ORDS_SCHEMAS s, DBA_ORDS_URL_MAPPINGS m where s.url_mapping_id=m.id;

COLUMN name FORMAT A60

--select distinct object_type,object_name from dba_objects where owner='ORDS_METADATA' and object_type in ('VIEW','TABLE') order by object_type,object_name;

-- view schema,client, role

select distinct client_id,(select parsing_schema from DBA_ORDS_SCHEMAS where id= cr.CLIENT_SCHEMA_ID) client_schema,client_name, role_id,role_name,(select parsing_schema from DBA_ORDS_SCHEMAS where id= cr.ROLE_SCHEMA_ID) role_schema 

from  ords_metadata.ORDS_CLIENT_ROLES cr;

-- view privs

select distinct (select parsing_schema from DBA_ORDS_SCHEMAS where id= m.SCHEMA_ID) schema, m.* from ords_metadata.ORDS_PRIVILEGE_MAPPINGS m;

-- view client privs

COLUMN client_priv_id FORMAT 999999 HEADING "Client Privilege ID"

COLUMN client_priv_created_by FORMAT A20 HEADING "Created By"

COLUMN client_priv_updated_by FORMAT A20 HEADING "Updated By"

COLUMN client_priv_client_id FORMAT 999999 HEADING "Client ID"

COLUMN priv_id FORMAT 999999 HEADING "Privilege ID"

COLUMN name FORMAT A20 HEADING "Name"

COLUMN label FORMAT A20 HEADING "Label"

COLUMN description FORMAT A30 HEADING "Description"

COLUMN parsing_schema FORMAT A15 HEADING "Parsing Schema"

COLUMN client_name FORMAT A15 HEADING "Client Name"

COLUMN client_created_by FORMAT A20 HEADING "Client Created By"

SELECT p.id client_priv_id,

  p.created_by client_priv_created_by,

  p.updated_by client_priv_updated_by,

  p.client_id client_priv_client_id,

  sec.id priv_id,

  sec.name,

  sec.label,

  sec.description,

  sch.parsing_schema,

  c.name client_name,

  c.created_by client_created_by

FROM ords_metadata.ords_schemas sch,

  ords_metadata.sec_privileges sec,

  ords_metadata.oauth_client_privileges p,

  ords_metadata.oauth_clients c

WHERE sch.id           = p.schema_id

AND sch.id             = c.schema_id

AND sec.id             = p.privilege_id

AND p.client_id        = c.id

;

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

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

--part 1 : connect as ORDS user schema, and the following queries will display all ORDS settings of this schema, modules/templates/handlers etc

connect &ordsuser/&password@&connstring

set echo on

set linesize 240

--for ORDS settings

set echo on

set linesize 240

--view modules/template/handlers

COLUMN name FORMAT A50

COLUMN uri_prefix FORMAT A80

SELECT id, name, uri_prefix FROM   user_ords_modules ORDER BY name;

COLUMN uri_template FORMAT A20

col source format a60

SELECT id, module_id, uri_template FROM   user_ords_templates ORDER BY module_id;

SELECT id, template_id, source_type, method, source FROM   user_ords_handlers ORDER BY id;

-- display schema,modules||templates||handlers

COLUMN source_type FORMAT A30

COLUMN source FORMAT A60

COLUMN module_desc FORMAT A36 HEADING "Module Description"

COLUMN template_desc FORMAT A30 HEADING "Template Description"

COLUMN handler_desc FORMAT A30 HEADING "Handler Description"

COLUMN method FORMAT A10 HEADING "Method"

COLUMN source_type FORMAT A15 HEADING "Source Type"

COLUMN url FORMAT A50 HEADING "URL"

COLUMN parameter_count FORMAT 9999 HEADING "Parameter|Count"

COLUMN source FORMAT A50 HEADING "Source"

SELECT uoh.method,        uoh.source_type,        '<host_ref>' || uos.pattern || uom.uri_prefix || uot.uri_template url,        

(SELECT COUNT(id) FROM user_ords_parameters WHERE handler_id = uoh.id) parameter_count ,

uoh.source, uom.comments module_desc,        uot.comments template_desc,        uoh.comments handler_desc        

FROM user_ords_schemas   uos,      user_ords_modules   uom,      user_ords_templates uot,      user_ords_handlers  uoh

WHERE uot.module_id = uom.id AND uom.schema_id = uos.id AND uoh.template_id = uot.id 

--AND uos.parsing_schema = 'ORDSDEMO' 

ORDER BY uom.comments, uot.uri_template;

--display privs

col name format a40

SELECT id, name FROM   user_ords_privileges

--WHERE  name = 'emp_priv'

order by id;

--display privs and assigned roles

COLUMN privilege_name FORMAT A60

COLUMN role_name FORMAT A50

SELECT privilege_id, privilege_name, role_id, role_name FROM   user_ords_privilege_roles

--WHERE  role_name = 'emp_role'

;

--display privs and their mapping patterns

COLUMN priv_name FORMAT A40

COLUMN pattern FORMAT A40

SELECT privilege_id, name priv_name, pattern FROM   user_ords_privilege_mappings

--WHERE  name = 'emp_priv'

;

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

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

--Part 2 : display OAuth2 settings, clientid/secret/privs/roles/protected patterns

COLUMN name FORMAT A20

SELECT id, name, client_id, client_secret FROM  user_ords_clients;

--select name,CLIENT_ID,CLIENT_SECRET from ORDS_METADATA.OAUTH_CLIENTS;

-- Display client-privilege relationship.

col client_name format a20

SELECT name, client_name,CLIENT_PRIV_ID,PRIV_ID FROM   user_ords_client_privileges;

-- Display client-role relationship.

COLUMN client_name FORMAT A30

COLUMN role_name FORMAT A20

SELECT * from user_ords_client_roles;

-- view services

col base_path format a30

col name format a40

col pattern format a20

col source format a20

col source_type format a40

select method,base_path,pattern,name,module_id,template_id,handler_id,source_type,source from USER_ORDS_SERVICES;

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

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

--part 3 : export ORDS settings for this ORDS enabled schemas

-- export schema, this will export all settings in this enabled schema

select ORDS_METADATA.ords_export.export_schema from dual;

set long 5000

set pagesize 1000

column PARSING_SCHEMA format a20

col name format a60

--display modules

select schema.parsing_schema,modules.name from user_ords_schemas schema, user_ORDS_MODULES modules where modules.schema_id=schema.id;

-- export modules: this will export all medules in this schema

select ORDS_METADATA.ords_export.export_module(p_module_name=>name) from user_ORDS_MODULES;