1

Since our company upgraded our version of Oracle SQL Developer, I've been noticing issues when outputting data to the DBMS_OUTPUT using DBMS_OUTPUT.PUT_LINE.

We use this function and combine it with the text-to-columns feature in Excel to quickly generate reports. However, I've found that since the update, text is forcing a line break in the DBMS_OUTPUT (seemingly) randomly. This is detrimental to the report generating as it relies on perfect alignment with a spread sheet template.

I'm uncertain what is causing this line break but to demonstrate I have supplied a messy but hopefully useful example below.

If I run the below statement 22 times as an example:

begin
DBMS_OUTPUT.PUT_LINE('111111111111111111111111111111111111111111111111111111111111111111111111111');
end;

We would expect the output to return 75 1's in a row, 22 times. However, this is not the case. On lines 11 and 12, the following is returned:

11
1111111111111111111111111111111111111111111111111111111111111111111111111

(the above displays 2 1's on line 11 and 73 on line 12)

again on lines 22 and 23

111
111111111111111111111111111111111111111111111111111111111111111111111111

(the above displays 3 1's on line 11 and 72 on line 12)

As you can see, the line is breaking at line 11 and line 22.

It should be noted that all other lines are formatted correctly, it is only the text on lines 11 and 22 that is wrapping/breaking

Of course this is not a valid real world example but I thought it was the easiest way of explaining it.

Does anybody has any ideas as to why the lines are wrapping and how I can fix it?

TomB
  • 255
  • 2
  • 5
  • 15

1 Answers1

3

The version you are using, 4.2.0.16, is the second early-adopter release, 4.2EA2. This behaviour was reported as a bug, and is fixed in the 4.2 release version (4.2.0.17).

I can replicate in 4.2.0.16, but not in 4.2.0.17; although even in EA2 I see the extra breaks in a different place to you. You can modify where they appear by changing the session linesize. For the exact behaviour you see, you appear have your linesize set to 762.

With the maximum of 32767 set I don't see the issue until the 432nd line, which supports is being related to linesize/string length (plus one for the newlines - 32767/76 = 431.144, so you get 431 complete lines and 11 on the next line before the extra unwanted linebreak); that may still not be good enough for your real scenario of course.

Upgrading would be a more reobust approach than trying to work around it. You can get the current release version from the download site.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • 1
    Thank you, setting the linesize before running the query worked perfectly. I think I may get away with this in the short term but I'll try and get my SQL Developer updated for the long term – TomB Jun 21 '17 at 11:40