Enabling HR​ (or other) Schema for ORDS Oracle REST Data Services

Once we have configured ORDS, we must enable a schema for REST operations. Let’s do it for HR schema.

If the database doesn’t have the HR schema created, google for it or follow this doco.

Anyway, you can use the schema user you want.

And now let’s enable the schema, first, execute the following as SYS:

alter user hr grant connect through ords_public_user

And now, execute the following connected as the HR user:

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
p_schema => 'HR',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'hr',
p_auto_rest_auth => FALSE);
commit;
END;
/

And now we must enable database objects for REST operations:

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
ORDS.ENABLE_OBJECT(p_enabled => TRUE,
p_schema => 'HR',
p_object => 'EMPLOYEES',
p_object_type => 'TABLE',
p_object_alias => 'employees',
p_auto_rest_auth => false);
commit;
END;
/
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
ORDS.ENABLE_OBJECT(p_enabled => TRUE,
p_schema => 'HR',
p_object => 'DEPARTMENTS',
p_object_type => 'TABLE',
p_object_alias => 'departments',
p_auto_rest_auth => false);
commit;
END;
/
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
ORDS.ENABLE_OBJECT(p_enabled => TRUE,
p_schema => 'HR',
p_object => 'COUNTRIES',
p_object_type => 'TABLE',
p_object_alias => 'countries',
p_auto_rest_auth => false);
commit;
END;
/
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
ORDS.ENABLE_OBJECT(p_enabled => TRUE,
p_schema => 'HR',
p_object => 'JOBS',
p_object_type => 'TABLE',
p_object_alias => 'jobs',
p_auto_rest_auth => false);
commit;
END;
/
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
ORDS.ENABLE_OBJECT(p_enabled => TRUE,
p_schema => 'HR',
p_object => 'LOCATIONS',
p_object_type => 'TABLE',
p_object_alias => 'locations',
p_auto_rest_auth => false);
commit;
END;
/

Let’s try it:

curl -i -X GET http://130.61.67.88:8080/ords/hr/employees/198

{"employee_id":198,"first_name":"Donald","last_name":"OConnell","email":"DOCONNEL","phone_number":"650.507.9833","hire_date":"2007-06-21T00:00:00Z","job_id":"SH_CLERK","salary":2600,"commission_pct":null,"manager_id":124,"department_id":50,"links":[{"rel":"self","href":"http://130.61.67.88:8080/ords/hr/employees/198"},{"rel":"edit","href":"http://130.61.67.88:8080/ords/hr/employees/198"},{"rel":"describedby","href":"http://130.61.67.88:8080/ords/hr/metadata-catalog/employees/item"},{"rel":"collection","href":"http://130.61.67.88:8080/ords/hr/employees/"}]}

That’s all for the moment. Now we have the schema enabled, we can deploy ORDS as explained here, which allows working with REST operations from whatever consumer out there.

Enjoy 😉