Skip to content

Workaround for ORA-04063: view “x” has errors

October 31, 2012

When you deploy a procedure, function or package that has a compilation error, the object is still created, and you can still apply grants on them. This is convenient when deploying a large number of objects, meaning you don’t have to get them all in the right order. After deploying your schema, you can just recompile the invalid objects.

Unfortunately, this doesn’t work for views. Now, normally if you create a view with compilation errors, the view will not be created at all; for a deployment script, however, you could use CREATE FORCE VIEW that means the view will be created (but marked invalid).

Let’s say you have a view that depends on a table that doesn’t exist yet – and won’t exist until much later in your deployment scripts. So you create the view with the FORCE option – success. Then, you apply the GRANTs for the view, and get this:

ORA-04063: view "x" has errors

Why? If you try to grant on a procedure, function or package that has errors, it works fine. For views, apparently, this is not allowed.

Obviously, to solve this you might do the hard work and reorder your deployment scripts so that they create every object in the perfectly correct order, avoiding compilation errors entirely. If you have a large number of objects to deploy, this might be more trouble than you want. Well, there is a workaround:

1. Create the view, minus the bit that causes a compilation error.

2. Apply the grant.

3. Recreate the view, compilation error and all. The grant will remain.

Why might this be useful? In my case, we have two databases connected by database links (on both sides); and we need to deploy a large number of objects to both instances. They are managed by different teams, so we want to be able to deploy the changes to each independently. For the most part, the objects on “our side” compile fine, except for some views that refer to objects on the other side of the database link; but they won’t exist until the other team deploys their changes. We could even have a chicken-and-egg problem, when their views refer to objects on our instance; either way, some of the objects cannot be created error-free until both deployments have been completed.

As it stands, we have two options: deploy everything as best we can, then afterwards (when both deployments have completed), recompile the invalid objects and apply the view grants. An alternative is to use this workaround.

Demonstration

TEST CASE #1: cannot grant on a view with errors

SQL> create or replace force view testview as
     select 1 as col from bla;
Warning: View created with compilation errors.
SQL> grant select on testview to someone;
ORA-04063: view "USER.TESTVIEW" has errors
SQL> select grantee, privilege from user_tab_privs
     where table_name = 'TESTVIEW';
no rows selected

TEST CASE #2: grant on a view with errors

SQL> create or replace view testview as
     select 1 as col from dual;
View created.
SQL> grant select on testview to someone;
Grant succeeded.
SQL> create or replace force view testview as
     select 1 as col from bla;
Warning: View created with compilation errors.
SQL> select grantee, privilege from user_tab_privs
     where table_name = 'TESTVIEW';
GRANTEE PRIVILEGE
======= =========
SOMEONE SELECT

The above has been tested on Oracle 10gR2 and 11gR2. Should this mean that Oracle should not really raise ORA-04063 in this case? I think so.

About these ads

From → SQL

4 Comments
  1. interesting !

    Note, that you even cannot revoke select from a view when it is invalid:

    sokrates@11.2 > create view v as select * from dual;

    View created.

    sokrates@11.2 > grant select on v to jeff;

    Grant succeeded.

    sokrates@11.2 > create or replace force view v as select * from dua;

    Warning: View created with compilation errors.

    sokrates@11.2 > revoke select on v from jeff;
    revoke select on v from jeff
    *
    ERROR at line 1:
    ORA-04063: view “SOKRATES.V” has errors

    “…
    Well, there is a workaround:

    1. Create the view, minus the bit that causes a compilation error.

    2. Apply the grant.

    3. Recreate the view, compilation error and all. The grant will remain.

    Another workaround would be

    1. Create all objects.
    2. recompile all objects
    3. apply the grants

    , correct ?

    But: this would not work in your case when you have a chicken-and-egg problem between two databases connected by databae links.

  2. interestingly, you can’t even revoke grant select from an invalid view:

    sokrates@11.2 > create view v as select * from dual;

    View created.

    sokrates@11.2 > grant select on v to jeff;

    Grant succeeded.

    sokrates@11.2 > create or replace force view v as select * from dua;

    Warning: View created with compilation errors.

    sokrates@11.2 > revoke select on v from jeff;
    revoke select on v from jeff
    *
    ERROR at line 1:
    ORA-04063: view “SOKRATES.V” has errors

    I can’t see this behaviour documented anywhere, seems to be a bug

  3. Islam permalink

    Interesting workaround, thanks

  4. Paul permalink

    thanks for posting the workaround!
    I never would have thought of that

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 207 other followers

%d bloggers like this: