SQL Optimizer is not recognizing a bind variable when using a flashback query (4378847)

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.

Click To See Full Image.

WORKAROUND

None

STATUS

Waiting for fix in a future release of SQL Optimizer for Oracle