rename columns in a view Oracle-CodePudding

we have a view let's say table1_v

if I run select * from table1_v ,I get an output with a column - Year (Jahr)

I want to know what is the underlying sql so I can find it in Sql developer under details of this view or in all_views.

I wonder because the sql looks like this select year from table1. such sql will give my the column name YEAR and not Year (Jahr)

I would expect something like this in the view definition - select year as "Year (Jahr)" from table1

so my question is,where could the column name be renamed if not in the view definition and if it is best practice?

CodePudding user response：

If you do want to rename it in the view then give the column an alias:

CREATE VIEW view_name (col1, col2, col3, col4, "Year (Jahr)", col6, col7) AS
SELECT col1, col2, col3, col4, year, col5, col6
FROM   table_name;

However, don't, as it is not best-practice.

If you want to include mixed-case, spaces and symbol characters in your identifiers then you will need to use a quoted identifier and that means you would need to use the exact same quoted identifier wherever it is reused in queries. Because of the difficulties caused by matching cases, etc. using quoted identifiers is not considered best-practice and you should use unquoted identifiers wherever possible.

Where could the column name be renamed if not in the view definition and if it is best practice?

Instead of changing the view, you can alias the column when you SELECT from the view when you want to display the output:

SELECT col1, col2, col3, col4, year AS "Year (Jahr)", col6, col7
FROM   view_name;