tag:blogger.com,1999:blog-8677649049588007585.post5136002427077911143..comments2023-06-18T16:15:22.432+01:00Comments on PL/SQL Challenge: Every block will fail with an error? Not so, say players. (1764)Steven Feuersteinhttp://www.blogger.com/profile/16619706770920320550noreply@blogger.comBlogger14125tag:blogger.com,1999:blog-8677649049588007585.post-36827944361812411352010-12-11T01:06:09.029+00:002010-12-11T01:06:09.029+00:00Just a quick reminder to keep this thread focused:...Just a quick reminder to keep this thread focused: the choice stated "Any block of code that includes a call to salespkg.calc_total will result in Oracle raising an error when executed."<br /><br />The issue is not if an exception would escape unhandled from the block, only that Oracle will raise an error. <br /><br />So demonstrating that you can trap an exception raised in calling the program is irrelevant to the quiz.<br /><br />The only problem I see with this choice is in the phrase "that includes a call". As Iudith suggests, I should have more clearly said "that executes". <br /><br />It has been demonstrated that you can "include" a call in the block, not execute it, and then Oracle doesn't complain.<br /><br />Several players have argued that code executed as a dynamic PL/SQL block from another, static block is not executed in that static block.<br /><br />I do not plan to draw that distinction (as one player pointed out, that would be a contradiction with a recent quiz). Yes, that dynamic block executes at the schema level, in terms of resolving references to objects within the dynamic block. But generally I will consider any code written and executed in the block to be a part of that block.<br /><br />Given the ambiguity of "includes a call", I will rescore for two players. I don't think this will be a disservice to other players, though I think we can all agree that it is a finely-drawn distinction.<br /><br />Well, there's nothing new with that in the PL/SQL Challenge. We've been doing a lot of very fine distincting.<br /><br />Have a fine weekend, everyone!<br />SFSteven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-6923278807441495672010-12-11T00:19:41.833+00:002010-12-11T00:19:41.833+00:00And totally without execute immediate:
create dat...And totally without execute immediate:<br /><br />create database link selflink<br />connect to *** identified by ***<br />using ***;<br /><br />CREATE OR REPLACE PACKAGE salespkg<br />IS<br /> /*PROCEDURE calc_total (reg_in IN CHAR);*/<br /> PROCEDURE calc_total (reg_in IN VARCHAR2);<br />END salespkg;<br />/<br /><br />CREATE OR REPLACE PACKAGE BODY salespkg<br />IS<br /> /*PROCEDURE calc_total (reg_in IN CHAR) IS <br /> BEGIN DBMS_OUTPUT.put_line ('Region :' || reg_in); END;*/<br /> PROCEDURE calc_total (reg_in IN VARCHAR2) IS<br /> BEGIN DBMS_OUTPUT.put_line ('Region :' || reg_in); END;<br />END salespkg;<br />/<br /><br />create or replace procedure caller_for_calc_total is<br />begin<br /> salespkg.calc_total@selflink('A');<br />exception<br /> when others then<br /> dbms_output.put_line('Hello from handler! Not rised '||SQLCODE);<br />end;<br />/<br /><br />CREATE OR REPLACE PACKAGE salespkg<br />IS<br /> PROCEDURE calc_total (reg_in IN CHAR);<br /> PROCEDURE calc_total (reg_in IN VARCHAR2);<br />END salespkg;<br />/<br /><br />CREATE OR REPLACE PACKAGE BODY salespkg<br />IS<br /> PROCEDURE calc_total (reg_in IN CHAR) IS <br /> BEGIN DBMS_OUTPUT.put_line ('Region :' || reg_in); END;<br /> PROCEDURE calc_total (reg_in IN VARCHAR2) IS<br /> BEGIN DBMS_OUTPUT.put_line ('Region :' || reg_in); END;<br />END salespkg;<br />/<br /><br />begin<br /> caller_for_calc_total;<br />end;<br />/<br /><br />After that we can see<br /><br />Hello from handler! Not rised -4062 <br />PL/SQL procedure successfully completed_Nikotinnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-7616809275897608632010-12-10T22:32:34.048+00:002010-12-10T22:32:34.048+00:00A player who was unable to post asked me to offer ...A player who was unable to post asked me to offer the following thoughts:<br /><br />At the end of the day, the scoring/rescoring is not important, but this discussion is certainly leading to some interesting comments.<br /><br />-- From 12/08/2010 quiz, marked as a correct statement. <br />"You can increase the maximum size of a varray type from within a PL/SQL block."<br /><br />-- Verification code included with question answers.<br />/* Change the limit inside a PL/SQL block*/<br /><br />BEGIN<br /> EXECUTE IMMEDIATE 'ALTER TYPE names_vat MODIFY LIMIT 200 INVALIDATE';<br />END;<br />/<br /><br />-- From 12/09/2010 quiz, marked as a correct statement, but up for debate on this blog entry<br />"Any block of code that includes a call to salespkg.calc_total will result in Oracle raising an error when executed."<br /><br />-- code example from this 12/09/2010 question blog entry<br />BEGIN<br /> IF 1 = 2<br /> THEN<br /> EXECUTE IMMEDIATE 'call salespkg.calc_total(''A'')';<br /> END IF;<br />END;<br /><br />Based on the current scoring of these two answers, I would argue that the following contradictions exist.<br /><br />Based on the answer from the 12/08/2010 quiz, operations included within a Execute Immediate statement are considered to be within a PL/SQL block. Hence, it is possible to modify the limit of a type within the PL/SQL block and this answer is marked correct. Following from this, in the 12/09/2010 question, the call to salespkg.calc_total within the Execute Immediate statement would be considered to be within the PL/SQL block. (I would suggest that this does not depend on whether the statement is executed at runtime) It is possible to run this block successfully without encountering an error, meaning that error will not always be encountered.<br /><br />Alternatively, suggesting that the call to salespkg.cacl_total is not a part of the block would suggest two options. option 1: Since the call was not executed due to the 'if 1 = 2', inclusion in a block is dependent on whether the line is actually executed. I would hope that we can agree that this is not the case, as any code with if..then...else would indicate that the portion not executed is not included in the block. or option 2: Statements executed within Execute Immediate are not part of a block. This contradicts the 12/08/2010 quiz answer although I don't think anyone would argue the 12/08/2010 answer was incorrect. Either way, both result in contradictory results, showing inconsistent definitions/scoring.<br /><br />Thus, the code example presented in the blog entry should provide the example to mark this answer incorrect, resulting in rescoring.<br /><br /><br />The only other reasoning I can come up with is that "...includes a call..." means something along the line of "is executed as a part of the block" rather than the code just being present in the block. That would lead to a wording interpretation, a discussion I won't enter into and would accept the scoring as is.Steven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-68172455214302665992010-12-10T22:29:39.717+00:002010-12-10T22:29:39.717+00:00I'm surprised that, if "if 1=2" shou...I'm surprised that, if "if 1=2" should be considered a "hole", no one suggested:<br /><br />begin<br /> begin<br /> EXECUTE IMMEDIATE 'call salespkg.calc_total(''A'')';<br /> exception<br /> when others then null ;<br /> end ;<br />end;<br />/<br /><br />would "not raise an exception" because it does not allow the exception to escape the block.<br /><br />Hopefully, if I were to seriously raise such an issue, I would NOT get a re-score...Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-66252606413554168942010-12-10T22:26:35.364+00:002010-12-10T22:26:35.364+00:00One more interpretation for choice solution :)
SQ...One more interpretation for choice solution :)<br /><br />SQL> begin<br /> 2 execute immediate 'call salespkg.calc_total(''A'')';<br /> 3 exception<br /> 4 when others then<br /> 5 dbms_output.put_line('Procedure salespkg.calc_total was called, but no error raised from block');<br /> 6 end;<br /> 7 /<br /> <br />Procedure salespkg.calc_total was called, but no error raised from block<br /> <br />PL/SQL procedure successfully completed_Nikotinnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-42149417395216453022010-12-10T21:56:29.808+00:002010-12-10T21:56:29.808+00:00Some comments on above.
Steven Feuerstein said:
...Some comments on above.<br /><br />Steven Feuerstein said:<br /><br /> > I said "includes a call" but I never said that the block had to actually execute the subprogram. <br /> <br /> >well, or at least giving me enough backbone to say: <br /> >"No, you should have understood that I meant you are calling the subprogram."<br /> <br />Yes, it's obvious that we can't actually execute the subprogram without pls-307 error in the error stack, <br />so I suppose that it doesn't happen in the PLSQL block.<br /><br />jhall62 said:<br /><br /> >the executable code still does not contain a call to salespkg.calc_total. <br /> >Because the expression "1 = 2" always evaluates to FALSE, <br /> >the code within the IF is unreachable and thus the call to the offending <br /> >procedure will never be attempted.<br /> <br />Ok let's consider not so simple condition for IF. We can use reference to<br />other code like package or dbms_random. So it can depend on stars placement<br />if we will get exception or not. The choice states "when executed"; however, <br />I would rather argue that it happens in every execution.<br /><br />jhall62 said:<br /><br /> >Although the original phrasing did not explicitly state that the call had to be executed,<br /> >the requirement should have been understood by anyone as implicit.<br /> <br />If we suppose that the call has to be executed we will get pls-307._Nikotinnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-16192099045270194322010-12-10T21:19:23.333+00:002010-12-10T21:19:23.333+00:00Iudith, I agree that it is strange that Oracle per...Iudith, I agree that it is strange that Oracle permits such a package to be compiled when there is no possible way to call any of the overloaded procedures. It seems to me that trapping ambiguous overloading could be caught easily (for most cases) by comparing a procedure’s type signature (reduced to the type family instead of using the specific subtype) and it’s parameter names with those of any previously declared procedures with the same name. At the very least a warning could be issued. My guess is that Oracle only checks when attempting to compile a calling program in order to be consistent and to handle less obvious instances such as the following: <br /><br />CREATE PACKAGE ambiguous_overloads IS<br />PROCEDURE overload (p1 NUMBER, p2 VARCHAR2);<br />PROCEDURE overload (p1 VARCHAR2, p2 NUMBER);<br />END ambiguous_overloads;<br />/<br />CREATE PACKAGE BODY ambiguous_overloads IS<br />PROCEDURE overload (p1 NUMBER, p2 VARCHAR2) IS<br />BEGIN<br />NULL;<br />END overload;<br />PROCEDURE overload (p1 VARCHAR2, p2 NUMBER)<br />BEGIN<br />NULL;<br />END overload;<br />END ambiguous_overloads;<br />/<br /><br />The <i>overload</i> procedures have different signatures, although the parameter names match. One can successfully call overload(1,'1') or overload('1',1); however, if one attempts to call overload(1,1) or overload('1','1') , then Oracle will raise the PLS-00307 exception. It is reported as a compilation error because that is what it is. When attempting to execute an anonymous block outside of a stored procedure (e.g., from command line SQL*Plus or an <i>EXECUTE IMMEDIATE</i> statement), the failure is not occurring at runtime but when the code is being compiled/interpreted in preparation for execution.<br /><br />_Nikotin’s reference to the use of native dynamic SQL in the explanation of the December 8 quiz ignores a significant characteristic of the <i>EXECUTE IMMEDIATE</i> statement in the December 8 quiz: it will be executed. The string in _Nikotin’s example could be replaced by any string without altering the behavior of the block since it can never be referenced by any executing code.<br /><br />Gary, _Nikotin’s code doesn’t catch the exception, in fact, it doesn’t do anything except skip to the end. Even if it did catch the exception, that is not the same as Oracle not raising an exception.jhall62https://www.blogger.com/profile/10339038131928463003noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-40448862397673550432010-12-10T19:56:52.283+00:002010-12-10T19:56:52.283+00:00My interpretation of "Any block of code that ...My interpretation of "Any block of code that includes a call to salespkg.calc_total will result in Oracle raising an error when executed." was that the "when executed" and "raising an error" referred to the call rather than the block of code. <br />Nikotin's variation does allow the exception to be caught and ignored such that the block itself could be run without the block raising an exception.SydOraclehttps://www.blogger.com/profile/08828771074492585943noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-71049838823256262422010-12-10T18:50:59.172+00:002010-12-10T18:50:59.172+00:00Ok, I admit that my comment looks pedantic, but th...Ok, I admit that my comment looks pedantic, but this is because I was surprised by 08 Dec quiz with solution that uses native dynamic sql to execute ddl, and so I expected that you might have had in mind something like that. In the light of that, I would suggest that you take my point and give me the rescore :)_Nikotinnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-74687318379793074452010-12-10T18:33:26.782+00:002010-12-10T18:33:26.782+00:00Hello All,
What I rather find interesting about th...Hello All,<br />What I rather find interesting about this quiz is Oracle's behavior itself,<br />rather than the specific choice in question.<br /><br />The fact that the package spec and both can be compiled without errors looks to be more in <br />contradiction with the Oracle PL/SQL Language reference, that says the following:<br /><br />"PL/SQL lets you overload nested subprograms, package subprograms, and type<br />methods. You can use the same name for several different subprograms if their formal<br />parameters differ in name, number, order, or data type family. (A data type family is a<br />data type and its subtypes ...).<br /><br />It is not very clear what Oracle does mean by "lets you overload", does it mean <br />"lets you compile" or "lets you execute" ?<br /><br />VARCHAR2 and CHAR belong both to the "CHAR datatype family" (as shown the same documentation),<br />so I would rather expect the compilation of such a package to fail.<br /><br />Instead of this, it fails at runtime only, but due to a compilation error, PLS-00307,<br />which is rather unusual for Oracle.<br /><br />This reminds me of a previous quiz, in which there were also 2 overloads of the same program,<br />one having a single parameter and the second having two parameters, with a default value<br />for the second parameter.<br />In such a case, maybe the compilation can be accepted as successful because there can be<br />at least one successful call, namely a call that specifies both parameters.<br /><br />But in our case, no valid call is possible, therefore I still wonder how is it <br />that Oracle still allows the compilation to be successful.<br /><br />I am very curious what do you think of this aspect.<br /><br />Regarding the specific choice in discussion, maybe a less ambiguous wording would have said<br />"Any block of code that EXECUTES a call to salespkg.calc_total ..." instead of<br />"Any block of code that INCLUDES a call to salespkg.calc_total ...".<br /><br />Thanks & Best Regards,<br />Iudithiudithhttps://www.blogger.com/profile/04905902445036068357noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-59411024950120591592010-12-10T17:47:59.588+00:002010-12-10T17:47:59.588+00:00Hi!
Steve, but the ebook will be received by play...Hi!<br /><br />Steve, but the ebook will be received by player _Nikotin, will not it? At least, he made you to think about the quiz. ;)<br /><br />AlexsuPPLerhttps://www.blogger.com/profile/01186200400558858674noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-34506108378564617502010-12-10T17:09:05.660+00:002010-12-10T17:09:05.660+00:00Note to players: I stated in my original post that...Note to players: I stated in my original post that I would rescore for _Nikotin and anyone else who lays claim to this analysis (one person has already done so). I should have held off on doing so until other players had a chance to provide feedback.<br /><br />I find the Wim's and JHall62's arguments persuasive (well, or at least giving me enough backbone to say: "No, you should have understood that I meant you are calling the subprogram.").<br /><br />So...I am definitely considering NOT applying a rescore. Keep the comments coming!<br /><br />Thanks, SFSteven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-46821419417554181732010-12-10T16:59:19.334+00:002010-12-10T16:59:19.334+00:00The first objection definitely does NOT deserve an...The first objection definitely does NOT deserve any reward because the executable code does NOT contain a call to the procedure <i>salespkg.calc_total</i>. <br /><br />Wim de Lange has a valid point, the "CALL" to <i>salespkg.calc_total</i> is not in the PL/SQL block, it is in a string passed to an <i>EXECUTE IMMEDIATE</i>. Because the player made use of the SQL <i>CALL</i> statement, this string is not a PL/SQL block. Even if the string is replaced with the functionally equivalent 'BEGIN salespkg.calc_total(''A''); END;' anonymous block, the call would only exist if the <i>EXECUTE IMMEDIATE</i> statement were executed at runtime and the PL/SQL engine could successfully compile the code in the string (which it could not since the overloading is ambiguous).<br /><br />Even if we were to accept the contents of constant strings passed to <i>EXECUTE IMMEDIATE</i> as being part of the PL/SQL block (which I do not), the executable code still does not contain a call to <i>salespkg.calc_total</i>. Because the expression "1 = 2" always evaluates to <i>FALSE</i>, the code within the <i>IF</i> is unreachable and thus the call to the offending procedure will never be attempted. Although the original phrasing did not explicitly state that the call had to be executed, the requirement should have been understood by anyone as implicit. Giving any player credit for a correct choice based on the argument that an unreachable "call" to the procedure appears in the source code is a disservice to the other players.jhall62https://www.blogger.com/profile/10339038131928463003noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-69813537527481757092010-12-10T14:14:01.131+00:002010-12-10T14:14:01.131+00:00My opinion? I don't agree with the fact that t...My opinion? I don't agree with the fact that there is a call in that program. There is a call to EXECUTE IMMEDIATE that has a string. That string evaluates to a call on runtime but the program does not know that when the code compiles! That is by our interpretation that we know that it is a call. And our interpretation does not count, I assume.<br /><br />I know this is a tricky discussion. It is all about semantics on run time, compile time and in the readers mind.<br /><br />Conclusion, don't rescore (even if I have it wrong?)Wim de Langehttps://www.blogger.com/profile/05505341375827859005noreply@blogger.com