* the two cursors returned from the same stored proc are inconsistent
Assuming we are talking about another transaction modifying the data between
time t1 when c1 is opened and time t2 when c2 is opened, this is correct and is
the default statement-level isolation provided
https://docs.oracle.com/en/database/oracle/oracle-database/21/cncpt/data-concurrency-and-consistency.html#GUID-2A0FDFF0-5F72-4476-BFD2-060A20EA1685
https://docs.oracle.com/en/database/oracle/oracle-database/21/adfns/sql-processing-for-application-developers.html
You can try SERIALIZABLE depending on your circumstances.
Cheers,
Dominic
From: Michael O'Shea/Woodward Informatics
Ltd<mailto:woodwardinformatics@xxxxxxxxxxxxxxxx>
Sent: 11 October 2022 17:27
To: ORACLE-L (oracle-l@xxxxxxxxxxxxx)<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: Flashback query
Hi chaps, a quick question if I may.
I have contrived the stored procedure and results below yet it does loosely
mimic something on a large prod database.
The underlying issue for them is that the two cursors returned from the same
stored proc are inconsistent without the „as of timestamp“. To solve this
historical problem, they have adopted this flashback query approach (it is a
very busy OLTP database) however recently they have begun to encounter the
ORA-01555 snapshot too old errors.
My questions
* What other database centric options have they got?
* How crap is this?
21c EE RHEL hosted inhouse.
Mike
Woodward Informatics Ltd,
http://www.strychnine.co.uk<https://nam12.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.strychnine.co.uk%2F&data=05%7C01%7C%7Cb0efdfa208e74664b5df08daaba580da%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638011024619624096%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=T26r8XnNyP7kAhLKV%2BKVXPglrOU305dtSW4v9QzOHuo%3D&reserved=0>
SQL>
SQL>
SQL>
SQL> var rc1 refcursor
SQL> var rc2 refcursor
SQL>
SQL> create or replace procedure getTest(c1 out sys_refcursor, c2 out
sys_refcursor) as
2 t1 timestamp;
3 begin
4 select systimestamp into t1 from dual;
5 open c1 for select sum(id) idSum from test as of timestamp t1;
6 dbms_session.sleep(10); --used to simulate the query above taking a
long time and allowing for other sessions to update table test
7 open c2 for select sum(id) idSum from test as of timestamp t1;
8 end;
9 /
Procedure created.
SQL>
SQL> exec getTest(:rc1, :rc2);
PL/SQL procedure successfully completed.
SQL> print rc1
IDSUM
----------
14080
SQL> print rc2
IDSUM
----------
14080
