1 2 Previous Next 18 Replies Latest reply on Apr 27, 2017 4:26 PM by Gary Graham-Oracle

    [4.2EA] extra newlines sometimes added to dbms_output

    B.Delmée

      i have noticed that the output of one of my scripts contains extra newlines when run under 4.2.

      This is a script that collects ALL_VIEWS.TEXT for a couple development schemas (600+ views) in a text file.

      It produces 64k+ lines, and is a valid (if huge) script with comments and "create view xxx as ..." statements.

      Under 4.2, the output is broken as an extra newline is sometimes added in the middle of a comment, introducing invalid statements.

      The broken lines are not necessarily the longest ones, either.

       

      i could not  produce a simplified test case so far,but perhaps some users have observed this behaviour, too ?

       

      On the bright side, i was using the output of that script to feed the formater,

      and had not noticed the extra newlines which caused failures to format (invalid) code.

        • 1. Re: [4.2EA] extra newlines sometimes added to dbms_output
          ThomasStrub

          Same issue here:

           

          declare

            v_out varchar2(1000) := '' ;

          begin

            dbms_output.put_line('');

            for i in 1..200 loop

                  for y in 1..20 loop

                     v_out:= v_out || lpad(y,5,' ');

                  end loop;

                  dbms_output.put_line(v_out);

               v_out := '';

             end loop;

          end;

           

           

              1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

              1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

              1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

              1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

              1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

              1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

              1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

              1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

              1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

              1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

              1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17

            18   19   20

              1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

              1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

              1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

              1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

              1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

              1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

              1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

              1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

              1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

           

          When repeating the script the issue appears on the same position.

          1 person found this helpful
          • 2. Re: [4.2EA] extra newlines sometimes added to dbms_output
            ThomasStrub

            better Testcase:

             

            declare

              v_out varchar2(1100) := '' ;

            begin

              dbms_output.put_line('');

              for i in 1..20 loop

                    for y in 1..1 loop

                       v_out:= v_out || lpad(y,1100,'0');

                    end loop;

                    dbms_output.put_line(v_out);

                 v_out := '';

               end loop;

            end;

             

            0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

            0001

            00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

            000000001

             

             

            I get a wrap after 1096 characters. Probably some issue with compatibility to sqlplus ...

            • 3. Re: [4.2EA] extra newlines sometimes added to dbms_output
              thatJeffSmith-Oracle

              did you check linesize?

               

              we dynamically set linesize based on the size of the editor window

              • 4. Re: [4.2EA] extra newlines sometimes added to dbms_output
                ThomasStrub

                SQL> show linesize;

                linesize 1097

                 

                That describes why there is a wrap at the given position.

                 

                But:

                set linesize 32767;

                declare

                  v_out varchar2(20000);

                begin

                  dbms_output.put_line('');

                  for i in 1..5 loop

                     v_out:= lpad(1,20000,'0');

                     dbms_output.put_line(v_out);

                  end loop;

                end;

                 

                4.1.3 -> 5 lines with 1 at the end.

                4.2.EA: 8 lines. 1 and 3 complete, 2,4,5 separeted.

                 

                I would expect that I get 5 full lines in 4.2.EA as well. So that the new linesize counter starts with every new line.

                 

                SQLPLUS:

                 

                SQL> declare

                  v_out varchar2(100);

                begin

                  dbms_output.enable;

                  dbms_output.put_line('');

                  for i in 1..5 loop

                     v_out:= lpad(1,100,'0');

                     dbms_output.put_line(v_out);

                  end loop;

                end;  2    3    4    5    6    7    8    9   10

                11  /

                00000000000000000000000000000000000000000000000000000000000000000000000000000000

                00000000000000000001

                00000000000000000000000000000000000000000000000000000000000000000000000000000000

                00000000000000000001

                00000000000000000000000000000000000000000000000000000000000000000000000000000000

                00000000000000000001

                00000000000000000000000000000000000000000000000000000000000000000000000000000000

                00000000000000000001

                00000000000000000000000000000000000000000000000000000000000000000000000000000000

                00000000000000000001

                 

                PL/SQL-Prozedur erfolgreich abgeschlossen.

                 

                SQL> show linesize;

                linesize 80

                ---

                SQL DEV 4.2.EA

                 

                SQL> set linesize 80;

                SQL> set echo on;

                SQL> show linesize;

                linesize 80

                SQL> declare

                  2    v_out varchar2(100);

                  3  begin

                  4    dbms_output.enable;

                  5    dbms_output.put_line('');

                  6    for i in 1..5 loop

                  7       v_out:= lpad(1,100,'0');

                  8       dbms_output.put_line(v_out);

                  9    end loop;

                10  end;

                11  /

                 

                0000000000000000000000000000000000000000000000000000000000000000000000000000000

                000000000000000000001

                000000000000000000000000000000000000000000000000000000000

                0000000000000000000000000000000000000000001

                00000000000000000000000000000000000

                00000000000000000000000000000000000000000000000000000000000000001

                0000000000000

                0000000000000000000000000000000000000000000000000000000000000000000000000000000

                00000001

                0000000000000000000000000000000000000000000000000000000000000000000000

                000000000000000000000000000001

                 

                 

                PL/SQL procedure successfully completed.

                 

                SQL> show linesize;

                linesize 80

                 

                (Bold to separate code and text.)

                 

                Regards

                Thomas

                • 5. Re: [4.2EA] extra newlines sometimes added to dbms_output
                  Jim 13131

                  I've got the same issue.

                   

                  SET SERVEROUTPUT ON;

                  DECLARE
                  j   NUMBER := 0;

                  BEGIN

                    WHILE j < 200 LOOP 
                      dbms_output.put_line('one two three four five six seven eight nine');
                         
                      j := j + 1;

                    END LOOP;

                  END;

                   

                  On 4.1.2 or 4.1.5 this produces:

                  one two three four five six seven eight nine

                  one two three four five six seven eight nine

                  one two three four five six seven eight nine

                   

                  But on 4.2.0 EA every 32 lines or so I get something like this:

                   

                  one two three four five six seven eight nine

                  one two

                  three four five six seven eight nine

                  one two three four five six seven eight nine

                   

                  This is messing up a script that has output formatted in a table.

                   

                  Jim

                  • 7. Re: [4.2EA] extra newlines sometimes added to dbms_output
                    Jim 13131

                    Thanks Jeff,

                     

                    I just confirmed the issue still exists in 4.20 EA2

                     

                    Jim

                    • 8. Re: [4.2EA] extra newlines sometimes added to dbms_output
                      Turloch O'Tierney-Oracle

                      Hi Jim,

                      Previous Bug fix in development.

                      Bug 25316690 - SET SERVEROUTPUT FORMAT WORD_WRAP NOT PRINTING CORRECTLY

                      \n (no\r) not recognised on windows.

                      --Turloch,

                      SQLDeveloper Team

                      • 9. Re: [4.2EA] extra newlines sometimes added to dbms_output
                        ThomasStrub

                        Seems to be fixed not.

                        Tested with the new Release 4.2.0.17.089

                        Test cases provide the intended output format.

                        • 10. Re: [4.2EA] extra newlines sometimes added to dbms_output
                          B.Delmée

                          Unfortunately i also still see the issue with 4.2-release indeed.

                          In my case, it seems to happen much less often, and my guess is only in case of very long input line.

                          My script basically (dbms_)outputs the all_views.text field for a certain selection of views

                          There are 650+ views, and i see the problem in 9 cases (was many more with the EAs)

                          This makes the dumped text invalid to be re-used as-is for a "create view" statements

                          because of the ill-placed newlines and despite the original views all being valid.

                           

                          Anyway just my feedback, thanks to Thomas for providing a reproducible test case.

                          • 11. Re: [4.2EA] extra newlines sometimes added to dbms_output
                            Jim 13131

                            Just a note that my test case above works fine in the release version 4.2.0.17.089 build 17.089.1709

                            • 12. Re: [4.2EA] extra newlines sometimes added to dbms_output
                              B.Delmée

                              here is one simplified test case that works under 4.1, fails under 4.2-release:

                              drop VIEW v_many_cols ;

                               

                              create view v_many_cols as

                              SELECT *

                              FROM x$kglob

                              WHERE ROWNUM <= 10

                              ;

                               

                              BEGIN

                                FOR l_rec IN (SELECT text FROM user_views WHERE view_name='V_MANY_COLS')

                                LOOP

                                  dbms_output.put_line(l_rec.text);

                                end loop;

                              END;

                              /

                              --> the output consists of 3 lines under 4.1, 5 under 4.2 (and it breaks one line in the middle of a field name, rendering the statement invalid)

                              Of course i imagine the same issue would occur with any view with many columns (and explains what i was seeing myself in my real-world report)

                              • 13. Re: [4.2EA] extra newlines sometimes added to dbms_output
                                thatJeffSmith-Oracle

                                Pretty sure the bug report included the one specific test case and not the other mentioned in this thread.

                                • 14. Re: [4.2EA] extra newlines sometimes added to dbms_output
                                  B.Delmée

                                  It is not entirely clear to me whether the other contributors to this thread consider their test case fixed.

                                  I am the OP and was not initially capable of posting a reasonable test case.

                                  I still see the same issue under 4.2-release, and it can be illustrated by the newly provided test case.

                                  1 2 Previous Next