tag:blogger.com,1999:blog-8677649049588007585.post7423594394592098761..comments2023-06-18T16:15:22.432+01:00Comments on PL/SQL Challenge: Mistake confirmed for quiz on 5 July 2010(461)Steven Feuersteinhttp://www.blogger.com/profile/16619706770920320550noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-8677649049588007585.post-10112602931170442892010-07-07T15:40:16.204+01:002010-07-07T15:40:16.204+01:00Yes, that piece of information is indeed sufficien...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 ;-)).Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-9149657793947363412010-07-07T13:48:20.260+01:002010-07-07T13:48:20.260+01:00Toine,
One of our assumptions is:
"The sess...Toine,<br /><br />One of our assumptions is:<br /><br />"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."<br /><br />Since the trigger did not appear in the question text, you should definitely assume that no triggers exist. <br /><br />SFSteven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-88037459785708766092010-07-07T13:33:26.174+01:002010-07-07T13:33:26.174+01:00Good one, Gary, nicely found :-)
The question is ...Good one, Gary, nicely found :-)<br /><br />The 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...: <br /><br />CREATE TABLE TEST (a VARCHAR2(1), b VARCHAR2(1));<br /><br />ALTER TABLE TEST <br /> ADD CONSTRAINT pk_a PRIMARY KEY (a);<br /><br />CREATE OR REPLACE TRIGGER tr_test<br /> BEFORE INSERT<br /> ON TEST<br /> FOR EACH ROW<br />BEGIN<br /> IF :NEW.a IS NULL<br /> THEN<br /> :NEW.a := '-';<br /> END IF;<br />END;<br />/<br /><br />INSERT INTO TEST<br /> VALUES (NULL, NULL);<br /><br />1 row inserted<br /><br />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")<br /><br />ToineAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-46923908320762798362010-07-07T01:34:56.333+01:002010-07-07T01:34:56.333+01:00alter session set nls_date_format='Month';...alter session set nls_date_format='Month';<br />select user a,to_date(user) b, to_char(to_date(user),'dd-mon-yyyy') c from dual;<br /><br />A B C<br />-------- --------- --------------<br />JUNE June 01-jun-2010<br /><br />:)SydOraclehttps://www.blogger.com/profile/08828771074492585943noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-6381086098152460702010-07-06T21:17:36.206+01:002010-07-06T21:17:36.206+01:00Regarding my somewhat tongue-in-cheek remark about...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". <br /><br />Regarding 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.Steven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-19471515739326551362010-07-06T15:23:46.898+01:002010-07-06T15:23:46.898+01:00Blogspot is having problems with comment moderatio...Blogspot is having problems with comment moderation and is wiping out comments, so I post this for Ramesh:<br /><br />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.<br /><br />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?)."<br /><br />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.<br /><br />Finn/SF: Can you please explain?Steven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-88672329586090117182010-07-06T05:50:00.021+01:002010-07-06T05:50:00.021+01:00I am not sure the re-scoring due to this mistake w...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.<br /><br />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?)."<br /><br />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.<br /><br />Finn/SF: Can you please explain?<br /><br />Thanks...Rameshhttps://www.blogger.com/profile/04557315341209397856noreply@blogger.com