Database Actions, our web browser powered version of SQL Developer, is brought to you by Oracle REST Data Services (ORDS).
While Database Actions has MANY things in common with our Desktop tools, there’s one very important difference: it’s stateless.
What does this mean, exactly?
In the desktop application, there is a JDBC driver being used to connect directly to the database. This connection is stateful – I can do things and say…COMMIT that work, or I can say let’s NOT persist that work and do a ROLLBACK. The connection will sit there until I’m done with it. When I close it, that connection ‘goes away.’
ORDS delivers connectivity to the database via HTTPS to the clients, applications, etc making requests.
Every time we go to run something in a SQL worksheet, in our browser, a new connection is borrowed from the JDBC Connection Pool (UCP) being maintained by ORDS.
I can see a few of these connections from the connection pool in the screenshot above, they are the ones belonging to ORDS_PUBLIC_USER.
The work being sent by the SQL worksheet is applied then the connection used for that work is given back to the pool for the next HTTPS request.
This sharing of connections is how, 500 people can concurrently use a database by only needing 5, 10, 50, or maybe 100 connections vs 500.
In the World Wide Web, we don’t want to maintain a ‘heavy’ JDBC connection. Instead we use HTTPS. ALL of the information required to do the work, is included in the request. So there’s no ‘known information’ about what you did previously. If that information needs to be maintained, it has to be included on EVERY request.
What we do on every SQL/Script call in the browser
If we open Developer Tools in our browser, we can see what goes across the network when I hit the Execute button.
An HTTPS POST is called an endpoint called SQL – that’s our REST Enabled SQL Service. Our SQL script engine is there, and allows for ad hoc query stuff. Exactly what you’d need in a SQL worksheet.
But if we look at what was sent to be executed, we see more than our SELECT.
SET SERVEROUTPUT ON\nset define &;\nset escape OFF;\nset timing ON;\nselect * from I_SEE_YOU order by id desc;See all the SET commands? They get sent along on every query, so the session can be prepared for the incoming statements to be executed.
AutoCOMMIT
Since there’s no session state being maintained, that also means the concept of ROLLBACK and COMMIT go away in large part.
That’s why you have COMMIT/ROLLBACK buttons in SQL Developer desktop toolbar but NOT in the browser when you’re using SQL Developer Web.
The SESSION begins and ends on every request (SQL or Script execution). That means before we give up the connection to the pool, we do an IMPLICIT COMMIT.
That’s why I can see data in program number 2 that was inserted in program number 1 without the benefit of an EXPLICIT COMMIT.
Does that mean we CANNOT do a COMMIT or ROLLBACK in the Web?
No – you CAN in fact do both. It must occur IN the block of code being sent to the database, not afterwards at your convenience.
This means you can also have COMMIT and ROLLBACK statements INSIDE your REST APIs for ORDS as well. Just remember that everything runs in it’s own session, and after that…you are back at the starting line.
