While the code itself was correct, the comments attached to the names of columns in logtab were not correct. Specifically, I wrote this:
, created_on /* column type is VARCHAR2(30) */ , created_by) /* column type is DATE */
when I should have written:
, created_on /* column type is DATE */ , created_by) /* column type is VARCHAR2(30) */
Ironically (and believe you me, very irritatingly), I did not simply include the CREATE TABLE DDL for logtab, because I wanted to make it harder for a player to copy/paste the code and simply run it to get the answer. The table was already created in my schema, so when I compiled this package and tested the choices, everything was fine. Argh!
If you created the table according to these comments, then any attempt to insert a row into the table would raise an Oracle error (unless your Oracle schema name can be converted implicitly to a date - and what's the chance of that?). So the following choice, which we scored as "correct", is actually incorrect: "Replace NULL with -1403. Execute the block and a row will be inserted into logtab; then the change will be committed." (I will refer to this choice below as BADCHOICE) There was another choice that was scored as correct, and it remains correct, because under that scenario, no insert was ever attempted: "Run the block unchanged. No new row will be inserted into logtab." Many, many players wrote to us, having noticed this discrepancy between comment and actual usage. I congratulate all of you on your close reading of our quizzes. We certainly can't "sneak" anything by you! I also apologize to everyone. It is hard enough when you are presented with a long, complicated quiz. To then discover a mistake inside of it, well, that can be, and I am sure was to many, very frustrating. So this is what we did to fix our mistake:- Fix the question so that it no longer has this problem.It is vitally important that our database of questions and answers does not have mistakes.
- Everyone (321 players) who did not mark BADCHOICE as correct now have their answer changed to "correct".
- The scores of those same people are increased to reflect this change.
- Rankings are refreshed.
Player "MarcJT" was the first to report this mistake. Nice work, Marc, and you win your choice of an O''Reilly e-book.
I am not sure the re-scoring due to this mistake would justify the scores of the players who did notice this mistake and consider it as mistake and answered accordingly.
ReplyDeleteAnyways, I am not concerned about the score but would like a clarification on the comment "If you created the table according to these comments, then any attempt to insert a row into the table would raise an Oracle error (unless your Oracle schema name can be converted implicitly to a date - and what's the chance of that?)."
I am not sure what "unless your Oracle schema name can be converted implicitly to a date" means. I guess this is some feature that I am not aware of or may be I am not interpreting this statement correctly.
Finn/SF: Can you please explain?
Thanks...
Blogspot is having problems with comment moderation and is wiping out comments, so I post this for Ramesh:
ReplyDeleteI am not sure the re-scoring due to this mistake would justify the scores of the players who did notice this mistake and consider it as mistake and answered accordingly.
Anyways, I am not concerned about the score but would like a clarification on the comment "If you created the table according to these comments, then any attempt to insert a row into the table would raise an Oracle error (unless your Oracle schema name can be converted implicitly to a date - and what's the chance of that?)."
I am not sure what "unless your Oracle schema name can be converted implicitly to a date" means. I guess this is some feature that I am not aware of or may be I am not interpreting this statement correctly.
Finn/SF: Can you please explain?
Regarding my somewhat tongue-in-cheek remark about schemas, at least in Oracle11g it is possible to create a schema with the name "2010-07-05".
ReplyDeleteRegarding your comment about who deserves and does not deserve a re-scoring, our feeling is that we cannot make a judgment call on the intentions of a user who answered the question.
alter session set nls_date_format='Month';
ReplyDeleteselect user a,to_date(user) b, to_char(to_date(user),'dd-mon-yyyy') c from dual;
A B C
-------- --------- --------------
JUNE June 01-jun-2010
:)
Good one, Gary, nicely found :-)
ReplyDeleteThe question is not mentioning that triggers are not existing on the table, so we may actually be able to insert a NULL into a column that is part of a primary key if a trigger exists...:
CREATE TABLE TEST (a VARCHAR2(1), b VARCHAR2(1));
ALTER TABLE TEST
ADD CONSTRAINT pk_a PRIMARY KEY (a);
CREATE OR REPLACE TRIGGER tr_test
BEFORE INSERT
ON TEST
FOR EACH ROW
BEGIN
IF :NEW.a IS NULL
THEN
:NEW.a := '-';
END IF;
END;
/
INSERT INTO TEST
VALUES (NULL, NULL);
1 row inserted
Now I had that answer wrong because I misunderstood, not because I was thinking about a potentially existing trigger, so I do not even earn a rescore ;-), but it is probably an extra piece of info you would mention in such a question ("no triggers exist")
Toine
Toine,
ReplyDeleteOne of our assumptions is:
"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."
Since the trigger did not appear in the question text, you should definitely assume that no triggers exist.
SF
Yes, that piece of information is indeed sufficient. No need to add that piece of info then (now I did not even consider the possibility of a trigger when answering the question, just made another stupid mistake...seems like I made several the last week...like answering wrong too quickly on the most simple question I have seen so far. But on the answering quickly there is another topic ;-)).
ReplyDelete