08 February 2011

Questions raised about when DBMS_OUTPUT text is displayed (2001)

The 7 February quiz tested your knowledge of calling DBMS_OUTPUT.disable to disable output to the DBMS_OUTPUT buffer. We showed you a series of three top-level anonymous blocks and asked you what would be displayed after they are run:
BEGIN
   DBMS_OUTPUT.put_line ('BLOCK ONE - LINE ONE');
END;
/

BEGIN
   DBMS_OUTPUT.put_line ('BLOCK TWO - LINE ONE');
   DBMS_OUTPUT.disable;
   DBMS_OUTPUT.put_line ('BLOCK TWO - LINE THREE');
END;
/

BEGIN
   DBMS_OUTPUT.put_line ('BLOCK THREE - LINE ONE');
END;
/
Several players questioned our scoring of "BLOCK ONE - LINE ONE" as correct. Here are three comments that reflect the issues raised:

"I do admit that I don't use disable or enable at all, so I may be wrong in my comment here. I understood the manual that everything is flushed, even what has been run earlier. Better yet, I tested the code both in SQL Developer and Toad after answering, and in both systems, I did not get any result at all. Remarking out the disable command, I got of course all the lines. Hence, I put to you that answering that all are wrong is actually correct."

"Reading the answer, it was only then that I realised you actually meant to run block 1 first, then run block 2, then run block 3. I am so used to using TOAD, and just running several blocks one after another by using "run as script", that I assumed that was the way to do it, and that would have resulted in none of the choices being correct, as the buffer would be cleared before being displayed at all. It might be helpful to indicate in some way that these are separate statements, not to be run as a single script."

"Nothing gets displayed "after I run" three blocks in questions, first line gets displayed after first block, NOT after 3 blocks. If the intention was to take first block's output display as correct answer, then question could have been clearer by saying "What will be displayed when I run each of the following blocks in the following order?"

First, let's address the issue of order of statements and "after I run." I don't believe that I need to state in my question that the three (or N) blocks shown in the question are run "in the following order." That has always been implied in any of the questions played in the PL/SQL Challenge. Otherwise, you could argue, say, that my table has no data because the block with insert statements was run before the create table statement.

As for the IDE issue - it may well be the case that various IDEs deal with server output from DBMS_OUTPUT calls differently. And I am sorry for the confusion that this may cause. But I do not believe that I can be responsible for accounting for differences among IDE behavior or testing my quizzes across all IDEs. The quiz, so far as I know, correctly demonstrates the impact of a call to DBMS_OUTPUT.disable on the output from the current block and all subsequent blocks (until serveroutput is re-enabled, that is). It also shows (at least if you run it in SQL*Plus, which you see below) that the output of any successfully completed blocks is displayed on the screen.
SQL: BEGIN
  2     DBMS_OUTPUT.put_line ('BLOCK ONE - LINE ONE');
  3  END;
  4  /
BLOCK ONE - LINE ONE
SQL:
SQL: BEGIN
  2     DBMS_OUTPUT.put_line ('BLOCK TWO - LINE ONE');
  3     DBMS_OUTPUT.disable;
  4     DBMS_OUTPUT.put_line ('BLOCK TWO - LINE THREE');
  5  END;
  6  /
SQL:
SQL: BEGIN
  2     DBMS_OUTPUT.put_line ('BLOCK THREE - LINE ONE');
  3  END;
  4  /
SQL:

15 comments:

  1. The one issue I would have with your explanation here is that the "Assumptions" section says the following:
    -----------------------------
    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.
    ----------------------

    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.

    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.

    ReplyDelete
  2. 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:

    SET serverout on

    BEGIN
    DBMS_OUTPUT.put_line ('1');
    DBMS_OUTPUT.put_line (NULL);
    DBMS_OUTPUT.put_line ('2');
    END;
    /

    * 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

    1
    1
    2

    * Run the two statements as a script in TOAD, Notice the carriage return because of the call with NULL:

    1

    2

    * And finally, run in SQL*Plus, now the call with does not show at all...:

    SQL*Plus: Release 10.2.0.3.0 - Production on Tue Feb 8 15:40:05 2011

    Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options

    1
    2

    PL/SQL procedure successfully completed.

    SQL>

    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...

    ReplyDelete
  3. 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.

    Regards,
    Ingimundur

    ReplyDelete
  4. 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.

    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.

    I will apply the following corrections:

    1. Change the text of the question to specify that SQL*Plus is in this case used to execute the blocks.

    2. Give credit to anyone who chose "None of the answers are correct."

    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.

    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.

    Cheers, SF

    ReplyDelete
  5. 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.

    At the end of end by opening of SR with Oracle support you have to specify in your bug is reproducible with SQL*Plus :)

    ReplyDelete
  6. Hello All,
    I completely agree with AL0 and I also feel that Steven himself usually means "running in SQL*PLUS" as an understatement, and stating that
    ALL tools are supposed to display output in the same way is just for being
    so-called "politically correct".

    I don't want to argue with anyone that prefers
    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.

    Regarding the display of the NULL in SQL*PLUS:

    The assumptions DO SPECIFY explicitly that
    a SET SERVEROUTPUT ON FORMAT WRAPPED is to be used.
    The FORMAT WRAPPED option is the one that causes the display of a NULL to appear as a blank line.
    The same is true for FORMAT TRUNCATED.

    Instead, if you use the DEFAULT option which is
    FORMAT WORD_WRAPPED, then the NULL does NOT appear in the output.

    Most of those who test things probably use this default, therefore they DON'T see the NULL output.

    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 ...

    In summary, I think that for the sake of a fair
    PL/SQL Challenge we should avoid using any dependency on any IDE tool's peculiarities
    and avoid by any means rescoring due to such reasons that have nothing to do with PL/SQL ...

    Thanks & Best Regards,
    Iudith

    ReplyDelete
  7. I tested the code in SQL-Developer and got the correct answer, but just because I run each statement independently :-)

    ReplyDelete
  8. First of all , I agree with al0 and iudith.
    But I have another point add.
    I hope I won't be too rude.

    Everyone is free to play the challenge the way they want, but my way is the following.
    I read the code, and choose the answer based on what I think is the behaviour of the script.

    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!
    So I answered for the output
    BLOCK ONE - LINE ONE

    Isn't it the only correct choise?

    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.
    Because you aren't supposed to "copy and execute" the code in some IDE to see what happens and the answer the right choise....

    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.....

    Regards,
    Tiggi.

    ReplyDelete
  9. Sorry for off-topic.

    Assumption says:
    /*
    If an exception propagates unhandled from the outermost PL/SQL block to the host environment, that environment performs a rollback in the session.
    */

    I think its not necessary to have this assumption. Database implicitly does the rollback, and client does not have to do it.

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:43818437682131

    ReplyDelete
  10. 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 ;-)

    ReplyDelete
  11. Hi again.

    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
    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.
    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.
    Therefore, without meaning to sound rude, i must say the question itself was flawed.
    But the good thing is that most of us learned something new about the DBMS_OUTPUT package.

    For the fun of it, try running this code.

    DECLARE
    vText VARCHAR2(100);
    vStatus INTEGER;
    BEGIN
    DBMS_OUTPUT.PUT_LINE('Test 1);
    DBMS_OUTPUT.GET_LINE(vText, vStatus);
    END;

    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).

    Regards,
    Ingimundur

    ReplyDelete
  12. 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.

    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.

    SF

    ReplyDelete
  13. This comment has been removed by a blog administrator.

    ReplyDelete
  14. While I almost 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.

    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.

    Running 3 separate blocks/programs should display the answer as described. Should we limit questions using WITH clause since some older IDE's can't recognise it?

    For the record, running this as a script in SQLDev3 shows results "as expected"

    My 5 cents.

    ReplyDelete
  15. Scott,

    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.

    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.

    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.

    SF

    ReplyDelete