20240123 - Flashback table example

create or replace PROCEDURE PROC_FLASHBACK_TEMP

AS

BEGIN

  delete from TST_PO_MST_TEMP;

  FOR rec IN (SELECT

                TO_CHAR(TRUNC(TO_DATE('2024-01-11 02:30:00', 'YYYY-MM-DD HH24:MI:SS') - (LEVEL - 1) / (24 * 6), 'MI'), 'YYYY-MM-DD HH24:MI:SS') AS interval_start

              FROM dual

              CONNECT BY LEVEL <= ((TO_DATE('2024-01-11 02:30:00', 'YYYY-MM-DD HH24:MI:SS')) - (TO_DATE('2024-01-06 02:30:00', 'YYYY-MM-DD HH24:MI:SS'))) * 24 * 6)

  LOOP

    INSERT INTO TST_PO_MST_TEMP

    SELECT rec.interval_start, po_mst.*

    FROM po_mst AS OF TIMESTAMP TO_TIMESTAMP(rec.interval_start, 'YYYY-MM-DD HH24:MI:SS')

    WHERE PO_NUM = '312937194_218905789';

  END LOOP;

END PROC_FLASHBACK_TEMP;

/