we have a need to retain the data in the stream after the first transaction consumes it. How do we do this? The second transaction will consume again and offset the stream. Need a solution for the 2nd DML to see the stream data after the first DML is done. both the DML's will need the same data in the stream.
2 Answers
Sergiu's comment would be the much preferred option but another approach may be to query the stream first using a simple SELECT statement and then follow it up with a DML statement based on the last query result
SELECT * FROM my_stream;
INSERT INTO my_table select * from table(result_scan(last_query_id()));
It is recommended that users create a separate stream for each change record recipient for the table.
Note that a stream itself does not contain any table data.
Reference: Multiple Consumers of Streams
Related