15 October 2010

14 October quiz: What if the table already exists? (1507)

Great minds clearly think alike. In the 14 October quiz, I asked you to choose the blocks that would display "1" when executed. One of the correct choices was:
DECLARE
  l_count   INTEGER;
BEGIN
  DBMS_UTILITY.EXEC_DDL_STATEMENT ('CREATE TABLE plch_table (n NUMBER)');

  SELECT COUNT (*)
    INTO l_count
    FROM user_tables
   WHERE table_name = 'PLCH_TABLE';

  DBMS_OUTPUT.put_line (l_count);
END;
/
Several players raised a concern best expressed by MNL: 'It is not possible to answer today's quiz without knowing if the table plch_table exists in advance. If it exists, none of the choices display "1" because the possible exception from the table creation is not handled, which is an often seen mistake. To avoid any confusion you should add the general assumption that the code is executed on an empty schema.' I must admit that I was very surprised to see these responses, but not because it isn't a valid concern. It is. Certainly, whenever you execute DDL like this in your programs, you do need to either check first to see if the table exists, or trap the error that would occur if it did exist. Now having said that, I was surprised, because I had thought that we did have an assumption in place covering this. It read as follows:
'The session and the environment in which the quiz code executes has enabled output from DBMS_OUTPUT, and can reference only those datatypes, programs and database objects defined in the context of the quiz or are available in a default installation of the Oracle "instance."'
If you can't reference it from your schema, it doesn't exist, right? Well, clearly, this assumption was not clear enough for some players. So I have now changed it to:
'The only database objects and PL/SQL program elements ("things" that can be referenced in the quiz code) that exist are those defined in the context of the quiz or are available in a default installation of the Oracle instance.'
Does anyone feel that this needs to be adjusted further to avoid any confusion? Regards, SF

9 comments:

  1. Here's my suggestion:
    The session and the environment in which the quiz code executes has enabled output from DBMS_OUTPUT. Prior to running a quiz, the user schema contains no objects and have access only to those objects globally available with a default installation of the instance. Any DML or DDL executed by a choice is un-done when moving on to the next choice.

    Mike

    ReplyDelete
  2. Mike's suggestion doesn't account for public synonyms. If a public synonym exists it can affect the results irrespective of whether the user has access to the object referenced by the synonym or not (or even whether the object referenced by the synonym exists). The effect may be a different exception being raised, but still an effect.

    I'm not sure re-wording the assumption is necessary for this. It is more that people either don't read them or forget them. With over a thousand players, some people will always forget.

    If someone had thought that the topic of the challenge was about whether role-based privileges were accessible in a top-level anonymous block, they MIGHT have objected that the assumptions don't go the level of privileges. But that would really indicate the topic wasn't that, and that we should assume the table is created without any privilege problem (or tablespace quotas, read only tablespaces, etc).

    ReplyDelete
  3. Gary,

    I think that "access only to those objects globally available with a default installation of the instance" does exclude public synonyms other than those provided by Oracle.

    Maybe I should have written:
    "access only to those objects globally available within an unmodified default installation of the Oracle instance".

    But, it proves how hard it is to write a piece of text and make it positively unambiguous to such a large audience. A challenge that Steven is subjected to with every single quiz.

    Mike

    ReplyDelete
  4. How about: "the code is execute in a brand new installation with a brand new user" :-)

    I think that those concerns are for US people you love to add in a peanuts bag a "warning: this bad contains peanuts!" in small letter when in the big letters already says "Peanuts"

    ReplyDelete
  5. Gary,

    You got me thinking about the system privileges granted to the user taking the quiz.

    I kind of assumed it was in the assumptions somewhere :-)

    As it is not, I would say that you have a very valid point and, now that everybody knows, Steven has got to take action. If he doesn't, it won't be long before a smart guy will use this knowledge in self defense to recover from a bad day ;-)

    As the PL/SQL Challenge is for PL/SQL developers, one would assume that all players have sufficient privileges on their default tablespace. But, I just used the little word "assume" and that makes it a candidate for a prominent spot in the list of assumptions.

    Good catch, I think
    Mike

    ReplyDelete
  6. Filipe, that is ambiguous... "a brand new user" does not exclude the existence of another 20 brand new users, all of whom could be friendly ones who grant you access to stuff :-)

    I think you are right about the peanut bag though ;-)

    ReplyDelete
  7. Excellent points. I don't see the need to change my current text in order to deal with issues like synonyms, but I suppose I should provide an explicit list of privileges in effect for the schema in which the code runs. I will work on this.

    Thanks for the feedback, SF

    ReplyDelete
  8. The following statement should adequately address the issue of privileges:

    "Unless otherwise stated in the quiz, the user has sufficient system and object privileges to perform the specified activities."

    I agree that there is no need to change the current text regarding accessible objects -- synonyms are objects that are covered by the general statement.

    ReplyDelete
  9. JHall62, I really REALLY like that. Instead of coming up with a specific list, I can keep it general. I will use that approach. Many thanks, SF

    ReplyDelete