CODEWITHSUNDEEP
oracle-database
ennovation
ennovation

Reputation: 366

Oracle - Create or Replace View - preserving the comments on the columns

I have a view with some fixed number of columns. These columns are specified with comments.

Now I change the view sql (some where conditions) using the "create or replace" (not manually, some business logic of my java application changes this view sql)

The view gets created, and working fine. But the only problem is that all the comments added to the columns are all lost.

Though I understand we are rewriting the ddl of the view, is there a way to preserve the comments even after the sql change?

Upvotes: 1

Views: 1952

Answers (1)

Sylvain Leroux
Sylvain Leroux

Reputation: 51990

CREATE OR REPLACE VIEW will create ... or replace the view. That is, there is an implied DROP VIEW on it¹.

If you need to change some constraints on your view without dropping it entirely you probably need ALTER VIEW instead. However, you can't use it to change the definition of your view.

In your particular case, as you need to update the WHERE clause, maybe an alternate option would be to have some kind of master view with all relevant columns and comments -- but without the WHERE clause. And have an other view on top of it which add only the WHERE clause. That way you only have to replace that second-level view when you need to update it.

¹ As noticed by be here now in a comment bellow, this is a little bit over simplified as *"privileges are preserved after create or replace". To quote the relevant part of the documentation(emphasis mine):

Specify OR REPLACE to re-create the view if it already exists. You can use this clause to change the definition of an existing view without dropping, re-creating, and regranting object privileges previously granted on it.

INSTEAD OF triggers defined in the view are dropped when a view is re-created.

Upvotes: 5

Related Questions