6 Replies Latest reply on Sep 14, 2020 12:14 PM by Tim St. H.

    Serveroutput bug got worse in 20.2

    Erik van Roon

      I've been nagging about this bug for years.

      In version 20.2 instead of a fix I found it actually got worse.

      To the point where I will go back to 19.4 because the output of lots of my scripts is rubbish in 20.2.

      I would really like it if this bug gets fixed really soon.

      Script and output can be found below the summary.

       

      Summary of the bug (See also script and output below)

      Both line (--) and block (/*  */)  comments in scripts cause extra empty lines to be output to screen (and of course to file if spooling).

      This used to happen only when using "format wrapped" for "set serveroutput".

      But now it happens whichever format I choose, though "format wrapped" produces even more extra empty lines

       

      First appearance of the bug

      There was a bug in the 4.* versions that made comments in scripts cause extra empty line output to screen

      This happened only when using "format wrapped" on "set serveroutput on"

      See: Re: Comments in scripts cause empty line output

      Turloch O'Tierney reported it fixed here: Re: Comments in scripts cause empty line output

      Saying: "Bug fixed in code in development - confusion between  \r\n windows newlines and \n UNIX/Linux newlines. (Or more specially \n (no \r)  in a piece of text on windows)."

      And indeed the problem was gone in the 17.2 version.

       

      Re-appearance of the bug

      In 18.2 suddenly the problem was back.

      Obviously, I don't know if it's the same bug, but it's the exact same behavior.

      See: Old issue back: comments cause empty line output

      So far no fix for this yet.

      I see this behavior both on windows and on linux.

       

      Now it got a lot worse

      With the release of 20.2 the problem exists not only when using "format wrapped" but with *any* format of "set serveroutput".

      This means that even my workaround of using "format truncated" (which I *don't* want to use) is not a workaround anymore.

       

      Please fix this, because currently I have only three options:

      1. stay on 19.4 (will not be an option for years)

      2. Remove all comments from all my scripts (not a valid option)

      3. Return to using sql*plus (not something that makes me happy)

       

      Script test_sqlcl.sql

      prompt this is the first of three lines that should actualy be put on screen
      -----------------------------------------------------------------------
      --
      --      _/_/_/_/              _/_/_/      _/_/      _/_/_/    _/_/_/
      --      _/        _/      _/  _/    _/  _/    _/  _/        _/
      --    _/_/_/    _/      _/  _/_/_/    _/    _/  _/          _/_/
      --    _/          _/  _/    _/    _/  _/    _/  _/              _/
      --  _/_/_/_/      _/      _/    _/    _/_/      _/_/_/  _/_/_/
      --
      -----------------------------------------------------------------------
      
      prompt this is the second of three lines that should actualy be put on screen
      
      /*
      -----------------------------------------------------------------------
      --
      --      _/_/_/_/              _/_/_/      _/_/      _/_/_/    _/_/_/
      --      _/        _/      _/  _/    _/  _/    _/  _/        _/
      --    _/_/_/    _/      _/  _/_/_/    _/    _/  _/          _/_/
      --    _/          _/  _/    _/    _/  _/    _/  _/              _/
      --  _/_/_/_/      _/      _/    _/    _/_/      _/_/_/  _/_/_/
      --
      -----------------------------------------------------------------------
      */
      
      prompt this is the third and last line that should actualy be put on screen
      

       

      Output in SQL*PLUS 19.3 (as expected)

      ERO@EVROCS>set serveroutput on format truncated
      ERO@EVROCS>@test_sqlcl.sql
      this is the first of three lines that should actualy be put on screen
      this is the second of three lines that should actualy be put on screen
      this is the third and last line that should actualy be put on screen
      ERO@EVROCS>set serveroutput on format word_wrapped
      ERO@EVROCS>@test_sqlcl.sql
      this is the first of three lines that should actualy be put on screen
      this is the second of three lines that should actualy be put on screen
      this is the third and last line that should actualy be put on screen
      ERO@EVROCS>set serveroutput on format wrapped
      ERO@EVROCS>@test_sqlcl.sql
      this is the first of three lines that should actualy be put on screen
      this is the second of three lines that should actualy be put on screen
      this is the third and last line that should actualy be put on screen
      ERO@EVROCS>
      
      

       

      Output in SQLcl 19.4 (extra lines only when using format wrapped)

      ERO@EVROCS>set serveroutput on format truncated
      ERO@EVROCS>@test_sqlcl.sql
      this is the first of three lines that should actualy be put on screen
      this is the second of three lines that should actualy be put on screen
      this is the third and last line that should actualy be put on screen
      ERO@EVROCS>set serveroutput on format word_wrapped
      ERO@EVROCS>@test_sqlcl.sql
      this is the first of three lines that should actualy be put on screen
      this is the second of three lines that should actualy be put on screen
      this is the third and last line that should actualy be put on screen
      ERO@EVROCS>set serveroutput on format wrapped
      ERO@EVROCS>@test_sqlcl.sql
      this is the first of three lines that should actualy be put on screen
      
      
      
      
      
      
      
      
      
      
      this is the second of three lines that should actualy be put on screen
      
      
      this is the third and last line that should actualy be put on screen
      
      
      
      ERO@EVROCS>
      

       

      Output in SQLcl 20.2 (extra lines using any format)

      ERO@EVROCS>set serveroutput on format truncated
      
      
      ERO@EVROCS>@test_sqlcl.sql
      
      
      this is the first of three lines that should actualy be put on screen
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      this is the second of three lines that should actualy be put on screen
      
      
      this is the third and last line that should actualy be put on screen
      
      
      ERO@EVROCS>set serveroutput on format word_wrapped
      
      
      ERO@EVROCS>@test_sqlcl.sql
      
      
      this is the first of three lines that should actualy be put on screen
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      this is the second of three lines that should actualy be put on screen
      
      
      this is the third and last line that should actualy be put on screen
      
      
      ERO@EVROCS>set serveroutput on format wrapped
      
      
      ERO@EVROCS>@test_sqlcl.sql
      
      
      this is the first of three lines that should actualy be put on screen
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      this is the second of three lines that should actualy be put on screen
      
      
      
      
      this is the third and last line that should actualy be put on screen
      
      
      
      
      
      ERO@EVROCS>
      
        • 1. Re: Serveroutput bug got worse in 20.2
          Glen Conway

          There was another recent report of extra lines in SQLcl, but unrelated to comments:  SQLCL: extra link breaks/blank lines comparing with SQL*PLUS

           

          thatJeffSmith-Oracle logged a bug for it, but always remember the release notes specifically distinguish between official Support (MOS) and mere community Feedback.

           

          Note: it was posted in the SQL Developer space rather than the SQLcl subspace, which would probably get your questions more exposure in the community than the SQLcl subspace.

          • 2. Re: Serveroutput bug got worse in 20.2
            Erik van Roon

            Thanks Glen.

             

            Yes, the always problematic requirement to have a non-read only MOS account to be able to report bugs.

            Being a self-employed one-man army, I don't have an exadata in the basement hence no MOS account of my own. Clients may give me (read-only) access to their MOS, but for reporting a bug I need a client's DBA who is willing to file a bug for something that might not be such a big deal to him/her/the company.

            So, "mere community Feedback" is pretty much the only option I have to report such problems with Oracle software.

            Don't get me wrong, I'm not saying everyone should have support. But an easier way to just report bugs would be welcome (for any software from Oracle).

             

            Anyway,

            Do I understand correctly that you are saying that the recommended  place for sqlcl related stuff is the sql dev space, because more exposure?

            Ok. But then why is this subspace here? It seemed the most logical place for sqlcl specific stuff.

            • 3. Re: Serveroutput bug got worse in 20.2
              Glen Conway

              Do I understand correctly that you are saying that the recommended  place for sqlcl related stuff is the sql dev space, because more exposure?

              Ok. But then why is this subspace here? It seemed the most logical place for sqlcl specific stuff.

              It seems you are correct. I "misread" the SQLcl 20.2 release notes: they say Feedback should go to the "SQL Developer forum", but mousing over the hyperlink, it points to the SQLcl subspace.

              Capture.JPG

              Oh well.  In practice, though, many SQLcl questions get posted directly in the SQL Developer space. Maybe those posters are making my same mistake!

              1 person found this helpful
              • 4. Re: Serveroutput bug got worse in 20.2
                Erik van Roon

                 

                Then I better start monitoring the sql dev space more too

                 

                • 5. Re: Serveroutput bug got worse in 20.2
                  peter_raganitsch

                  This new behavior is definitely an unwelcome change. We also experience the script output (prompt/spool) is way slower than with our previous used version (18.2).

                  • 6. Re: Serveroutput bug got worse in 20.2
                    Tim St. H.

                    Thank you for the detailed example.  It is good to know that this is visible by others and not an instance setting.