Someone on StackOverflow asked how to go about implementing a PUT handler to:
- grab stuff off of the request body to do an UPDATE for a specific record using an ID
- via a PL/SQL block
- 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.
Let’s create our module. But first, what’s a module? A template? A handler?
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
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:
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:
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)
When we hit ‘Create’ – we’ll be taken to a full screen editor for the PUT handler.
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.
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.
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.
Now I need to create a few more, use your imagination…but you can see them all here below.
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.
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.
The Module Code
Back on the module page, I can export my definition to a script.
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.
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!
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!
