ORDS 3 – Enabling RESTful service for database tables

June 18, 2015 at 2:26 pm | Posted in Oracle Developement | Leave a comment

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.

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;
/

To disable a schema use:


-- 3. Disable the schema:
BEGIN
 ORDS.ENABLE_SCHEMA(
  p_enabled => FALSE,
  p_schema => 'ORDSTEST');
 
end;
/

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

Advertisements

Leave a Comment »

RSS feed for comments on this post. TrackBack URI

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: