How can I SELECT uncommitted records within the same transaction?

Question

I'm using OracleDB with TypeORM on Sveltekit.

I want to SELECT records that were updated before commit in the same transaction. However, regardless of whether the transaction ISOLATION LEVEL is set to SERIALIZABLE or READ COMMITTED, the records that can be retrieved by SELECT will be old records.

SELECT is running immediately after UPDATE in TypeORM's SQL log.
I can select correctly if I do an explicit commit after the update.
How can I retrieve the last written record?

(September 28 Edit) It seems that my post was wrong. very sorry.
With .Net Core 6 + C# 10 + Oracle, I was able to UPDATE a record and SELECT before commit to get the updated record.
However, with node.js + Sveltekit + TypeORM, even if I did the same thing as above, I got the old record before the update.
I want to select new records in the same way on the node.js side.

_"I want to SELECT records that were updated before commit in the same transaction"_ - it sounds like you're trying to do something like triggers or auto-auditing, [in which case you just need the `RETURNING` clause for `INSERT`/`UPDATE`](https://stackoverflow.com/questions/1074843/is-there-an-oracle-equivalent-to-sql-servers-output-inserted). — Dai, Sep 23 '22 at 01:05

score 0 · Answer 1 · answered Sep 23 '22 at 12:10

Don't try to do this from a third-party application.

Use PL/SQL and do it all in a single block:

DECLARE
  v_ids SYS.ODCINUMBERLIST;
BEGIN
  UPDATE table_name
  SET    value = 'something'
  WHERE  other = 'something else'
  RETURNING id BULK COLLECT INTO v_ids;

  OPEN :your_cursor FOR
    SELECT *
    FROM   table_name
    WHERE  id IN (SELECT COLUMN_VALUE FROM TABLE(v_ids));
END;
/

If you want you can wrap it in a procedure:

CREATE PROCEDURE update_table_name(
  i_new_value IN  TABLE_NAME.VALUE%TYPE,
  i_other     IN  TABLE_NAME.OTHER%TYPE,
  o_cursor    OUT SYS_REFCURSOR
)
IS
  v_ids SYS.ODCINUMBERLIST;
BEGIN
  UPDATE table_name
  SET    value = i_new_value
  WHERE  other = i_other
  RETURNING id BULK COLLECT INTO v_ids;

  OPEN o_cursor FOR
    SELECT *
    FROM   table_name
    WHERE  id IN (SELECT COLUMN_VALUE FROM TABLE(v_ids));
END;
/

Then call the procedure from TypeORM.

fiddle

How can I SELECT uncommitted records within the same transaction?

1 Answers1