In another RDBMS, I can perform multiple DMLs within the same WITH clause by passing the returned values from one DML into another without staging the data. Below is an example:
WITH insert_source AS(
INSERT INTO trnxn_tab(col1,col2,col3,col4)
VALUES(val1,val2,val3,val4)
RETURNING col4,trxn_id
) UPDATE account SET last_activity_date = sysdate, account_bal = (account_bal - insrt.col4), last_trxn_id = insrt.trxn_id
FROM insert_source insrt
WHERE account_number = insrt.account_number;
Even though this isn't as pretty as I would expect, it does allow me to do multiple DMLs by using the returned keys and values from one dml into another. I know there are workaround in Oracle but adding this will be cool. I will prefer the Oracle version avoids that from clause as below:
WITH insert_source AS(
INSERT INTO trnxn_tab(col1,col2,col3,col4)
VALUES(val1,val2,val3,val4)
RETURNING col4,trxn_id
) UPDATE account SET (last_activity_date, account_bal, last_trxn_id) = (SELECT sysdate, (account_bal - insrt.col4) ,insrt.trxn_id FROM insert_source insrt)
WHERE account_number = insrt.account_number;