Oracle Database 23 Feature: Returning OLD VALUE via REST

With a general availability of Oracle Database 23c for all customers just around the corner, I thought I’d spotlight a minor new feature, via a REST API.

Let’s create a dummy table.

CREATE TABLE IF NOT EXISTS HR.NEW_OLD_DEMO
(
ID NUMBER GENERATED BY DEFAULT AS IDENTITY
( START WITH 1 CACHE 20 ) ,
NAME VARCHAR2 (50) ,
SALARY NUMBER
)
LOGGING
;

ALTER TABLE HR.NEW_OLD_DEMO
ADD CONSTRAINT NEW_OLD_DEMO_PK PRIMARY KEY ( ID )
USING INDEX LOGGING ;

And put some data in it.

insert into NEW_OLD_DEMO (NAME, SALARY)
values ('Smitty', 10),
        ('Kristopher', 20 ),
        ('Larry', 30);

I’m actually taking advantage of 2 additional new features in here, the IF NOT EXISTS (blog/demo) clause and our NEW VALUE (blog/demo) constructor feature.

This multiple values thing has been tripping people up for way too long. Now it’s finally, easy.

Ok, now let’s build our REST API to fix Smitty’s salary

My template will be emps/:id and we’ll use a PUT handler to do an update on an existing record. This won’t be an UPSERT demo, just a straight up UPDATE, but that could easily be done as well, remember this is YOUR code, you can make your API do anything you want.

Disclaimer: this isn’t production ready code.
Me, please don’t copy and paste this into your APIs

If you’re looking for an idea of what a proper API for say, an INSERT, would look like, take a look at this.

Desired Behavior:

I send a PUT to emps/10, with a payload of {“salary”: 5}, and my response will be something like 200/OK with a message of {“old_salary”: 10, “new_salary”: 5}…more or less.

So I want both the former value AND the new value. We’ve always allowed you to return the NEW value of an updated or inserted row. But now in 23c we also make it just as easy to get the former, or OLD value for an updated row (blog/example).

So I’ll call the api, changing Smitty’s salary to 5, and we should see what the before and after values are in our request response.

So yes, we’ve gone from 10 to 5, and if I query the table, I can see the current value is indeed, 5.

Here’s the super-uber-basic-handler code:

BEGIN
    update NEW_OLD_DEMO set SALARY = :salary
    where id = :id
    RETURNING OLD SALARY, NEW SALARY INTO :old_sal, :new_sal;
end;

:old_sal and :new_sal are binds derived from two parameters on the HANDLER I’ve defined, I can reference them by those names because I declared them.

And the JSON response attributes come from the parameter names, so we can control what the inputs and the outputs look like.

Catch up on all of the cool 23c new features

This is by far the most significant update Oracle has done in recent memory when it comes to addressing the needs and WANTS of the developer community.

We have a new features / 23c reference page you’ll want to bookmark. It heavily features one of our AskTOM gurus, Connor, so if you don’t like to read, you can watch his videos.

Or you can watch a general overview video from our lead PM on the Database, Ms Jenny Tsai-Smith!