This post is a bit longer than ‘it needs to be’ – someone brand new to ORDS asked me for advice on how ‘best’ to build a RESTful Web Service for Oracle Database using ORDS, but when there was ‘more than one table’ and at least one parameter.

So, I’m going to go into great detail on exactly how to do this, and will include all the code for defining the RESTful Service.

Now, when someone asks me for ‘best’ – I get antsy. Your requirements and budget together will often dictate what’s best – and the more we know about your exact requirements and use cases, the better we can give guidance and insight.

K.I.S.S – Keep It SQL, Seriously

This isn’t my idea, but I have surely latched onto it. I believe it was Tom Kyte that once said, if you can do it in SQL, use SQL.

SQL can CLEARLY handle more than one table and parameters, so on the surface, SQL should suffice for the ‘brains’ of this RESTful Web Service.

Many means more than two generally, and maybe our user has 25 tables. But it doesn’t really matter, so much as here’s how to use SQL and ORDS. My example has 4 tables.

My advice for anyone is – don’t JOIN a table if you don’t need to. Check your SELECTs to make sure you’re actually NEEDING those columns and tables. The less work, the better.

The Many Tables

Human Resources (HR), anyone? Click here to download the HR scripts.

Oh employees, my employees.

And yes, I used Data Modeler to build this relational diagram.

The SQL

SELECT
    employees.first_name,
    employees.last_name,
    employees.salary,
    to_char(employees.hire_date, 'DD-Month-YYYY') START_DATE,
    departments.department_name,
    jobs.job_title,
    locations.city
    || ', ' 
    || locations.state_province CITY_STATE
  FROM
     employees
     INNER JOIN departments ON employees.department_id = departments.department_id
     INNER JOIN locations ON departments.location_id = locations.location_id
     INNER JOIN jobs ON employees.job_id = jobs.job_id
 WHERE
    employees.job_id = :job -- this is IMPORTANT
 ORDER BY
    employees.last_name,
    employees.first_name

And yes, I used ANSI SQL for those JOINs. And YES, I used SQL Developer to do that for me.

The RESTful Web Service, in ORDS

To deploy a new service, we’re going to take the following steps.

  1. Ensure we have a REST Enabled Schema
  2. Create New or Use Existing Module
  3. Define the URL Template
  4. Define the Handler
  5. Secure it for production

REST Enabled Schema

All ORDS RESTful Web Services are defined as belonging to a SCHEMA. When they are called, the SQL or PL/SQL of those APIs are executed as that USER.

Before defining a RESTful Web Service, we need a schema.

You have a couple of options.

  1. Keep the APIs with the data
  2. Define the APIs in a schema with the proper PRIVs to the data/stored procedures

Option 1 would be, we have a query that runs against HR, we defined the RESTful Web Service in the HR schema. And then these APIs could do anything they want to this data.

Option 2 would be, create a new schema to store the APIs in. For that USER, only grant SELECT or READ privs on the tables involved. That way, your APIs couldn’t ‘accidentally’ be used to delete or change the data.

Option 1 would look like this –

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

Things of note here – this will allow HR to have REST Services defined. When accessed, the base URI will always start with ‘hr’, and if someone wants an inventory of all the services defined in HR, they won’t need to provide any credentials.

The Module

We created modules to allow developer to group services. These services would share a common set of properties, and would all share the same URI base path.

I want to create a Module called ‘DEMO’ and I want it’s base path to be ‘demo’

BEGIN
  ORDS.DEFINE_MODULE(
      p_module_name    => 'DEMO',
      p_base_path      => 'demo',
      p_items_per_page =>  25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);
END;
/

The URL Template

I want my Web Service to be ‘RESTful’ – that is, I want it to follow the REST design paradigm. I’m going to have a resource (Noun), that I’m going to perform actions (Verb) against.

The URL Template, or Uniform Resource Identifier (URI), tells ORDS how to identify the resource.

BEGIN
ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'demo', -- that module we just created!
      p_pattern        => 'employees/:job',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
END;
/

Our resource, or thing, or NOUN, is a collection of employees. I’m further describing this collection via a parameter, called ‘job’.

The Handler

Now we get to define the action (verb) that we’re going to implement for this resource. In ORDS, we have the option of using GET, PUT, POST, and DELETE.

We’re going to assume our user wanted to retrieve records from multiple tables – hence we went with that SQL statement above, the SELECT.

The easiest way to implement a SELECT via a HANDLER in ORDS, is with a GET, and a source type of ‘json/collection’. That tells ORDS there’s likely to be more than one item come back, and to implement the GET with a SQL statement.

I talk about all the different SOURCE TYPEs here.

BEGIN
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'demo',
      p_pattern        => 'tables/:param',
      p_method         => 'GET',
      p_source_type    => 'json/collection',
      p_items_per_page =>  25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'SELECT
    employees.first_name,
    employees.last_name,
    employees.salary,
    to_char(employees.hire_date, ''DD-Month-YYYY'') START_DATE,
    departments.department_name,
    jobs.job_title,
    locations.city
    || '', '' 
    || locations.state_province CITY_STATE
  FROM
     employees
     INNER JOIN departments ON employees.department_id = departments.department_id
     INNER JOIN locations ON departments.location_id = locations.location_id
     INNER JOIN jobs ON employees.job_id = jobs.job_id
 WHERE
    employees.job_id = :job
 ORDER BY
    employees.last_name,
    employees.first_name'
      );
 
  COMMIT; 
END;

Using the API

There are many ways to test, or exercise an API, especially if you’re just doing GETs. GETs can be called in any web browser.

I’m running ORDS on my local machine as a java program, and I’ve published my RESTful Web Service in the HR schema, aliased as ‘hr’ – so all of my requests will start with

http://localhost:8080/ords/hr/

HTTP – not good for production, OK for dev. It’s not encrypted, so folks on our network can see what we’re passing back and forth to and from ORDS. Get a certificate, make sure your webserver is running in HTTPS.

localhost:8080 – ORDS has a Jetty webserver listening on port 8080, and I’m using my browser on the same machine ORDS is running, so 127.0.0.1 or ‘localhost’ will work.

ords – tells the webserver it needs to call the ORDS java servlet code.

hr – tells ORDS where to find the RESTful Web Service AND which Oracle Database user to use to execute said service.

Now, our template was employees/:job, so…

ORDS is passing ‘:job’ literally back to the :job bind variable in our SQL based handler code.

So, if I wanted to actually see some employees, let’s say those of JOB_ID ‘FI_ACCOUNT’ –

Magic! ORDS knows to take the :job part or the URI and tie it to a :job bind variable in its Handler.

But, This Should be Easier!

Well, we have AUTO REST. You give us a database object, and ORDS figures out how to publish RESTful Web Services for it. The Database Object I’m thinking about here is a VIEW.

So let’s create a VIEW

CREATE VIEW EMPS_VIEW
AS SELECT
    employees.first_name,
    employees.last_name,
    employees.salary,
    to_char(employees.hire_date, 'DD-Month-YYYY') START_DATE,
    departments.department_name,
    jobs.job_title,
    locations.city
    || ', ' 
    || locations.state_province CITY_STATE
  FROM
     employees
     INNER JOIN departments ON employees.department_id = departments.department_id
     INNER JOIN locations ON departments.location_id = locations.location_id
     INNER JOIN jobs ON employees.job_id = jobs.job_id
 ORDER BY
    employees.last_name,
    employees.first_name;
--our WHERE CLAUSE WENT AWAY!
COMMENT ON TABLE EMPS_VIEW IS 'Our EMPS ORDS AUTOREST VIEW demo';

There’s no more WHERE clause, no more :JOB bind…, but bear with me.

Now, REST Enable the VIEW.

BEGIN
    ORDS.ENABLE_OBJECT(p_enabled        => TRUE,
                       p_schema         => 'HR',
                       p_object         => 'EMPS_VIEW',
                       p_object_type    => 'VIEW',
                       p_object_alias   => 'emps_view',
                       p_auto_rest_auth => FALSE);
 
    COMMIT;
END;

Now, let’s hit the VIEW endpoint.

But we have a parameter, we want to filter on JOB_ID!

No worries, we’ll use a Query Parameter on our GET request.

ORDS lets me add parameters on my GET requests – and use those to add WHERE clauses for the underlying SQL!

I have many examples on filtering for ORDS RESTful Web Services here.

You’ll notice with the REST Enabled VIEW, there’s no more module or URL Template, it’s just schema, and then the object name. Now both the schema AND the object names can be aliased in the URIs, and we do recommmend you do that for security purposes.

Let’s do a quick review of how an ORDS RESTful Web Service is constructed, from the HTTP VERB all the way to the Template URI.

Image Credit: Miguel Sanchez, our lead Dev Manager for SQL Developer Web.

But Wait, Couldn’t we Use PL/SQL?

Yes, of course we could have. We could have a function that returns a REF_CURSOR and call that in a SQL Select. Or we could build a Procedure that does the same and use the ORDS AUTO feature to execute that, or….

But, I wanted to talk about SQL today. And SQL was up to the task.

For ORDS and PL/SQL – see this.

But Wait, do I really need to code all of these PL/SQL API calls to define the Service?

No, we have REST Development Interfaces in SQL Developer (16 minutes Video demo).

Point, click, type the SQL. Click ‘Ok’ – you’re good to go. We’ll even show you the PL/SQL behind the APIs, and we’ll let you export the services to a file, so you can source control those.

SQL Developer Web REST development feature is coming SOON.

Summing things up

If you know SQL (or PL/SQL) – you can build a REST Service for Oracle Database with ORDS. You need to think about what your service is going to do, how you’re going to model your resources, and represent them with URIs (url templates). Then you need to take the Verbs (GET, PUT, POST, DELETE), and attach your SQL and PL/SQL.

I’ve covered everything here but Security and Documentation. We’ll cover those in a follow-up post in more detail.

For security, if you don’t have your own webserver and mid-tier authentication system to tie into, then I recommend you look into ORDS’s OAUTH2 capabilities.

thatjeffsmith
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Write A Comment

RSS
Follow by Email
LinkedIn
Share