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:
{
metadata: [
{
name: "dept",
links: [
{
rel: "describedby",
href: "http://vm2:8080/ords/ot/metadata-catalog/objects/dept"
},
{
rel: "canonical",
href: "http://vm2:8080/ords/ot/objects/dept/"
}
]
},
{
name: "emp",
links: [
{
rel: "describedby",
href: "http://vm2:8080/ords/ot/metadata-catalog/objects/emp"
},
{
rel: "canonical",
href: "http://vm2:8080/ords/ot/objects/emp/"
}
]
}
]
}
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