Hi:
On Mon, May 16, 2016 at 9:46 AM, Sachin Kotwal <kotsachin@gmail.com> wrote:
>> You need to drop the view before recreating it. Then it works. If you
>> changed the access to the view with grants or revokes, you also neet to
>> recreate them. They are dropped with the view.
> Sorry to say but If we need to drop and replace then what is use of "Create
> OR Replace " syntax?
That is the use case. Create or replace <whatever> is for doing
backwards compatible changes without having to worry about what other
things is running concurrently with you. If you make non-backwards
compatible changes the system forces you to drop, so you can see
dependencies and who is using the things before dropping ( unless you
go trigger happy with cascade ).
>>> If its not a bug and a limitation kindly guide me towards any
>>> documentation where it is mentioned.
>> CREATE OR REPLACE VIEW is similar, but if a view of the same name already
>> exists, it is replaced. The new query must generate the same columns that
....
> If this is the limitation. Is community is planning update this or add this
> feature soon?
IMO you've got it backwards. The limitation IS the feature. In my
case, as an example, I can code every view ( and functions ) in a
script file using create or replace. And when I find a bug / want to
make a backwards compatible improvement I can just edit the script and
fire it again, and this feature insures I do not impact other code if
I inadvertently rename a column, or delete it.
If the feature were to be removed, and backwards-incompatible changes
were allowed, a lot of people will be unhappy.
Francisco Olarte.
