Enabling HR​ Schema for ORDS REST operations


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.

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;
BEGINORDS.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 😉

 

IT

javier mugueta View All →

Me and my circumstance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: