tag:blogger.com,1999:blog-8677649049588007585.post9077274701581344591..comments2023-06-18T16:15:22.432+01:00Comments on PL/SQL Challenge: Questions raised about when DBMS_OUTPUT text is displayed (2001)Steven Feuersteinhttp://www.blogger.com/profile/16619706770920320550noreply@blogger.comBlogger15125tag:blogger.com,1999:blog-8677649049588007585.post-73815341704444735182011-02-10T15:00:49.894+00:002011-02-10T15:00:49.894+00:00Scott,
In general, the IDE does not matter when r...Scott,<br /><br />In general, the IDE does not matter when running scripts that display output, so I don't think it does or will cast doubt on all such questions.<br /><br />I don't think it's pandering. It's reality. I have not previously insisted on using SQL*Plus (though I am now considering it) as the execution environment. Not many people use SQL*Plus, nor should they as far as I am concerned.<br /><br />I think this quiz just happened to touch on an aspect of PL/SQL that related as much to the way PL/SQL is executed in an IDE (and there is lots of leeway here) as to the behavior of DBMS_OUTPUT itself.<br /><br />SFSteven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-66360457550476071872011-02-10T00:46:01.205+00:002011-02-10T00:46:01.205+00:00While I almost agree with the re-score, I think it...While I <i>almost</i> agree with the re-score, I think it's now setting an unwelcome precedence that casts doubt in all questions that use DBMS_OUTPUT to display information.<br /><br />It almost seems like it's pandering to what should be considered as flaws in some IDEs - by not display results as per intended and documented behaviour.<br /><br />Running 3 separate blocks/programs <b>should</b> display the answer as described. Should we limit questions using WITH clause since some older IDE's can't recognise it?<br /><br />For the record, running this as a script in SQLDev3 shows results "as expected"<br /><br />My 5 cents.Scott Wesleyhttps://www.blogger.com/profile/18106937181788036683noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-48847740340153750882011-02-09T19:07:38.977+00:002011-02-09T19:07:38.977+00:00This comment has been removed by a blog administrator.iudithhttps://www.blogger.com/profile/04905902445036068357noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-71667525782974807422011-02-09T13:03:07.923+00:002011-02-09T13:03:07.923+00:00Spoon - wow, do I love the PL/SQL Challenge! Thank...Spoon - wow, do I love the PL/SQL Challenge! Thanks for pointing this out. It was definitely not my understanding. I will remove that line from the assumption.<br /><br />Ingimundur - You reinforced my feeling that for questions like this, I need to be careful about specifying a host environment, like SQL*Plus. Otherwise, I need to just stay away from this issue and others like it.<br /><br />SFSteven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-66503440934001476312011-02-09T08:20:27.782+00:002011-02-09T08:20:27.782+00:00Hi again.
DBMS_OUTPUT.PUT_LINE does not output an...Hi again.<br /><br />DBMS_OUTPUT.PUT_LINE does not output anything to the screen, unless we specifically tell it to do so, usually with SET SERVEROUTPUT ON. DBMS_OUTPUT.PUT_LINE only puts text into a buffer, which is then read by the host environment. It is then the internal code of the host evironment that calls DBMS_OUTPUT.GET_LINE and uses non database methods to display the text on the screen. This also applies to SQL*PLUS. Here is an excerpt from SQL*PLUS manual: "Setting SERVEROUTPUT OFF stops internal SQL*Plus calls to the DBMS_OUTPUT <br />package done after user SQL statements." The way SQL*PLUS does this is after each block is run, while SQL Developer, on the other hand, does this after the whole script has been run.<br />SQL*PLUS is not neccessarily the standard of how this should be done, it is just one way of doing it. There are very many people who never use SQL*PLUS. <br />Therefore, without meaning to sound rude, i must say the question itself was flawed.<br />But the good thing is that most of us learned something new about the DBMS_OUTPUT package.<br /><br />For the fun of it, try running this code.<br /><br />DECLARE<br /> vText VARCHAR2(100);<br /> vStatus INTEGER;<br />BEGIN<br /> DBMS_OUTPUT.PUT_LINE('Test 1);<br /> DBMS_OUTPUT.GET_LINE(vText, vStatus);<br />END;<br /><br />This code will not display anything, even though the SERVEROUTPUT is ON. This is due to the fact that you have already deleted the line from the buffer before the internal call from SQL*PLUS (or any other host environment).<br /><br />Regards,<br />IngimundurIngimundurhttps://www.blogger.com/profile/03109127982618084355noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-38205736703713020662011-02-09T04:04:58.509+00:002011-02-09T04:04:58.509+00:00I think only correct answer is none of the choice ...I think only correct answer is none of the choice are correct. No matter what IDE you use, nothing gets displayed - after - you run all three blocks. What gets displayed is after first block, and before 2nd and 3rd block ;-)Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-78997533364612307942011-02-09T01:43:10.476+00:002011-02-09T01:43:10.476+00:00Sorry for off-topic.
Assumption says:
/*
If an ex...Sorry for off-topic.<br /><br />Assumption says:<br />/*<br /><i>If an exception propagates unhandled from the outermost PL/SQL block to the host environment, that environment performs a rollback in the session.</i><br />*/<br /><br />I think its not necessary to have this assumption. Database implicitly does the rollback, and client does not have to do it.<br /><br /><a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:43818437682131" rel="nofollow">http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:43818437682131</a>Spoonhttps://www.blogger.com/profile/04277621011196124328noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-46954538112839056092011-02-08T22:12:09.080+00:002011-02-08T22:12:09.080+00:00First of all , I agree with al0 and iudith.
But I...First of all , I agree with al0 and iudith. <br />But I have another point add.<br />I hope I won't be too rude. <br /><br />Everyone is free to play the challenge the way they want, but my way is the following.<br />I read the code, and choose the answer based on what I think is the behaviour of the script. <br /><br />In these question, since there are three separate block, I thougth at what happened at the output after each block. Otherwise it could be one whole block!<br />So I answered for the output <br />BLOCK ONE - LINE ONE<br /><br />Isn't it the only correct choise? <br /><br />The behaviour with nothing displayed, cannot be justified by something like "TOAD works like this". It should be also justified explainig why you think it's correct.<br />Because you aren't supposed to "copy and execute" the code in some IDE to see what happens and the answer the right choise....<br /><br />And let me say that I rarely use Sql*plus for daily work; instead I usually use TOAD od SQL Developer. But I had no doubt about what would be the correct choise.....<br /><br />Regards,<br />Tiggi.Tiggihttp://it.linkedin.com/pub/daniele-tagliaferro/7/82/b50noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-19266553350896664822011-02-08T19:59:09.625+00:002011-02-08T19:59:09.625+00:00I tested the code in SQL-Developer and got the cor...I tested the code in SQL-Developer and got the correct answer, but just because I run each statement independently :-)Marcushttps://xml_spreadsheet.samplecode.oracle.comnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-60080068509515176312011-02-08T18:27:11.907+00:002011-02-08T18:27:11.907+00:00Hello All,
I completely agree with AL0 and I also ...Hello All,<br />I completely agree with AL0 and I also feel that Steven himself usually means "running in SQL*PLUS" as an understatement, and stating that<br />ALL tools are supposed to display output in the same way is just for being <br />so-called "politically correct".<br /><br />I don't want to argue with anyone that prefers <br />to work with ANY tool, and there are lots and lots of them, but, since this is a PL/SQL Challenge, I also think -- and dare say probably most of the others do --- that stating from the beginning that the code for ALL the quizes is supposed to be run in SQL*PLUS would be a more than welcome agreement.<br /><br />Regarding the display of the NULL in SQL*PLUS:<br /><br />The assumptions DO SPECIFY explicitly that<br />a SET SERVEROUTPUT ON FORMAT WRAPPED is to be used.<br />The FORMAT WRAPPED option is the one that causes the display of a NULL to appear as a blank line.<br />The same is true for FORMAT TRUNCATED.<br /><br />Instead, if you use the DEFAULT option which is<br />FORMAT WORD_WRAPPED, then the NULL does NOT appear in the output.<br /><br />Most of those who test things probably use this default, therefore they DON'T see the NULL output.<br /><br />I don't want to speak here of the numerous strange bugs encountered sometimes in different IDE tools, with completely correct code that runs without problems both in SQL*PLUS and in the database ...<br /><br />In summary, I think that for the sake of a fair<br />PL/SQL Challenge we should avoid using any dependency on any IDE tool's peculiarities<br />and avoid by any means rescoring due to such reasons that have nothing to do with PL/SQL ...<br /><br />Thanks & Best Regards,<br />Iudithiudithhttps://www.blogger.com/profile/04905902445036068357noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-5623119739716114652011-02-08T16:50:59.431+00:002011-02-08T16:50:59.431+00:00I guess it would be fair enough to specify in the ...I guess it would be fair enough to specify in the assumptions section that SQL*Plus should be used as an reference environment for outputs.<br /><br />At the end of end by opening of SR with Oracle support you have to specify in your bug is reproducible with SQL*Plus :)al0https://www.blogger.com/profile/15743792964167204705noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-69648651280543452592011-02-08T15:55:39.693+00:002011-02-08T15:55:39.693+00:00After reviewing the documentation further, I must ...After reviewing the documentation further, I must agree with Ingimundur. The rules controlling the display of the contents of the DBMS_OUTPUT buffer are determined by the host environment, not by PL/SQL.<br /><br />If I had specified that this code would be run in SQL*Plus, then there would be no ambiguity. Since I did not specify a host or execution environment, I cannot guarantee that the single line of text would display.<br /><br />I will apply the following corrections:<br /><br />1. Change the text of the question to specify that SQL*Plus is in this case used to execute the blocks.<br /><br />2. Give credit to anyone who chose "None of the answers are correct."<br /><br />This quiz helped me clarify my understanding not only of the impact of DBMS_OUTPUT.DISABLE but also of the way that output is handled by different IDEs.<br /><br />So while I hate to have to rescore a quiz, I would say that the PL/SQL Challenge has once again served its purpose of deepening my (our?) knowledge of the workings of this language.<br /><br />Cheers, SFSteven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-17353853486843545712011-02-08T15:28:11.407+00:002011-02-08T15:28:11.407+00:00I agree with Alex, the assumptions state that the ...I agree with Alex, the assumptions state that the code can be run with any host environment in which you can see the result of the block. Some of those host environment apparently do not display from the buffer until after all blocks have been run, and the way I understand the documentation, it is up to the host environment when the GET_LINE is called. Therefore I think that answering that all are incorrect is a correct answer as well.<br /><br />Regards,<br />IngimundurIngimundurhttps://www.blogger.com/profile/03109127982618084355noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-62528463113879486322011-02-08T14:52:16.079+00:002011-02-08T14:52:16.079+00:00Just for background info on how different client t...Just for background info on how different client tools react on DBMS_OUTPUT: I recently almost thought I found a bug in DBMS_OUTPUT, because I also use TOAD a lot and noticed that I saw output I did not sent to dbms_output. Until I realized that it was a call to DBMS_output.put_line with NULL after calling it with a non-NULL value: at that time the previous value simply was repeated. When I wrote a testscript to show the behavior, I ran it in SQL*Plus and there the issue did not show at all! Then I realized that TOAD seems to work differently with the output of DBMS_OUTPUT than SQL*Plus...and now I see that even within TOAD (this is 9.5 by the way) there is a difference: running as a script shows different output than running as a single statement. But the script output is also not the same behavior as SQL*Plus:<br /><br />SET serverout on<br /><br />BEGIN<br /> DBMS_OUTPUT.put_line ('1');<br /> DBMS_OUTPUT.put_line (NULL);<br /> DBMS_OUTPUT.put_line ('2');<br />END;<br />/<br /><br />* Enable DBMS_OUTPUT in the Editor and run the PL/SQL block using in TOAD. Notice the "repeat" of 1 because of the call with NULL<br /><br />1<br />1<br />2<br /><br />* Run the two statements as a script in TOAD, Notice the carriage return because of the call with NULL:<br /><br />1<br /><br />2<br /><br />* And finally, run in SQL*Plus, now the call with does not show at all...:<br /><br />SQL*Plus: Release 10.2.0.3.0 - Production on Tue Feb 8 15:40:05 2011<br /><br />Copyright (c) 1982, 2006, Oracle. All Rights Reserved.<br /><br /><br />Connected to:<br />Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production<br />With the Partitioning, OLAP and Data Mining options<br /><br />1<br />2<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL><br /><br />Great right? These things make it even more difficult to play a quiz like PL/SQL Challenge. I happen to get this question correct, but I am lucky enough to have discovered some differences in how DBMS_OUTPUT output behaves...Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-13390603817875269202011-02-08T14:41:31.153+00:002011-02-08T14:41:31.153+00:00The one issue I would have with your explanation h...The one issue I would have with your explanation here is that the "Assumptions" section says the following:<br />-----------------------------<br />All code (PL/SQL blocks, DDL statements, SQL statements, etc.) is run in a host environment in which you can see the results of block and SQL statement execution directly, such as SQL*Plus, Toad, SQL Navigator, SQL Developer, PL/SQL Developer. If an exception propagates unhandled from the outermost PL/SQL block to the host environment, that environment performs a rollback in the session.<br />----------------------<br /><br />Based on that we might expect the answers to be the same, regardless of whether it is run in "native" SQL*Plus, or whether it is run in TOAD, SQLDeveloper, or whatever.<br /><br />In this particular quiz, the results appear to only be as expected if SQL*Plus is used, not if any of the other environments specified are used.Alex Heneyhttps://www.blogger.com/profile/16613299088274425352noreply@blogger.com