Questions regarding 30 August Quiz and ALL_PLSQL_OBJECT_SETTINGS (1366)

The 30 August quiz asked you to choose options that would stop a program unit from being usable if the optimization level for that unit was < 2 (the current default level of "aggressive" optimization). One choice, scored as correct, was:

CREATE OR REPLACE PROCEDURE compute_intensive
IS
  l_level PLS_INTEGER;
 
  PROCEDURE body_of_proc IS BEGIN NULL; END;
BEGIN
  SELECT PLSQL_OPTIMIZE_LEVEL into l_level
    FROM ALL_PLSQL_OBJECT_SETTINGS
   WHERE NAME = 'COMPUTE_INTENSIVE';
 
  IF l_level < 2
  THEN
     RAISE PROGRAM_ERROR;
  END IF;
 
  body_of_proc;
END compute_intensive;

Several players raised a concern about this scoring, with one person writing: "Hello, from my point of view today quiz contains a bug. The third choice should use not only NAME but as well OWNER in the WHERE clause [since it was a query against an ALL* view], otherwise it may fail even if the PLSQL optimization level for it is set to 2 (with TOO_MANY_ROWS). For this reason I have not selected it, while I assume it was intended as the "correct" choice." And as another player put it: "To be pedantically correct, shouldn't it either use USER_PLSQL_OBJECT_SETTINGS or include the OWNER in the where clause? There might be an object with the same name in a different schema that current user has access to (and therefore included in the ALL_* view.) (But if such an object existed, I would get a runtime exception of too many rows, so the answer is still 'correct.' Anyway, the general assumptions of course tells me that there probably is NOT another schema with such an object, so I'm not asking for any rescoring - mostly that when you publish the answers you might make a note that when using ALL_* view it's normally prudent to include OWNER in the where clause." Well, to tell you the truth, that last comment pretty much said it all. To summarize: 1. I should have included "OWNER = USER" in the query against the ALL_PLSQL_OBJECT_SETTINGS view. In fact, any query against an ALL* view should include a predicate in the WHERE clause for the owner - unless you really want to analyze rows across all schemas. 2. There is no need to rescore. As the second player explained, the assumption for the quiz is that the only DB objects that exist are those described in the quiz itself (or defined by Oracle with the installation of the database). So the query could only return one row. 3. I will change the choice to reflect this critique (add "OWNER = USER"). To conclude: no change in scores or ranks, but I will strive in the future to either use a USER* view or include an "OWNER=" predicate with an ALL* view. Thanks for pointing this out! Finally, another player had a very different concern: "The wording of today's quiz was really puzzling for a non-english like me. I'm not sure to have understood what was the question. Don't you think there was a simpler way to write the same sentence?" My apologies; I do see what you mean. I need to step embedding sentences within questions, as I do below: "Which of the choices define a program named "COMPUTE_INTENSIVE" in a way that ensures that the program cannot be used (it will either fail to compile or always raise an exception before the local subprogram body_of_proc is executed) unless the PL/SQL compiler optimization level for the program is set to at least 2?" Thanks for reminding me about this. I will continue to try to simplify the language I use in the questions. SF