CODEWITHSUNDEEP
sqloracle-database
Vinod Chelladurai
Vinod Chelladurai

Reputation: 539

Oracle view result - Strange order of columns

I am facing a strange problem while doing a reporting with oracle.

For example.

When I execute 

Select col1,col2,col3 
  from table

I am getting the expected order of columns in the result - col1, col2, col3.

But, when I create a view and select, for example:

create view report_view as 
  Select col1,col2,col3 
    from table

and then do 

select * 
  from report_view

the order of columns in the result is - col1, col3, col2.

Can anyone explain why this is happening? For your information, previously I had only col1 and col3 in my view and I added col2 recently in my view.

Upvotes: 0

Views: 437

Answers (2)

sqlab
sqlab

Reputation: 6436

You probably added col2 as the last column in your view. The order is like it is in your create statement

Upvotes: 0

Erkan Haspulat
Erkan Haspulat

Reputation: 12562

Issue the query below and check COLUMN_ID to know in which order your columns will be shown when you use * in your select statement.

select TABLE_NAME, COLUMN_NAME, COLUMN_ID 
from ALL_TAB_COLUMNS
where TABLE_NAME = '[YOUR_VIEW_NAME_HERE]'

If you insist on using * in your view, then you must drop/create your table and order the columns in the way you need them to be.

Upvotes: 1

Related Questions