This content has been marked as final. Show 8 replies
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.
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
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?
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?
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?
From a priviledged user:
SQL>grant select on sys.dba_jobs to <application_user> ;
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~)
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.