8 Replies Latest reply on Feb 16, 2011 12:31 PM by Thierry H.

    ORA-04063 View has errors

    DavidNils
      Hello,

      I'm trying to create a view but gets the following error/warning: ORA-04063 View XXXXX has errors.

      I have googled a bit and it seams that this error ocurrs when something that is inaccessible or non existans is referenced in the view query. However, the query executes fine when i run it in a query windows in Oracle SQL Developer.

      I'm Using 10g.

      Any ideas? Can it be that I need a perticular grant in order to run a query in a view?
        • 1. Re: ORA-04063 View has errors
          Mario Alcaide
          Hi,

          Are you sure the query executes correctly with the same user? You only need SELECT privilege on the table, and the GRANT CREATE VIEW.

          Maybe you have an invalid column name in the create view statement.

          Regards,

          Mario Alcaide

          http://marioalcaide.wordpress.com
          • 2. Re: ORA-04063 View has errors
            wiZ
            Hi,

            >
            04063, 00000, "%s has errors"
            // *Cause:  Attempt to execute a stored procedure or use a view that has
            // errors. For stored procedures, the problem could be syntax errors
            // or references to other, non-existent procedures. For views,
            // the problem could be a reference in the view's defining query to
            // a non-existent table.
            // Can also be a table which has references to non-existent or
            // inaccessible types.
            // *Action: Fix the errors and/or create referenced objects as necessary.
            >

            There is a colum/table to which your user doen't have access to. Execute the query as user you are trying to create the view. Make sure your user has been granted create view

            Regards,

            - wiZ
            • 3. Re: ORA-04063 View has errors
              DavidNils
              When I execute the query (with the same user) i do get a nice result.

              What would be an invalid columnname? and why would i be diffrent when executed in a view?

              /D
              • 4. Re: ORA-04063 View has errors
                mtefft
                While connected as the owner of the view, run the CREATE VIEW statement, making sure you do not use the keyword FORCE. Do you get any errors?
                • 5. Re: ORA-04063 View has errors
                  DavidNils
                  Hm, when I removed the FORCE keyword I got another error ORA 01031 "insufficent priviliges". So ut seams that i DO need some perticular grant somewhere. The view selects agains a sys-table (sys.dba_jobs). Can someone help me with what priviliges I need?
                  • 6. Re: ORA-04063 View has errors
                    Thierry H.
                    David,
                    From a priviledged user:
                    SQL>grant select on sys.dba_jobs to <application_user> ;
                    • 7. Re: ORA-04063 View has errors
                      DavidNils
                      Well, If my application user would not have been granted the select role, I would not be able to query the table in an ordinary query window either correct?

                      However, I have "solved" the issue in a way I probably shouldn't talk about ina DB-forum (~granted the user a hell whole of a lot of permissions~)

                      /D
                      • 8. Re: ORA-04063 View has errors
                        Thierry H.
                        Certainly has to do with the FORCE keywords...
                        Creating FORCE VIEWS  
                        A view can be created even if the defining query of the view cannot be executed, as long as the CREATE VIEW command has no syntax errors. We call such a view a view with errors. For example, if a view refers to a non-existent table or an invalid column of an existing table, or if the owner of the view does not have the required privileges, then the view can still be created and entered into the data dictionary. 
                        
                        You can only create a view with errors by using the FORCE option of the CREATE VIEW command: 
                        
                        CREATE FORCE VIEW AS ...;
                        When a view is created with errors, Oracle returns a message and leaves the status of the view as INVALID. If conditions later change so that the query of an invalid view can be executed, then the view can be recompiled and become valid. Oracle dynamically compiles the invalid view if you attempt to use it.  
                        http://www.oracle-dba-online.com/sql/create_and_manage_views.htm

                        HTH,
                        Thierry