Re: Is there anyway to get blank lines to display in the dbms output window?

Toad for Oracle
1

Of course this example is what happens in SQL*PLUS.

But it looks like TOAD is not quite emulating SQL_PLUS (at least when it comes to SERVEROUTPUT settings).

I've tested whether an EOL sequence is transmitted for NULL / empty lines (using DBMS_OUTPUT.GET_LINE to retrieve the lines), and it definitely is sent for every occurrence of DBMS_OUTPUT.PUT_LINE and DBMS_OUTPUT.NEW_LINE - so I can only assume that TOAD is throwing away a line that consists of just an EOL sequence.

SERVEROUTPUT in TOAD also does not respect whatever LINESIZE has been set either (at least in the Script Output tab).

All of this is assumed to have been executed using F5. If you use "Execute SQL via SQL*Plus", it works as expected.

Stephen Miller - email: miller_stephen at usa.net

------ Original Message ------

Received: 03:34 PM EST, 02/12/2014

From: "miller stephen" bounce-miller_stephen@toadworld.com

To: toadoracle@toadworld.com

Subject: Re: [Toad for Oracle - Discussion Forum] Is there anyway to get blank lines to display in the dbms output window?

Re: Is there anyway to get blank lines to display in the dbms output window?

Thread created by miller_stephen
By default, if you just execute SET SERVEROUTPUT ON, it defaults to FORMAT WORD_WRAPPED. That setting suppresses all blank lines.

Issue SET SERVEROUTPUT ON FORMAT TRUNCATE or SET SERVEROUTPUT ON FORMAT WRAPPED and you will get what you want:

** SQL> set serveroutput on

          SQL> create or replace procedure dbmsoutput_test as

            2  begin

            3      dbms_output.put_line('a');

            4      dbms_output.put_line(null);

            5      dbms_output.put_line('b');

            6  end;

7 /**

Procedure created.

** SQL>

          SQL> exec dbmsoutput_test;

          a

b**

PL/SQL procedure successfully completed.

** SQL>

          SQL>

          SQL> set serveroutput on tru

          Usage: SET SERVEROUTPUT { ON | OFF } [SIZE {n | UNL[IMITED]}]

                       [ FOR[MAT] { WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED] } ]

          SQL> set serveroutput on format tru

          SQL> exec dbmsoutput_test;

          a

b**

PL/SQL procedure successfully completed.

** SQL> set serveroutput on format wra

          SQL> exec dbmsoutput_test;

          a

b**

PL/SQL procedure successfully completed.

** SQL> set serveroutput on format wor

          SQL> exec dbmsoutput_test;

          a

b**

PL/SQL procedure successfully completed.

** SQL>
**

Stephen Miller - email: miller_stephen at usa.net

          ------ Original Message ------

Received: 09:52 AM EST, 02/12/2014

From: "Michael Staszewski" bounce-mstaszew@toadworld.com

To: toadoracle@toadworld.com

Subject: Re: [Toad for Oracle - Discussion Forum] Is there anyway to get blank lines to display in the dbms output window?

Re: Is there anyway to get blank lines to display in the dbms_output window?

Reply by Michael Staszewski
Null is not a character. There is nothing to display when you write that to output.

                  Try this in SQL*Plus and you'll see the same.



                  create or replace procedure dbmsoutput_test as

                  begin

                      dbms_output.put_line('a');

                      dbms_output.put_line(null);

                      dbms_output.put_line('b');

                  end;

                  /



                  exec dbmsoutput_test;





                    On 02/12/2014 01:28 AM, sdstuber wrote:

RE: Is there anyway to get blank lines to display in the dbms_output window?

Reply by sdstuber
I know I can stick "invisible" dummy values but what I'm looking for is output that reflects what my code actually did.

If I run the block above it seems reasonable that I would get 2 lines consisting of nothing but the end-of-line delimiter but I don't.

Let's make it a little more real. Let's say I'm looping through a cursor of a single column and outputing the values. Some rows have null.

If my cursor iterates through 15 rows with 4 nulls I'll only get 11 rows of output even though the code called put_line 15 times.

                        To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

                      No virus found in this message.

Checked by AVG - www.avg.com

                      Version: 2014.0.4259 / Virus Database: 3684/7045 - Release Date: 01/30/14

Internal Virus Database is out of date.

                  To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

      To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.