Workaround for ORA-00942: table or view does not exist when executing GRANT on a table list

When executing dynamic SQL against a schema that has differenly named objects, you will run into the "ORA-00942: table or view does not exist" during execution.

For example in my schema, I had 32 tables and one of them were named "flyway_schema_history". This means oracle will preserve the lower case naming in the dictionary.

The work around is to use DBMS_ASSERT.ENQUOTE_NAME to quote only when required.

In the example below, I am granting all of SCOTT's object to JIM:

BEGIN
  FOR r IN (
    SELECT object_name
    FROM   dba_objects
    WHERE  owner = 'SCOTT'
    AND    object_type IN ('TABLE', 'VIEW')
    AND    status = 'VALID'
  ) LOOP
    EXECUTE IMMEDIATE
      'GRANT SELECT ON SCOTT.' ||
      DBMS_ASSERT.ENQUOTE_NAME(r.object_name, FALSE) ||
      ' TO JIM';
  END LOOP;
END;
/