Someone on StackOverflow asked how to go about implementing a PUT handler to:

  1. grab stuff off of the request body to do an UPDATE for a specific record using an ID
  2. via a PL/SQL block
  3. and return the new values as the response

So I figured this would be a good excuse to do that, and record every step in great detail here. So, lets get started!

Signing in with SQL Developer Web, proceed to the REST screen – assuming you have ORDS version 20.3 or higher, of course.

You can click on the Blue MODULES tab to create a module.

Let’s create our module. But first, what’s a module? A template? A handler?

Modules are used to organize REST Services, which are composed of templates and handlers.

So let’s build our first Module. If I’m logged in as HR (and I am), this module and it’s services will be tied to, and executed in the database AS the HR user.

The Module

Comments: be nice, document your stuff!

The module name can be ANYTHING. The ‘Base Path’ is telling ORDS what the mapping pattern is for all templates to be defined in this module, which will be ‘so’.

I’ve also left ‘Go to Module after creation’ checked, so after hitting Apply – I see:

We’re going to need a template!

The Template(s)

It’s a brand new module, so nothing has been created for it yet, hence the ‘No results found…’ message at the bottom.

But I can create a new one, using that button with the Blue text:

The person on SO was pretty vague, so we’re going to stay vague and say ‘something’

We know what we’re only updating a single ‘something’ and that we’re referring to it by an ID, so we’ll use /:id in the URI Template. ORDS will let us reference the value from the PUT Request URI by simply referring to it as ‘:id’ in our PUT body handler, which we’re going to build next.

The Handler(s)

I’m not ready to write any real code at this point, so just a stub will do.

When we hit ‘Create’ – we’ll be taken to a full screen editor for the PUT handler.

See the green marked bits? Those are breadcrumbs – we can jump back to any point we wish.

Yes, we’re going to need some real code.

So, to get started I’ll add some comments to my PL/SQL block, let’s ‘doc’ this with the actual question from SO. And, then, let’s open another tab to the SQL worksheet, so we can create ‘something’ to UPDATE rows in, otherwise known as a TABLE.

Yes, I can have a SQL worksheet going at the same time as I’m building my Web Service.

BONUS – I can keep my worksheet code – click the ‘Save’ button in the Worksheet toolbar. This script will now be available whenever I come back to this database using this same browser on my machine.

Someday you’ll also be able to save your History/Work into your database.

Here’s that DDL and SQL, in case you’re wanting to literally play along.

CREATE TABLE something (
     id INTEGER,
  email varchar2(100),
    pwd varchar2(32),
   u_dt DATE DEFAULT sysdate);
 
ALTER TABLE SOMETHING ADD CONSTRAINT SOMETHING_XPK PRIMARY KEY ( ID );
 
INSERT INTO something (id) VALUES (1);
INSERT INTO something (id) VALUES (2);
INSERT INTO something (id) VALUES (3);
INSERT INTO something (id) VALUES (4);
INSERT INTO something (id) VALUES (5);
 
SELECT * FROM something;

Ok, so if you run that, you should have a table. And NOW we can start working on our PUT handler implementation code.

Let’s look at that code now –

BEGIN
   -- something to do an insert
   -- return what's been updated
   /* exact quote from SO
   for your keen interest to help me. here is what I understand that I had a PUT method which contains
   4 columns to update using an :id column, user can update password and/or email and the date to update
   is default for SYSDATE, now user should send all 3 columns to the PUT method that should update using
   a PL/SQL function which will return id, email, password and update date. id is to use in where clause
   to updae specific row.
   */
   UPDATE something SET email = :email,
                          pwd = :pwd,
                         u_dt = SYSDATE
                  WHERE id = :id
              returning email, pwd, u_dt INTO :out_email, :out_pwd, :out_udt;
 
    :something_id := :id;
END;

A few notes:

  • :id we get for free, based off of the template pattern, so if we PUT to something/5, when I reference :id, the code will see ‘5’
  • :email & :pwd are going to come from the PUT request body, in the {json} document
  • we’re using the RETURNING clause of our update to get the new values of the updated record
  • about those returning variables, :out_email, :out_pwd, … more on that in a second
  • the ID isn’t being updated, so there’s nothing to RETURN into it, so I’m going to use another variable to store it

We need to declare some parameters

Whiles there’s no work required to access the PUT Request Body OR the ID value from the Request URI, remember ORDS gives us that for free, I am going to need some parameters to do other stuff.

I need to construct a RESPONSE for our PUT request. I’m going to accomplish this with a parameter for each attribute in the JSON response body. Note, I could do this with HTTP Response Headers, but I don’t think that’s appropriate here…unless I wanted to use a LOCATION header, that would take us to the something that was updated, vs just telling us what it’s ID was. But, I’m not going to do that.

The ‘parameter name’ will be used to name the JSON Attribute in the response body.

Now I need to create a few more, use your imagination…but you can see them all here below.

I want to return the ID from the template, so I created an additional Parameter for it.

Making the Request

Let’s change the email and password for record number 1. So we’re going to do a PUT to ords/hr/so/something/1 with those values in a JSON document in the response body.

I use Insomina for a REST Client. But I do NOT use that email OR password.

Since I named my OUT RESPONSE parameter ‘something_id’, that’s how it’s returned in the response JSON document.

What happened in the database? We’ll, let’s go query something where ID = 1.

Our data made it to the table!

The Module Code

Back on the module page, I can export my definition to a script.

Export, edit, delete, unpublish, or navigate to the templates – click on the ellipsis looking button.

Here’s the code for our module, template, and handler –

 
-- Generated by ORDS REST Data Services 20.3.0.r3011819
-- Schema: HR  Date: Mon Nov 16 03:16:48 2020 
--
 
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);
 
  ORDS.DEFINE_MODULE(
      p_module_name    => 'stackoverflow.answers',
      p_base_path      => '/so/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => 'things i code to help answer questions on StackOverflow');
 
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'stackoverflow.answers',
      p_pattern        => 'something/:id',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => 'I don''t know what we''re modeling, only that it can be identified by an ID');
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'stackoverflow.answers',
      p_pattern        => 'something/:id',
      p_method         => 'PUT',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'begin
   -- something to do an insert
   -- return what''s been updated
   /* exact quote from SO
   for your keen interest to help me. here is what I understand that I had a PUT method which contains
   4 columns to update using an :id column, user can update password and/or email and the date to update
   is default for SYSDATE, now user should send all 3 columns to the PUT method that should update using
   a PL/SQL function which will return id, email, password and update date. id is to use in where clause
   to updae specific row.
   */
 
 
 
 
   update something set email = :email,
                          pwd = :pwd,
                         u_dt = sysdate
                  where id = :id
              returning email, pwd, u_dt into :out_email, :out_pwd, :out_udt;
 
    :something_id := :id;
 
end;');
 
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'stackoverflow.answers',
      p_pattern            => 'something/:id',
      p_method             => 'PUT',
      p_name               => 'new_email',
      p_bind_variable_name => 'out_email',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'STRING',
      p_access_method      => 'OUT',
      p_comments           => 'something.email after the update has ran');
 
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'stackoverflow.answers',
      p_pattern            => 'something/:id',
      p_method             => 'PUT',
      p_name               => 'new_password',
      p_bind_variable_name => 'out_pwd',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'STRING',
      p_access_method      => 'OUT',
      p_comments           => 'something.pwd after the update has ran');
 
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'stackoverflow.answers',
      p_pattern            => 'something/:id',
      p_method             => 'PUT',
      p_name               => 'time_of_update',
      p_bind_variable_name => 'out_udt',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'TIMESTAMP',
      p_access_method      => 'OUT',
      p_comments           => 'DATE TIME for when something with PK (id) was updated');
 
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'stackoverflow.answers',
      p_pattern            => 'something/:id',
      p_method             => 'PUT',
      p_name               => 'something_id',
      p_bind_variable_name => 'something_id',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'INT',
      p_access_method      => 'OUT',
      p_comments           => 'the record being updated');
 
 
 
COMMIT;
 
END;

What else is left to do?

I need to build exception handling into my code. What happens if someone tries to update something that doesn’t exist? Or tries to put in a 150 character long email address? Or or or…? I didn’t do any of that kind of checking. But I’ve shown HOW you can do that here.

This is much better, or less bad than a HTTP 500 response.

And, I’ve made it so we can UPDATE something…but what if someone wants to INSERT something, or get a LIST of all the somethings, or just the one something? We’ll need a new template (something/) and GET and POST handlers. Or maybe even a DELETE if we want to get ride of something.

And of course….SECURITY!

This is super easy now in SQL Developer Web!

I’m going to talk about how to secure our modules and RESTful Web Services with ORDS’ built-in OAuth2 mechanism and clients in our next post. Stay tuned!

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