tag:blogger.com,1999:blog-8677649049588007585.post617810720934243647..comments2023-06-18T16:15:22.432+01:00Comments on PL/SQL Challenge: Compiling Incomplete Program Units (9633)Steven Feuersteinhttp://www.blogger.com/profile/16619706770920320550noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-8677649049588007585.post-58618266549604437432012-02-01T02:42:45.889+00:002012-02-01T02:42:45.889+00:00You can blame me if you didn't like this quiz ...You can blame me if you didn't like this quiz :)<br /><br />jhall62: "This quiz tested one’s knowledge of the internal quirks of the PL/SQL compiler and various methods for submitting PL/SQL for compilation."<br /><br />Maybe, but I'm not so sure about that.<br /><br />The quiz was not about different *methods* of submitting PL/SQL for compilation (it was my mistake that the assumptions did not explicitly restrict it to SQL*Plus). I never intended it to have anything to do with whether TOAD, SQL Developer, or any other tool was used.<br /><br />The quiz's purpose was to test one's knowledge of whether an object would be created depending on different *syntax* used, even if the syntax was invalid. Perhaps the case of "CREATE PROCEDURE x;" (which results in no object) is a bug in the compiler - in which case I might concede that this quiz is about internals; but I'm not sure I'd characterize this as a bug, just an "undocumented behaviour" :)<br /><br />I did mention in private correspondence with Steven that I considered this quiz to be a trivia question. I'm glad he included it anyway, because I enjoy trivia questions as well as the good educational ones - just so long as there aren't too many of them.Jeffrey Kemphttp://jeffkemponoracle.comnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-52187720446494518162012-01-31T15:43:24.066+00:002012-01-31T15:43:24.066+00:00This quiz tested one’s knowledge of the internal q...This quiz tested one’s knowledge of the internal quirks of the PL/SQL compiler and various methods for submitting PL/SQL for compilation. Although such details may be interesting, they are not a test of one’s PL/SQL knowledge. Whether syntactically flawed PL/SQL results in an invalid object or none at all is of little importance. The only guarantee is that compiling invalid PL/SQL will not result in an executable object.<br /><br />The choice of tool for submitting PL/SQL can have a significant impact on the processing of submitted PL/SQL, as can the various versions of the Oracle client libraries. A few years ago I was writing some code that utilized PL/SQL features that were introduced with the then newly released version of Oracle RDBMS. Despite using the most recent client libraries, I was unable to get the code to compile via TOAD. Submitting the file via SQL*Plus was successful. After some experimentation it was concluded that the problem was due to TOAD’s not submitting the code to the database when Toad determined that the syntax was invalid. My experience with recent versions of TOAD indicates that this approach has been abandoned by Quest.<br /><br />For most quizzes the use of SQL*Plus, SQL Developer, TOAD, or some other tool will have no significant impact on the results; however, the assumptions should specify that the most recent version (at the time of the quiz) of SQL*Plus or SQL Developer will be the standard for verification. These tools support multiple platforms and are readily available to anyone participating in the PL/SQL Challenge.jhall62https://www.blogger.com/profile/10339038131928463003noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-22098944282278376742012-01-28T18:06:39.588+00:002012-01-28T18:06:39.588+00:00I think the assumptions should state that you shou...I think the assumptions should state that you should use only SQL Plus and the standard command terminator (since I guess you can change the terminator in SQL Plus by SET command).<br /><br />I hardly use GUI tools as I am scared of losing my command line skills. And also I don't trust the tools 100%.<br /><br />In my opinion for critical tasks like production DB administration you should only use Oracle supplied tools or command line.Rameshhttps://www.blogger.com/profile/04557315341209397856noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-52766713947778525612012-01-27T17:19:23.997+00:002012-01-27T17:19:23.997+00:00I was surprised at the results. It had never occu...I was surprised at the results. It had never occurred to me to try to create procedure with only a name, so I tried it. I happened to have Toad open and, wonder of wonders, it worked. The same behavior is in Oracle's own SQL Developer too.<br /><br />To remove the tool from the equation, try this anonymous block.<br /><br />DECLARE<br /> object_does_not_exist EXCEPTION;<br /> success_with_compile_error EXCEPTION;<br /> PRAGMA EXCEPTION_INIT(object_does_not_exist, -4043);<br /> PRAGMA EXCEPTION_INIT(success_with_compile_error, -24344);<br /> <br /> v_type user_objects.object_type%TYPE;<br />BEGIN<br /> BEGIN<br /> EXECUTE IMMEDIATE 'DROP PROCEDURE plch_test';<br /> EXCEPTION<br /> WHEN object_does_not_exist<br /> THEN<br /> DBMS_OUTPUT.put_line('oops! tried to drop an object that did not exist');<br /> END;<br /><br /> BEGIN<br /> EXECUTE IMMEDIATE 'CREATE PROCEDURE plch_test';<br /> EXCEPTION<br /> WHEN success_with_compile_error<br /> THEN<br /> DBMS_OUTPUT.put_line('Created ok but is invalid');<br /> END;<br /><br /> SELECT object_type<br /> INTO v_type<br /> FROM user_objects<br /> WHERE object_name = 'PLCH_TEST';<br /><br /> DBMS_OUTPUT.put_line(v_type);<br />END;Seanhttps://www.blogger.com/profile/15790298349995376048noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-46601493655666980972012-01-27T15:32:22.846+00:002012-01-27T15:32:22.846+00:00Hello All,
I could hardly believe that a quiz lik...Hello All,<br /><br />I could hardly believe that a quiz like this one will also generate a blog thread ...<br />but here it is :)<br /><br />In fact, for the sake of consistency, I think that ANY of the development tools should have been able to create the object as soon as the object type and name are correctly specified, like in the first option.<br /><br />This would have been similar to the statement that creates an incomplete object type,<br />by specifying only <br /><br />CREATE TYPE object_type_name;<br /><br />and defers the completion of the object definition.<br /><br />We can go even further, and imagine that a procedure without a "body" could have been interpreted as having a default body composed of a single "NULL;" statement, and thus to even compile it as a VALID object ... <br />This is maybe already too much, but, definitely, I don't see a reason for which<br />the first choice to be "discriminated" and being considered "less worth" of creating an object than are the other three ones ...<br /><br />It is interesting however to know that the different IDE tools have different behaviors ...<br />Those who are addicted to SQL*PLUS (like me) probably did not even think of such a possibility.<br /><br />Thanks a lot & Best Regards,<br />Iudithiudithhttps://www.blogger.com/profile/04905902445036068357noreply@blogger.com