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?

Sayan Malakshinov · Accepted Answer · 2021-04-27 22:46:22Z

3

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

edited Apr 27, 2021 at 22:46

answered Apr 27, 2021 at 22:31

Sayan Malakshinov

8,7501 gold badge21 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sebastian Over a year ago

It seemed like it did the trick. I did however remove the "FORCE" as well, but I suspect it did little to nothing to solve my issue. But adding an empty new line inside the select seemed to do the trick.

Collectives™ on Stack Overflow

Oracle SQL create or replace view with/without column names

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related