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
Here's my suggestion:
ReplyDeleteThe 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
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.
ReplyDeleteI'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).
Gary,
ReplyDeleteI 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
How about: "the code is execute in a brand new installation with a brand new user" :-)
ReplyDeleteI 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"
Gary,
ReplyDeleteYou 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
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 :-)
ReplyDeleteI think you are right about the peanut bag though ;-)
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.
ReplyDeleteThanks for the feedback, SF
The following statement should adequately address the issue of privileges:
ReplyDelete"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.
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