Match your METHOD with your SOURCE

Oracle REST Data Service makes it easy to provide a RESTful service interface to tables, views and procedures in your database. When two paradigms meet there’s rarely an exact one to one match between the concepts. It’s the same with Relational Database concepts and RESTful service concepts. These considerations must be kept in mind when when moving from one paradigm to another.

One thing to consider is the rule that most methods in a RESTful API must be idempotent. In the context of ORDS handlers this applies to GET, DELETE and PUT. However, ORDS does not enforce the rule because it can not be easily determined from a PL/SQL block if it is idempotent or not. To do so requires an understanding of not just the PL/SQL block itself, but also any functions or procedures it calls.

Idempotent considerations applies to the resource the endpoint represents. Therefore, if an audit entry must be written when a customer record is accessed, the PL/SQL that is defined in a GET handler can insert that audit record and still be considered idempotent because it is not modifying the customer record.

The ORDS handlers, particularly PL/SQL ones, that we have created should be reviewed from time to see if their declared method is correct. To get a list of your PL/SQL handlers that are not already defined with a POST method, run this query

SELECT
m.name,
m.uri_prefix,
t.uri_template,
h.source
FROM
user_ords_handlers h,
user_ords_templates t,
user_ords_modules m
WHERE
h.source_type = 'plsql/block'
AND h.method != 'POST'
AND h.template_id = t.id
AND t.module_id = m.id;

Review the results and ask does the source match the method?

Although there are more HTTP method types, these are the most common, and supported by Oracle REST Data Services.