tag:blogger.com,1999:blog-8677649049588007585.post3621927392932986717..comments2023-06-18T16:15:22.432+01:00Comments on PL/SQL Challenge: Questions regarding NEW and OLD quiz of 10 November (1643)Steven Feuersteinhttp://www.blogger.com/profile/16619706770920320550noreply@blogger.comBlogger12125tag:blogger.com,1999:blog-8677649049588007585.post-1126457868946604242010-11-12T15:18:36.267+00:002010-11-12T15:18:36.267+00:00William, I like your phrasing better. I should hav...William, I like your phrasing better. I should have chosen something more general, and will change the question to do that. I still feel, though, that "code" is a sufficiently general reference and will not be changing the scoring.<br /><br />SFSteven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-65968253927520449132010-11-12T08:09:34.153+00:002010-11-12T08:09:34.153+00:00I suspect the "oddly enough" comment in ...I suspect the "oddly enough" comment in the answer explanation gives away that Steven sees it as all one thing, with an arbitrary syntactical quirk for one of the clauses.<br /><br />If the question had been:<br /><br /><i>"A colon (:) must precede the OLD and NEW qualifiers wherever you reference the old and new column values to define a row-level trigger"</i><br /><br />I would have immediately said that was false. The fact that it took the trouble to refer to the <b>code</b> specifically suggested that it was referring to the PL/SQL code section.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-56025548903280209272010-11-12T06:53:34.754+00:002010-11-12T06:53:34.754+00:00I had a concern with a third option. One of the c...I had a concern with a third option. One of the correct answers to this quiz was "You cannot work with NEW and OLD in the aggregate, that is, as a record. You can only reference individual fields of these pseudo-records, matching the names of the columns." While that is strictly true in the case of relational tables, you can work with :NEW and :OLD in the aggregate (though not as a record) in the case of object tables. In an object table, :NEW.object_value is a full-blown instance of the object type and can be passed around just like any other object (see example below). <br /><br />Had the question not used the phrase "as a record", I believe this answer would be unambiguously incorrect. As it stands, the "as a record" clause seems to disqualify the object table exception however the followup comment about only being able to reference individual fields makes the choice murkier. <br /><br />create type plch_part_obj is object ( <br /> part_num number, <br /> part_desc varchar2(100) ); <br />/ <br /><br />create procedure plch_print_part( <br /> p_part plch_part_obj ) <br />as <br />begin <br /> dbms_output.put_line( p_part.part_desc ); <br />end; <br /><br />create or replace trigger trg_plch_parts <br /> before insert on plch_parts <br /> for each row <br />begin <br /> plch_print_part( :new.object_value ); <br />end; <br /><br />1* insert into plch_parts values( plch_part_obj( 1, 'Tractor' ) ) <br />SQL> / <br />Tractor <br />1 row created.Justinhttps://www.blogger.com/profile/06313153782408140629noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-3326656223209165032010-11-12T02:06:28.898+00:002010-11-12T02:06:28.898+00:00Cheers for the sympathy Jeff, I was torn which way...Cheers for the sympathy Jeff, I was torn which way to click on this one because I knew not everywhere needed the colon, but like you said, I just had to second guess. Oh well! I thought there might be a bit of reaction to this one.<br /><br />I agree with your "code" comment, it's certainly not a paragraph/novel/essay/documentation... it's code :-)<br /><br />Steven's done well this week to get us thinking.Scott Wesleyhttps://www.blogger.com/profile/18106937181788036683noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-44298495845889006002010-11-11T22:25:39.119+00:002010-11-11T22:25:39.119+00:00I don't agree with the idea that the term &quo...I don't agree with the idea that the term "code" should be limited only to the executable block of a trigger definition.<br /><br />The term "code" is very general, and refers to any sequence of characters that can be interpreted by a compiler (or interpreter). That's why we normally qualify it with terms like "procedural code" vs. "declarative code".<br /><br />I considered this option "wrong" because I consider the code for a trigger to be everything from the CREATE TRIGGER (inclusive) to the terminator character, therefore the WHEN clause is included.<br /><br />I do sympathise with those who marked it "correct" because I've been there before - second-guessing my and the quiz author's meaning of words :)Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-29682317397058672012010-11-11T19:52:05.244+00:002010-11-11T19:52:05.244+00:00I fully agree to william-robertson. Also a little ...I fully agree to william-robertson. Also a little bit above that in the documentation is written "Specify the trigger condition, which is a SQL condition that must be satisfied for the database to fire the trigger" and in the restrictions is written "You cannot invoke PL/SQL functions or methods in the trigger condition". And this is a "PL/SQL" challange.Niels Heckerhttps://www.blogger.com/profile/01416262288792133003noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-20270038572009891582010-11-11T19:27:24.234+00:002010-11-11T19:27:24.234+00:00jhall62, I could say "12% of the luck-guesser...jhall62, I could say "12% of the luck-guesser". But I gave them a credit :)<br />My point is just rephrasing. Also percentile is very strange.<br /><br />But you and others could try to say and to hear youself on<br />"the code to define the table" (I never think about create table as a CODE :) )<br />"the code to define the package body"<br />"the code to alter the table"<br />and so on ...<br /><br />Unusually? For me - yes. I've read somewhere in the Oracle docs that all of they are usually called SQL statements; but sometimes documentation lies :)Vitaliy Lyanchevskiyhttps://www.blogger.com/profile/03394959689295703518noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-91770367857972363722010-11-11T18:39:38.520+00:002010-11-11T18:39:38.520+00:00Well, the SQL "CREATE TRIGGER" statement...Well, the SQL "CREATE TRIGGER" statement and the PL/SQL code block are two separate things, which is why you don't need colons in the WHEN clause. I suppose it's all code in the end, but what with this being the PL/SQL Challenge I assumed you meant the trigger code proper. The documentation hints at this in passing, kind of, when it says "The call_procedure_statement lets you call a stored procedure rather than specifying the trigger code inline as a PL/SQL block", not to mention the fact that "CREATE TRIGGER" doesn't appear in the PL/SQL Reference.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-86096680210718042942010-11-11T15:56:51.938+00:002010-11-11T15:56:51.938+00:00Hi Steven,
as for the "standalone" argu...Hi Steven,<br /><br />as for the "standalone" argument would hold if both INSERT and DELETE choices did not include "row-level". Otherwise the difference in wording strongly hints on a difference in an interpretation. From my point of view this choice should be voided.<br /><br />Kind regards,<br />Oleksandral0https://www.blogger.com/profile/15743792964167204705noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-36294088734517916012010-11-11T15:34:43.000+00:002010-11-11T15:34:43.000+00:00Vitaliy, "12% got it right" is not the s...Vitaliy, "12% got it right" is not the same as "12% of same-thinkers." Some portion of the 88% that got it wrong may not have known the syntax required when referencing columns in the WHEN clause or may have responded quickly without considering all of the places where NEW and OLD references might appear. I nearly missed this one because I rarely use the WHEN clause with triggers and thus didn't immediately consider its syntax.<br /><br />I agree with Steven that "the code to define a row-level trigger" definitely includes every statement required to define the trigger. One cannot define a trigger without the CREATE TRIGGER statement and each subordinate clause contributes to the trigger's definition. While it is not uncommon use "trigger's code" to reference just the imperative portions in the context of execution, the context in this case was explicitly declared to be definition.jhall62https://www.blogger.com/profile/10339038131928463003noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-45814330634275121622010-11-11T12:41:56.174+00:002010-11-11T12:41:56.174+00:00> "I think that "the code to define a...> "I think that "the code to define a trigger" refers reasonably to all the statements that you write to define the trigger."<br />> "I may consider making some changes to the quiz text"<br />But only 12% of same-thinkers show that at least rephrasing is REQUIRED.<br /><br />"The trigger definition" means what you mean.Vitaliy Lyanchevskiyhttps://www.blogger.com/profile/03394959689295703518noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-74020333200725792662010-11-11T12:20:53.147+00:002010-11-11T12:20:53.147+00:00You are right, Steven.
I was mixing up the WHEN cl...You are right, Steven.<br />I was mixing up the WHEN clause and the REFERENCING clause. The WHEN clause does reference column values.<br /><br />Regards,<br />Rob.Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.com