We have a PB classic application migrating to PS (latest PB etc.), connecting to Oracle 19 database. When the application runs in PS it is not encapsulating multiple SQL statements in a transaction, almost as though a commit is issued between each statement, but Autocommit is set to false.
Here is an example / proof of what is going on:
In a single script I have the following code to insert the same session values twice, no commit in between (no error checking as this is just to prove a point):
Long ll_session
datetime dtNow
dtNow = datetime(today(), now())
string sUser
SELECT SYS_CONTEXT('userenv', 'sessionid') into :ll_session from dual using sqlca;
sUser = g_username + '-1'
INSERT INTO UNIPROM2.UNIPROM_SESSION_USER
SELECT :ll_session, :sUser, :dtnow from DUAL
using sqlca;
SELECT SYS_CONTEXT('userenv', 'sessionid') into :ll_session from dual using sqlca;
sUser = g_username + '-2'
INSERT INTO UNIPROM2.UNIPROM_SESSION_USER
SELECT :ll_session, :sUser, :dtnow from DUAL
using sqlca;
When I run this in PB classic I get two rows with the same session ID, as you would expect:
162878 Jason.Frost-2 17/MAR/22
162878 Jason.Frost-1 17/MAR/22
But when I run it from the PS cloud app (same database, same user, same code) I get the following two rows of data, clearly a different session ID per SQL statement:
162875 Jason.Frost2-2 17/MAR/22
162874 Jason.Frost2-1 17/MAR/22
This means that I can't roll back a multi statement / multi dw update, which is a real problem.
Can anyone shed any light on why this is happening or how to change it?
