Hi,
Confusing problem. If we leave aside the comment part it looks like an attempt on a parameterized view. Which I think Oracle should seriously consider supporting directly. Something like parameterized cursors in PL/SQL.
So you will probably use sys_context to prime the session and then have the view use this for filtering or something. Putting all these into a standardized WITH block makes sense. Even more to then fix it with materialize against multiple executions due to view merging.
Experiments then show this :
This works (i.e. you can insert a row) :
create or replace view vw as
with
parms AS (
select sys_context('USERENV', 'SID') ps from dual)
select c1
from t
where c1 < (select ps from parms);
so it is not the use of dual in with per se that triggers the problem.
With this it no longer works :
select /*+ materialize */ sys_context('USERENV', 'SID') ps from dual)
but I get an ORA-01732 (non-updateable view) which is different from your problem. I guess materialize => temp table -> not-updateable, even if it is an "internal" temp table? But without the hint Oracle can use materialization too (as per explain plan). Would that too make the view non-updateable?
This works (but may not prevent all duplicate executions) :
select /*+ NO_MERGE */ sys_context('USERENV', 'SID') ps from dual)
Strangely I only get your problem (ORA-01031) when leaving off the filter (where c1 < ...). One would think that an unused WITH-Query is optimized away?
It is not treated as a CROSS join, because this results in ORA-01779 (non-key preserved) :
create or replace view vw as
with
parms AS (
select sys_context('USERENV', 'SID') ps from dual)
select c1
from t
lateral cross join parms p
where c1 < p.ps;
No way to tell oracle that this will not duplicate rows?
So the question requires more specific use cases. I.e. are the constants always accessed and if so how?
regards,