Oracle SQL create or replace view with/without column names

Question

I got a question regarding views in Oracle. Let's say we create a view:

Create View V1(col1, col3, col2) as
(
  select a as col1,
         b as col2,
         c as col3
   from x.y
)

And let us say that you then find out that you now are selecting it wrong because you select it as:

select col1, col2, col3 
from V1 
--Expecting col1, col2, col3
--but gets  col1, col3, col2

Now you make a create or replace without the header names

Create or replace force editionable View V1 as
(
  select a as col1,
         b as col2,
         c as col3
   from x.y
)

Now you are still getting the same issue. Therefore my question is: When you create a view where you defined the headers, does Oracle store the position of the headernames somewhere and needs to be reloaded before the select goes correctly through?

Answer 1

Unfortunately Oracle checks the query text only and since it has not been changed, Oracle doesn't not really rebuild it.

Just modify a text a bit and all will be fine:

SQL> create or replace view v1(a,c,b) as select 'a' a,'b' b,'c' c from dual;

View created.

SQL> select * from v1;

A C B
- - -
a b c

SQL> create or replace view v1(a,b,c) as select 'a' a,'b' b,'c' c from dual;

View created.

SQL> select * from v1;

A C B
- - -
a b c

-- I've added just one extra space after dual:
SQL> create or replace view v1(a,b,c) as select 'a' a,'b' b,'c' c from dual ;

View created.

SQL> select * from v1;

A B C
- - -
a b c

PS. This bug exists on Oracle 19+ only. I've checked it on 11.2.0.4 and 18.5, and all is fine on them. It's better to register SR. Let me know if you want me register it