Sometimes you have work to do, and you need more than a single database session to do it. Wouldn’t it be nice if you could put your transition ‘to sleep,’ and then pick it up and finish it off, later?
Well now in version 23.6 of the database, you can.
A Quick Demo, just in VS Code and SQLcl
I’m going to create a connection (which has a session in the database), insert a row in a table. And then I’m going to use a different connection, with it’s own associated database session, ‘pick up’ that previous transaction, and COMMIT the row.
My table –
create table if not exists sessionless (
id integer,
message varchar2(25)
);Inserting the row, suspending the transaction
Now that we have a table to work with, let’s start a transaction, insert a ROW, and then SUSPEND the transaction.
My code –
DECLARE
gtrid VARCHAR2(128);
BEGIN
gtrid := DBMS_TRANSACTION.START_TRANSACTION
-- my transaction label, i can also let the system pick one for me
( UTL_RAW.CAST_TO_RAW('tjs-23.6')
, DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS
-- timeout period, finish the task, or you get rolled back!
, 2000
, DBMS_TRANSACTION.TRANSACTION_NEW
);
INSERT INTO SESSIONLESS values (1, 'Hello');
DBMS_TRANSACTION.SUSPEND_TRANSACTION;
END;
/I added some comments, but things to note, I’m supplying my own ‘label’ for the transaction, but the PL/SQL allows you to use a system generated one.
And I’m setting a fairly large timeout, 2000 (seconds). If i take longer than that to pick the session back up, then it will get rolled back.
So I’m going to run that in VS Code, and then in a different session in SQLcl, I’m going to query my table.
Picking back up the transaction, COMMIT the work
Now in the same SQLcl session, let’s RESUME the transaction, COMMIT, and then query the table again.
Now let’s talk about REST APIs
I’ve been telling people learning how to work with REST APIs , at least when it comes to your Oracle Database, that the nature of REST is that there is no STATE. Every request is complete in and unto itself.
What that has meant for doing database work, is that on every request, you have a complete TRANSACTION/CONNECTION and you are in AUTOCOMMIT territory.
With this new database feature, we now have the ability to have transactions LIVE across multiple HTTPs calls!
Perhaps your application needs to do some further processing, you’re not just inserting ROWs into tables, but maybe you want to reserve a concert ticket for an event, and then later handle the credit card transaction. You could do that now over 2 REST API calls, using ORDS and Database 23ai.
A Quick Demo
I’m going to have a single module and template, with a GET and POST handler.
My POST handler is going to use 3 parameters, so I can pass a message to be inserted to my table, the ID for the transaction, and an action.
Let’s look at my POST handler code –
What’s this code doing? I have my 3 parameters off of the URI defined, so I can reference them via :binds in the PL/SQL block.
Based on the action included on the request, it’ll either START .. INSERT .. suspend, OR it will RESUME .. COMMIT.
Note I have no room for errors here, I’ve not taken the time to build in exception handling, as I should. Nor do I redirect to the new resource, but I’ve shown these things before.
Demo
Starting the transaction –
Finishing the transaction –
And looking to see if the data is there in the database –
Parting Thoughts
This is/will be a game-changer for customers. We’ve been talking to developers for years about the ability to handle this type of workflow, and now it’s here. Remember that 23ai is available with our FREE offering, on premises with Exadata and ODA, and of course in Oracle Cloud (OCI) with all of the database cloud services.
The Code
-- Generated by ORDS REST Data Services 24.4.0.r3451601
-- Schema: HR Date: Wed Feb 12 05:47:45 2025
--
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 => 'sessionless',
p_base_path => '/sessionless/',
p_items_per_page => 25,
p_status => 'PUBLISHED',
p_comments => NULL);
ORDS.DEFINE_TEMPLATE(
p_module_name => 'sessionless',
p_pattern => 'messages/',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'sessionless',
p_pattern => 'messages/',
p_method => 'GET',
p_source_type => 'json/collection',
p_items_per_page => 25,
p_mimes_allowed => NULL,
p_comments => NULL,
p_source =>
'select *
from SESSIONLESS');
ORDS.DEFINE_HANDLER(
p_module_name => 'sessionless',
p_pattern => 'messages/',
p_method => 'POST',
p_source_type => 'plsql/block',
p_mimes_allowed => NULL,
p_comments => NULL,
p_source =>
'DECLARE
gtrid VARCHAR2(128);
BEGIN
if (lower(:action) = ''start'') then
gtrid := DBMS_TRANSACTION.START_TRANSACTION
( UTL_RAW.CAST_TO_RAW(:id)
, DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS
, 20
, DBMS_TRANSACTION.TRANSACTION_NEW
);
INSERT INTO SESSIONLESS values (1, :message);
DBMS_TRANSACTION.SUSPEND_TRANSACTION;
:status_code := 200;
:out := ''Transaction suspended, rollback/timeout of 20 seconds in play.'';
elsif (lower(:action) = ''finish'') then
gtrid := DBMS_TRANSACTION.START_TRANSACTION
(UTL_RAW.CAST_TO_RAW(:id)
, DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS
, 20
, DBMS_TRANSACTION.TRANSACTION_RESUME);
COMMIT;
:status_code := 201;
:out := ''Transaction finished, message added!'';
end if;
END;');
ORDS.DEFINE_PARAMETER(
p_module_name => 'sessionless',
p_pattern => 'messages/',
p_method => 'POST',
p_name => 'action',
p_bind_variable_name => 'action',
p_source_type => 'URI',
p_param_type => 'STRING',
p_access_method => 'IN',
p_comments => 'tells us if we''re starting to stopping the job');
ORDS.DEFINE_PARAMETER(
p_module_name => 'sessionless',
p_pattern => 'messages/',
p_method => 'POST',
p_name => 'message',
p_bind_variable_name => 'message',
p_source_type => 'URI',
p_param_type => 'STRING',
p_access_method => 'IN',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'sessionless',
p_pattern => 'messages/',
p_method => 'POST',
p_name => 'output',
p_bind_variable_name => 'out',
p_source_type => 'RESPONSE',
p_param_type => 'STRING',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'sessionless',
p_pattern => 'messages/',
p_method => 'POST',
p_name => 'id',
p_bind_variable_name => 'id',
p_source_type => 'URI',
p_param_type => 'STRING',
p_access_method => 'IN',
p_comments => NULL);
COMMIT;
END;
