Hi,

How about :

CREATE OR REPLACE VIEW MY_VW AS
  SELECT 1 A FROM DUAL;

COMMENT ON TABLE MY_VW IS 'Clever comment.';

select * from user_tab_comments where table_name = 'MY_VW';

TABLE_NAME TABLE_TYPE COMMENTS ORIGIN_CON_ID
MY_VW VIEW Clever comment. 0

comment on column my_vw.a is 'a column';

select * from user_col_comments where table_name = 'MY_VW';

TABLE_NAME COLUMN_NAME COMMENTS ORIGIN_CON_ID
MY_VW A a column 0

Same as with tables.

regards,

Thx for the replies, but I did not seem to get my "issue" across.
Concerning the 'comment' use case, of course there are enough alternatives as mentioned.

"The problem is you only have select privs on dual. You need to grant other DML on it for this approach to work."

Two remarks about that:
(1) Give extra priviliges to SYS.DUAL can't be a solution we should really considder.
(2) Why does the DML work if there is only SELECT privileges on a table in an other schema, but won't work with DUAL?

Further, this is not only about commments, but also using selecting sys_context(...) from dual in your view, will break the DML

create table t (
  c1 int
);
insert into t values ( 1 );
commit;

create or replace view vw as 
  select c1, sys_context ( 'USERENV', 'SID' ) sid from t;
  
select * from vw;

C1   SID   
   1 111  

insert into vw ( c1 ) values ( 2 );

select * from vw;

C1   SID   
   1 111   
   2 111  

The privilege you need on dual I believe is select with grant option, not insert/update/delete.

Using mutliple sys_context(...), although assumed deterministic, could lead to performance issues when using in select-lists.
And my argument does not apply necessary to the "select sys_context from dual" use-case per se.
There are other use cases where usage of DUAL could be usefull.

It's exactly this remark you make:
2. Not sure; remember dual is a special table that lives by its own rules.
Which triggers me.
I Always assumed that DUAL is the only object in the Database which the Oracle DB engine should considder to be read-only in all circumstances.
For some reasons however, when used in view-DML, the privileges on DUAL are checked - and is, in my opinion, superfluous.

create table test_employees
( id number 
, name varchar2(255)
, hiredate date
);

create or replace view test_employees_vw as 
select 
/* Created   : xxxx
|| Changelog : xxxx
*/
id
, name 
, hiredate
, ( select dummy from dual ) dummy
from test_employees;

insert into test_employees_vw 
(id,name, hiredate)
values
(1, 'John', date '2017-12-01');

select * from test_employees_vw;

ID   NAME   HIREDATE               DUMMY   
   1 John   01-DEC-2017 00:00:00   X       

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,