Advertisements

Enabling HR​ (or other) 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.

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 😉

 

IT

Advertisements

javier mugueta View All →

Me and my circumstance

1 Comment Leave a comment

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: