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.
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.
- Ensure we have a REST Enabled Schema
- Create New or Use Existing Module
- Define the URL Template
- Define the Handler
- 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.
- Keep the APIs with the data
- 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…
So, if I wanted to actually see some employees, let’s say those of JOB_ID ‘FI_ACCOUNT’ –
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.
No worries, we’ll use a Query Parameter on our GET request.
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.
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.
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.
