How can I SELECT uncommitted records within the same transaction?

dyn-do 0
Viewed 29

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?

oracle typeorm sql
1 Answers

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

MT00
Related