ORDS 3 – Enabling RESTful service for database tables

We’ll take a look at how to enable a RESTful service in ORDS 3.0 via PL/SQL, and how to retrieve some data and metadata from this service.

Assumptions:
ORDS 3.0 is installed and configured with RESTful services.
A schema named ORDSTEST with the EMP and DEPT tables is installed.
DBA privileges are needed to execute the PL/SQL.

RESTful Service

To expose tables through RESTful calls, the schema needs to be enabled and given a base URI path. In our example we will enable the ORDSTEST schema with the URI path ot.

[sourcecode language=”sql”]
BEGIN
ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
p_schema => ‘ORDSTEST’,
p_url_mapping_type => ‘BASE_PATH’,
p_url_mapping_pattern => ‘ot’,
p_auto_rest_auth => FALSE);

— 2. Enable individual tables in the schema
ORDS.ENABLE_OBJECT(p_enabled => TRUE,
p_schema => ‘ORDSTEST’,
p_object => ‘EMP’,
p_object_type => ‘TABLE’,
p_object_alias => ’emp’,
p_auto_rest_auth => FALSE);

ORDS.ENABLE_OBJECT(p_enabled => TRUE,
p_schema => ‘ORDSTEST’,
p_object => ‘DEPT’,
p_object_type => ‘TABLE’,
p_object_alias => ‘dept’,
p_auto_rest_auth => FALSE);

commit;
END;
/
[/sourcecode]

To disable a schema use:

[sourcecode language=”sql”]

— 3. Disable the schema:
BEGIN
ORDS.ENABLE_SCHEMA(
  p_enabled => FALSE,
  p_schema => ‘ORDSTEST’);

end;
/

[/sourcecode]

To re-enable the schema enable the schema and it’s objects again.

Metadata

To check whether the RESTful service works, check the metadata catalog (don’t forget the trailing forward slash):

http://vm2:8080/ords/ot/metadata-catalog/

Result:






























Follow the links to see metadata about the individual objects.

Queries

— To query table by primary key, simply add the key value to the end of the URI:
http://vm2:8080/ords/ot/objects/dept/10

— To query a table with a predicate:
http://vm2:8080/ords/ot/objects/emp/?q={“ename”:”MILLER”}
or
http://vm2:8080/ords/ot/objects/emp/?q={“deptno”:{“$lte”:20}}

Operators:
eq: equal to
lte: less than or equal to
gte: greater than or equal to
ne: not equal to

Leave a Reply

Your email address will not be published. Required fields are marked *