SQL Optimizer is not recognizing a bind variable when using a flashback query .
To reproduce the issue please follow the steps below:
CREATE TABLE empleados (
id NUMBER PRIMARY KEY,
nombre VARCHAR2(50),
salario NUMBER
);
ALTER TABLE empleados ENABLE ROW MOVEMENT;
select * from empleados;
INSERT INTO empleados VALUES (1, 'Ana', 5000);
INSERT INTO empleados VALUES (2, 'Luis', 6000);
COMMIT;
SELECT SYSTIMESTAMP FROM DUAL;
Write down the result and wait for some minutes (for example 13/03/2025 11:20:12.565000 +01:00).
Then run these statements:
UPDATE empleados SET salario = 7000 WHERE id = 1;
DELETE FROM empleados WHERE id = 2;
COMMIT;
If you run this
SELECT * FROM empleados AS OF TIMESTAMP TO_TIMESTAMP('13/03/2025 11:20:19', 'DD/MM/YYYY HH24:MI:SS');
You will get this
1 Ana 5000
2 Luis 6000
If you run this but adding the parameter, it also works
SELECT *
FROM empleados AS OF TIMESTAMP :b3;
But when you try to auto optimize the query above with sql optimizer, you get
ORA-30055: NULL snapshot expression not allowed here
Invalid SQL statement.
Verify that you have selected the right schema.
Only one SQL statement can be optimized in this window.
To analyze PL/SQL code or multiple SQL statements use the Scan SQL.
WORKAROUND
None
STATUS
Waiting for fix in a future release of SQL Optimizer for Oracle
