ORDS commands

ORDS Installation/config

${ORDS_HOME}/bin/ords --config ${ORDS_CONFIG} install

ORDS version: version from database and ords binary should match

select ords.installed_version from dual;

${ORDS_HOME}/bin/ords --version

sqldeveloper cli to show ORDS enabled schemas and modules

C:\Downloads\Oracle\sqlcl-23.1.0.089.0929\sqlcl\bin>sql jiulusun/$passwd@//MYIncorp-scan:1521/webd

SQL> rest schemas

C:\Downloads\Oracle\sqlcl-23.1.0.089.0929\sqlcl\bin>sql testuser1/testuser1@//MYIncorp-scan:1521/webd

SQL> rest  modules

To change the ords_public_user password in wallet file

${ORDS_HOME}/bin/ords config --db-pool webd secret db.password

start and stop:

more  /MYIncorpmw/scripts/stop_ords_dev.sh

#!/bin/bash

export PATH=/usr/sbin:/usr/local/bin:/usr/bin:/usr/local/sbin:$PATH

kill $(ps -ef | grep "ords_dev/ords/ords.war" | grep -v grep | awk '{print $2}')

[Wed Jun 07 10:18:49]oracle@vm-km-ords2-t.MYIncorp.local:/MYIncorpmw/scripts

/MYIncorpmw/ords_dev -bash-5.1 $

more  /MYIncorpmw/scripts/start_ords_dev.sh

#!/bin/bash

export PATH=/usr/sbin:/usr/local/bin:/usr/bin:/usr/local/sbin:$PATH

. /MYIncorpmw/scripts/set_env_dev.sh

LOGFILE=${SCRIPTS_PATH}/logs/ords-$(date +"%y%m%d").log

export _JAVA_OPTIONS="-Xms1126M -Xmx1126M"

##nohup ${ORDS_HOME}/bin/ords --config ${ORDS_CONFIG} serve --port 9400 >> ${LOGFILE} 2>&1 &

nohup ${ORDS_HOME}/bin/ords --config ${ORDS_CONFIG} serve >> ${LOGFILE} 2>&1 &

echo "View log file with : tail -f ${LOGFILE}"

verify Apex:

apex_verify.sql

Key structure of ORDS:

[Wed Jun 07 10:20:43]oracle@vm-km-ords2-t.MYIncorp.local:/MYIncorpmw/ords_dev/config/ords

/MYIncorpmw/ords_dev -bash-5.1 $

tree

├── databases

│   ├── default

│   │   ├── pool.xml

│   │   ├── pool.xml.20230531

│   │   └── wallet

│   │       ├── cwallet.sso

│   │       └── cwallet.sso.20230531

│   ├── dev3

│   │   ├── hostnames.jiulu

│   │   ├── paths.jiulu

│   │   ├── pool.xml

│   │   └── wallet

│   │       └── cwallet.sso

│   └── webd

│       ├── pool.xml

│       └── wallet

│           └── cwallet.sso

├── global

│   ├── settings.xml

│   ├── settings.xml.20230531

│   ├── settings.xml.https

│   └── standalone

│       ├── self-signed.key

│       └── self-signed.pem

└── logs

10 directories, 15 files

show all ORDS config parameters

$ ords config info

add access log config

ords --config ${ORDS_CONFIG} config set standalone.access.log ${ORDS_CONFIG}/logs

Custom Error Pages: If you are fronting ORDS with a load balancer, you may wish to use that to handle custom error messages, rather than altering the ORDS configuration

ords --config ${ORDS_CONFIG} config set error.externalPath ~/error-pages

Static Resources (Document Root)

ords --config ${ORDS_CONFIG} config set standalone.doc.root ${ORDS_CONFIG}/global/doc_root

APEX Static Images:

export APEX_IMAGES=/u01/software/apex/images

ords --config ${ORDS_CONFIG} config set standalone.static.path ${APEX_IMAGES}

download sqldeveloper command line tool

C:\Downloads\Oracle\sqlcl-23.1.0.089.0929\sqlcl\bin>sql jiulusun@//MYIncorp-scan:1521/webd

SQLcl to manage ORDS:

SQL> connect testuser1/testuser1@//MYIncorp-scan:1521/webd

Connected.

SQL> rest schemas

PARSING_SCHEMA PATTERN STATUS

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

TESTUSER1      hr      ENABLED

SQL> rest modules

NAME                                       PREFIX                         STATUS    ITEMS_PER_PAGE

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

com.oracle.dbtools.chart.TourChart         /sdw/charts/TourChart/         PUBLISHED 0

com.oracle.dbtools.dashboard.TourDashboard /sdw/dashboards/TourDashboard/ PUBLISHED 0

testmodule1                                /testmodule1/                  PUBLISHED 25

SQL> rest export testmodule1

-- Generated by SQLcl REST Data Services 23.1.0.0

-- Exported REST Definitions from ORDS Schema Version 23.1.3.r1371032

-- Schema: TESTUSER1   Date: Mon Jun 19 11:33:46 PDT 2023

--

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);

  ORDS.DEFINE_MODULE(

  p_module_name    => 'testmodule1',

  p_base_path      => '/testmodule1/',

  p_items_per_page =>  25,

  p_status         => 'PUBLISHED',

  p_comments       => NULL);

  ORDS.DEFINE_TEMPLATE(

  p_module_name    => 'testmodule1',

  p_pattern        => 'emp/',

  p_priority       => 0,

  p_etag_type      => 'HASH',

  p_etag_query     => NULL,

  p_comments       => NULL);

  ORDS.DEFINE_HANDLER(

  p_module_name    => 'testmodule1',

  p_pattern        => 'emp/',

  p_method         => 'GET',

  p_source_type    => 'json/collection',

  p_items_per_page =>  0,

  p_mimes_allowed  => '',

  p_comments       => NULL,

  p_source         =>

'select * from emp'

  );

  COMMIT;

END;

packages and code, views: https://www.youtube.com/watch?v=iT-ehrNKq_M

begin ords.enable_schema(...)

begin ords.enable_object(... p_obj=>'EMP'... p_object_alias=>'employees'...)   -- automatically generate the urls such as https://localhost:port/ords/hr/employees/7788

curl -k https://localhost:port/ords/hr/employees/7788 --- use self-signed certificate

begin ords.create_role (p_role_name=> 'emp_role'); commit; end; 

select id,name from user_ords_roles where name ='emp_role'

begin ... ords.define_privilege (p_privilege_name.., p_roles, p_patterns, p_lable,p_description...);

column name format a20

col pattern format a20

select privilege_id,name,pattern from user_ords_privilege_mappings where name like '%priv_name%'

select * from user_ords_privilege_roles where role_name='&ordsrole'

curl -ik https://localhost:port/ords/hr/employees/7788|grep 'HTTP/1/1' --- display header to show 401 error

begin oauth.create_client (p_name,..p_redirect_uri...p_privilege_names=>'emp_priv')

select * from user_ords_clients;   --- this view has the client_id and client_secret that can be passwd in curl to get a token.

select * from user_ords_client_privileges   -- display cliewnt-privilege relationship

oauth.grant_client_role (p_client_name,p_role_name);

select * from user_ords_client_roles

https://www.youtube.com/watch?v=dGsk38QwpmY

https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-authentication#enable-ords

after enabled the schema and object, this url works ok with http 200 return code

C:\Users\JIULUSUN>curl -i -k https://vm-km-ords2-t.MYIncorp.local:443/ords/webd/hr/employees/7788

HTTP/1.1 200 OK

Content-Type: application/json

X-ORDS_DEBUG: true

ETag: "UutI4vFaOb3Mq4HKcmCd1uswFJqGeYlYjRtTC9JRxPgjiqpNpskdjF3JW9ZBmrJK7/8nNlwqleOhxG6CAMJVCg=="

Transfer-Encoding: chunked

{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1987-04-19T00:00:00Z","sal":3000,"comm":null,"deptno":20,"links":[{"rel":"self","href":"https://vm-km-ords2-t.MYIncorp.local:443/ords/webd/hr/employees/7788"},{"rel":"edit","href":"https://vm-km-ords2-t.MYIncorp.local:443/ords/webd/hr/employees/7788"},{"rel":"describedby","href":"https://vm-km-ords2-t.MYIncorp.local:443/ords/webd/hr/metadata-catalog/employees/item"},{"rel":"collection","href":"https://vm-km-ords2-t.MYIncorp.local:443/ords/webd/hr/employees/"}]}

-- Display the role.

COLUMN name FORMAT A20

SELECT id, name FROM   user_ords_roles WHERE  name = 'emp_role';

-- Display the privilege information.

COLUMN name FORMAT A20

SELECT id, name FROM   user_ords_privileges WHERE  name = 'emp_priv';

COLUMN privilege_name FORMAT A20

COLUMN role_name FORMAT A20

SELECT privilege_id, privilege_name, role_id, role_name FROM   user_ords_privilege_roles WHERE  role_name = 'emp_role';

COLUMN name FORMAT A20

COLUMN pattern FORMAT A20

SELECT privilege_id, name, pattern FROM   user_ords_privilege_mappings WHERE  name = 'emp_priv';

after the above step, the 401 error

C:\Users\JIULUSUN>curl -i -k https://vm-km-ords2-t.MYIncorp.local:443/ords/webd/hr/employees/7788

HTTP/1.1 401 Unauthorized

COLUMN name FORMAT A20

SELECT id, name, client_id, client_secret FROM   user_ords_clients;

-- Display client-privilege relationship.

SELECT name, client_name FROM   user_ords_client_privileges;

C:\Users\JIULUSUN>curl -i -k --user jX5f0bYO0oKgnNPjPIH7Xw..:81IgUYKcgYidAnViSy_NVg.. --data "grant_type=client_credentials" https://vm-km-ords2-t.MYIncorp.local:443/ords/webd/hr/oauth/token

HTTP/1.1 200 OK

Content-Type: application/json

X-ORDS_DEBUG: true

X-Frame-Options: SAMEORIGIN

Transfer-Encoding: chunked

{"access_token":"oGgNjl7N1cYqZOWxARVcSQ","token_type":"bearer","expires_in":3600}

C:\Users\JIULUSUN>curl -i -k -H"Authorization: Bearer oGgNjl7N1cYqZOWxARVcSQ" https://vm-km-ords2-t.MYIncorp.local:443/ords/webd/hr/employees/7788

HTTP/1.1 200 OK

Content-Type: application/json

X-ORDS_DEBUG: true

ETag: "UutI4vFaOb3Mq4HKcmCd1uswFJqGeYlYjRtTC9JRxPgjiqpNpskdjF3JW9ZBmrJK7/8nNlwqleOhxG6CAMJVCg=="

Transfer-Encoding: chunked

{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1987-04-19T00:00:00Z","sal":3000,"comm":null,"deptno":20,"links":[{"rel":"self","href":"https://vm-km-ords2-t.MYIncorp.local:443/ords/webd/hr/employees/7788"},{"rel":"edit","href":"https://vm-km-ords2-t.MYIncorp.local:443/ords/webd/hr/employees/7788"},{"rel":"describedby","href":"https://vm-km-ords2-t.MYIncorp.local:443/ords/webd/hr/metadata-catalog/employees/item"},{"rel":"collection","href":"https://vm-km-ords2-t.MYIncorp.local:443/ords/webd/hr/employees/"}]}