My lucky find for ORA-01720: Grant Option Does Not Exist.

Strangest thing happened the other day during a customer upgrade.  Our automated scripts were in the process of creating a view and I received the error:

ORA-01720: Grant Option Does Not Exist.

OK…..  I get it…  BUT…. I didn’t even execute a GRANT statement!?  I was just executing a CREATE OR REPLACE VIEW…. what gives?

Doing some searching online, I didn’t find too much that fit my issue.  Everyone was getting this error with a GRANT statement.  So, I turned to My Oracle Support and I found my new favorite note (only because it saved the day).   Because I could not find my solution outside of My Oracle Support, I thought I’d post it here to help others.

Note: 1628033.1 – Post Upgrade to 11.2.04, “create or replace view” execution fails with “ORA-01720: Grant Option Does Not Exist”

According to Oracle:

"The code was changed in 11.2.0.4 so that create view behavior is similar to grant. If you try to make a GRANT on an existing view and the view owner does not have the grant option, then ORA-1720 is the expected result (even in 11.2.0.3). In 11.2.0.4, we have simply made CREATE VIEW consistent with the GRANT behavior, i.e. if an incompatible grant exists, then the new view definition must not be allowed (even with FORCE). In other words, we do not allow incompatible grants to coexist with the view definition and so an error must be thrown. The behavior in release 11.2.0.3 (and earlier) was incorrect; the new behavior in 11.2.0.4 is intentional and correct."

To correct:

1. Remove all grants on the view before REPLACING the view. This will ensure that no incompatible grants exist.
2. Drop and recreate the view. Dropping the view will automatically remove all grants.

In my case, the customer had just upgrade to 11.2.04 and bingo, immediately ran into this issue.  Hope this can help someone else!