Forum Stats

  • 3,874,424 Users
  • 2,266,733 Discussions
  • 7,911,843 Comments

Discussions

Serveroutput bug got worse in 20.2

Erik van Roon
Erik van Roon Member Posts: 60 Blue Ribbon
edited Jun 20, 2021 3:21PM in SQLcl

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:

Turloch O'Tierney reported it fixed here:

Saying: "Bug fixed in code in development - confusion between  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:

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

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

Output in SQL*PLUS 19.3 (as expected)

<p>[email protected]>set serveroutput on format truncated</p><p>[email protected]>@test_sqlcl.sql</p><p>this is the first of three lines that should actualy be put on screen</p><p>this is the second of three lines that should actualy be put on screen</p><p>this is the third and last line that should actualy be put on screen</p><p>[email protected]>set serveroutput on format word_wrapped</p><p>[email protected]>@test_sqlcl.sql</p><p>this is the first of three lines that should actualy be put on screen</p><p>this is the second of three lines that should actualy be put on screen</p><p>this is the third and last line that should actualy be put on screen</p><p>[email protected]>set serveroutput on format wrapped</p><p>[email protected]>@test_sqlcl.sql</p><p>this is the first of three lines that should actualy be put on screen</p><p>this is the second of three lines that should actualy be put on screen</p><p>this is the third and last line that should actualy be put on screen</p><p>[email protected]></p>

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

<p>[email protected]>set serveroutput on format truncated</p><p>[email protected]>@test_sqlcl.sql</p><p>this is the first of three lines that should actualy be put on screen</p><p>this is the second of three lines that should actualy be put on screen</p><p>this is the third and last line that should actualy be put on screen</p><p>[email protected]>set serveroutput on format word_wrapped</p><p>[email protected]>@test_sqlcl.sql</p><p>this is the first of three lines that should actualy be put on screen</p><p>this is the second of three lines that should actualy be put on screen</p><p>this is the third and last line that should actualy be put on screen</p><p>[email protected]>set serveroutput on format wrapped</p><p>[email protected]>@test_sqlcl.sql</p><p>this is the first of three lines that should actualy be put on screen</p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p>this is the second of three lines that should actualy be put on screen</p><p></p><p></p><p>this is the third and last line that should actualy be put on screen</p><p></p><p></p><p></p><p>[email protected]></p>

Output in SQLcl 20.2 (extra lines using any format)

<p>[email protected]>set serveroutput on format truncated</p><p></p><p></p><p>[email protected]>@test_sqlcl.sql</p><p></p><p></p><p>this is the first of three lines that should actualy be put on screen</p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p>this is the second of three lines that should actualy be put on screen</p><p></p><p></p><p>this is the third and last line that should actualy be put on screen</p><p></p><p></p><p>[email protected]>set serveroutput on format word_wrapped</p><p></p><p></p><p>[email protected]>@test_sqlcl.sql</p><p></p><p></p><p>this is the first of three lines that should actualy be put on screen</p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p>this is the second of three lines that should actualy be put on screen</p><p></p><p></p><p>this is the third and last line that should actualy be put on screen</p><p></p><p></p><p>[email protected]>set serveroutput on format wrapped</p><p></p><p></p><p>[email protected]>@test_sqlcl.sql</p><p></p><p></p><p>this is the first of three lines that should actualy be put on screen</p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p>this is the second of three lines that should actualy be put on screen</p><p></p><p></p><p></p><p></p><p>this is the third and last line that should actualy be put on screen</p><p></p><p></p><p></p><p></p><p></p><p>[email protected]></p>

Erik van Roonpeter_raganitschberxTim St. H.OverlordBR

Best Answer

Answers

  • Glen Conway
    Glen Conway Member Posts: 859 Gold Badge
    edited Jul 13, 2020 12:13PM

    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.

    Erik van RoonOverlordBR
  • Erik van Roon
    Erik van Roon Member Posts: 60 Blue Ribbon
    edited Jul 13, 2020 1:48PM

    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.

    OverlordBR
  • Glen Conway
    Glen Conway Member Posts: 859 Gold Badge
    edited Jul 13, 2020 3:01PM
    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!

    Erik van RoonErik van Roon
  • Erik van Roon
    Erik van Roon Member Posts: 60 Blue Ribbon
    edited Jul 13, 2020 3:25PM

    Then I better start monitoring the sql dev space more too

  • peter_raganitsch
    peter_raganitsch Member Posts: 1,076 Silver Trophy
    edited Jul 22, 2020 5:08AM

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

    Tim St. H.OverlordBR
  • Tim St. H.
    Tim St. H. Member Posts: 99 Red Ribbon
    edited Sep 14, 2020 8:14AM

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

  • Erik van Roon
    Erik van Roon Member Posts: 60 Blue Ribbon
    Answer ✓

    Just to close this one:

    This issue that SQLcl produces extra lines output when using "format wrapped" with serveroutput is solved in version 21.1