Beware of using force when creating objects
I got bitten by this particular issue the other day. When I am attempting to create a basic view:
CREATE OR REPLACE FORCE VIEW "MY_SCHEMA"."MY_VIEW" AS SELECT mydata from mytable;
And I get the following error:
Error report: SQL Command: CREATE OR REPLACE VIEW Failed: Warning: execution completed with warning
There was an issue with the SQL and I tried to do a show error.
show error; No Errors.
Inspecting the object status only revealed that it was invalid.
select * from user_objects where object_name = 'MY_SCHEMA';
I read up on force and decided to try to remove it and I got the error:
CREATE OR REPLACE VIEW "MY_SCHEMA"."MY_VIEW" AS SELECT mydata from mytable; Error report: SQL Error: ORA-00998: must name this expression with a column alias 00998. 00000 - "must name this expression with a column alias"
I looked up the error: http://ora-00998.ora-code.com/
And the fix is easy:
CREATE OR REPLACE VIEW "MY_SCHEMA"."MY_VIEW" ("mydata") AS SELECT mydata from mytable;
Lesson learned. Beware when you use force for creation of objects.