Toad World® Forums

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

This is an old “feature/quirk/bug” but I’m just now getting around to mentioning it.

begin

dbms_output.new_line;

dbms_output.put_line(null);

end;

I would expect that to produce 2 lines of output in the output window; but it doesn’t.

Is that an option I need to turn on or just something that Toad doesn’t support at all?

This works for any OS:

dbms_output.new_line(chr(9));

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.

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.

I’m not trying to get “null” to be returned in the output window. I’m trying to get the end-of-line delimiter to be returned.

When toad does it’s pulling loop, it will loop 3 times for my block, 2 to find my lines and 1 as “end”. Your block will loop 4 times. 3 to find your lines, and 1 as “end”

What gets displayed though is 0 lines for mine, 2 lines for yours.

Is it unreasonable that if I run your block I get something that looks like this?


a

b


the current form for your block


a

b


seems pretty obviously wrong. You called dbms_output.put_line 3 times, you only got 2 lines of output.

Going back to my previous example, instead of a hard coded set of calls, what about a cursor that loops through rows where some are null.

When my block is done I see 11 rows in my output.

Did my query return 11 rows? Maybe, it might have returned 12 rows, or 15 or 99 or 327 or 1294870 rows.
The output does not reflect the reality of what happens in my block.

I’m trying to get the end-of-line delimiter to be returned.

Then you need to produce a line of output. Null is not a line of output. Your original example shows one output line containing data and another line that is not a line. It is null. There is nothing there, no end of line delimiter, nada. Try your code in SQL*Plus.
What do you see? If your cursor is looping and returning null then your code should be written to show something in output to indicate null. Perhaps you can do something similar to the Toad data grid option to show {null} in place of null data. Using my example
there are three calls to dbms_output.put_line, but there are only 2 lines of output generated. The second call does not produce a line.

On 02/12/2014 10:11 AM, sdstuber wrote:

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

Reply by sdstuber
I’m not trying to get “null” to be returned in the output window. I’m trying to get the end-of-line delimiter to be returned.

When toad does it’s pulling loop, it will loop 3 times for my block, 2 to find my lines and 1 as “end”. Your block will loop 4 times. 3 to find your lines, and 1 as “end”

What gets displayed though is 0 lines for mine, 2 lines for yours.

Is it unreasonable that if I run your block I get something that looks like this?


a

b


the current form for your block


a

b

The second call does not produce a line.

I know what it does, or rather what it does not do. That’s my complaint.

I know what sql*plus does too. I don’t like the behavior there anymore than I do in toad.

I know “null” is not data. I don’t want you to print “null”, because, obviously, there’s nothing to print.

I know that null does not have an end-of-line, because it’s just null.

BUT… when Toad prints “a” to it’s output window, whatever code you use that does that also writes a new line. If it didn’t then we’d see “ab” instead of

"a

b"

THAT newline, the one that Toad adds to the output in order to indicate “one line from the buffer is done” is the one I’m looking for.

your code should be written to show something in output to indicate null

That’s what I do now, but really, that’s pretty silly. Toad already has my line, but for whatever reason it’s not displaying it.

Read those words “should be written to show something” - So, in order to show “nothing”, I have to create “something” ?

I don’t want a tab or a space or bell or SOH or any other invisible character. I already have to do that everytime I run into this problem.

I simply want a blank line.

Ironically, I can’t even try to create my own blank line.

If I dbms_output.put_line(chr(10)); I get 2 blank lines. One, my “fake one” and then another one that Toad inserts.

Toad pulls a line from the dbms_output buffer in each of the examples where you say there is nothing. It’s not nothing. It’s an actual element in the buffer array. That element happens to be a null, but it’s just as real and valid as “a”, “b” or any other varchar2 value.

Check the GET_LINE counts in Toad. If you do 3 dbms_output.put_lines, then you’ll get 3 lines. Whether there is actually any data or NULL in those lines is irrelevant. Just print what you get. If I output “a”, Toad actually prints “a\n” in the output window (where \n represents a newline). Put_line “b” becomes “b\n”. So, when the buffer has “” in it, then simply do the same thing: print “\n” maybe the \n is prepended before writing. I don’t know, but whatever the mechanism is that puts “b” below “a”, do the exact same thing for null.

All I’m trying to get is one line of output for each line in the buffer.

I didn’t invent blank lines, I’m simply trying to use them.

If that is unreasonable, can someone explain why?

Again, I understand the current functionality mimics sqlplus; I’m asking for Toad to be “better” than sqlplus, or at least provide the option to be so.

Thanks

I simply want a blank line.

Then post a new idea in the idea pond.
http://www.toadworld.com/products/toad-for-oracle/i/default.aspx

We can do that, sure. There’s specifically code in there to ignore lines that have no output with code comments noting the behavior of SQLPlus. In some cases it’s nice to have consistency with SQLPlus and other tools with respect to results of SQL and PL/SQL
execution. This may or may not be one of them. It would not be unreasonable for a developer to write a custom debugging tool that parses debug output to generate a report. In that case it would be nice if the output were consistent regardless of which tool
(Toad, SQL*Plus, etc.) they’ve generated it from. I’m sure this request has come up before, but I don’t recall it. Post the idea and gather some support.

On 02/12/2014 11:17 AM, sdstuber wrote:

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

Reply by sdstuber

The second call does not produce a line.

I know what it does, or rather what it does not do. That’s my complaint.

I know what sql*plus does too. I don’t like the behavior there anymore than I do in toad.

I know “null” is not data. I don’t want you to print “null”, because, obviously, there’s nothing to print.

I know that null does not have an end-of-line, because it’s just null.

BUT… when Toad prints “a” to it’s output window, whatever code you use that does that also writes a new line. If it didn’t then we’d see “ab” instead of

"a

b"

THAT newline, the one that Toad adds to the output in order to indicate “one line from the buffer is done” is the one I’m looking for.

your code should be written to show something in output to indicate null

That’s what I do now, but really, that’s pretty silly. Toad already has my line, but for whatever reason it’s not displaying it.

Read those words “should be written to show something” - So, in order to show “nothing”, I have to create “something” ?

I don’t want a tab or a space or bell or SOH or any other invisible character. I already have to do that everytime I run into this problem.

I simply want a blank line.

Ironically, I can’t even try to create my own blank line.

If I dbms_output.put_line(chr(10)); I get 2 blank lines. One, my “fake one” and then another one that Toad inserts.

Toad pulls a line from the dbms_output buffer in each of the examples where you say there is nothing. It’s not nothing. It’s an actual element in the buffer array. That element happens to be a null, but it’s just as real and valid
as “a”, “b” or any other varchar2 value.

Check the GET_LINE counts in Toad. If you do 3 dbms_output.put_lines, then you’ll get 3 lines. Whether there is actually any data or NULL in those lines is irrelevant. Just print what you get. If I output “a”, Toad actually prints
“a\n” in the output window (where \n represents a newline). Put_line “b” becomes “b\n”. So, when the buffer has “” in it, then simply do the same thing: print “\n” maybe the \n is prepended before writing. I don’t know, but whatever the mechanism is
that puts “b” below “a”, do the exact same thing for null.

All I’m trying to get is one line of output for each line in the buffer.

I didn’t invent blank lines, I’m simply trying to use them.

If that is unreasonable, can someone explain why?

Again, I understand the current functionality mimics sqlplus; I’m asking for Toad to be “better” than sqlplus, or at least provide the option to be so.

Thanks

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.

There’s specifically code in there to ignore lines that have no output.

I figured there had to be. Thanks for looking.

I forgot about the idea pond. Even though the old email list hasn’t been around for a while, I still think about the toad community as if it were.

I’ve submitted my request.

Thanks again.

dbms_output.put_line(chr(10));

this will makes two lines in sqlplus as well.

Do try all commands in sqplus so you can see when Toad is “coloring” something or it is core sqlplus behaviour

[I]

I’m asking for Toad to be “better” than sql*plus, or at least provide the option to be so.

I frankly do not want that toad is doing Oracle commands differently that original Oracle Sqlplus is.

@ miller_stephen

Now I see your point.

According your example I can show toad output (all three cases):

a

b

PL/SQL procedure successfully completed.

a

b

PL/SQL procedure successfully completed.

a

b

PL/SQL procedure successfully completed.

and sqlplus output (all three cases):

SQL> set serveroutput on size 10000;

SQL>

SQL> exec dbmsoutput_test;

a

b

PL/SQL procedure successfully completed.

SQL>

SQL> set serveroutput on format tru ;

SQL>

SQL> exec dbmsoutput_test;

a

b

PL/SQL procedure successfully completed.

SQL>

SQL> set serveroutput on format wor;

SQL>

SQL> exec dbmsoutput_test;

a

b

PL/SQL procedure successfully completed.

SQL>

So seems that Toad is “coloring” output-what I find not OK.

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

I have quite opposite results (Toad 12.1)

submitted a new question and discovered I hadn’t ever replied to the latest comments on my previous question.

I frankly do not want that toad is doing Oracle commands differently than original Oracle Sqlplus is.

Most of the time I don’t either; however, since SQL*Plus has a fairly obvious hole in its functionality what I’m asking for is an option/checkbox/radio button/whatever (it can default to off) to have the Toad dbms_output window reflect the buffer as I populated it.

That is, if I submit n lines, I get n lines.

Unfortunately, I can’t find my submission to the idea pond, I don’t know how much traction it got.

Here’s your idea pond item.

http://www.toadworld.com/products/toad-for-oracle/i/editor/allow_blank_lines_generated_b_dbms_output_to_be_displayed_as_blank_lines_in_the_output_window.aspx

You can view previous activity on ToadWorld.com. Click on your username and there’s a link there for recent activity.

Michael

On 06/17/2014 10:02 AM, sdstuber wrote:

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

Reply by sdstuber
submitted a new question and discovered I hadn’t ever replied to the latest comments on my previous question.

I frankly do not want that toad is doing Oracle commands differently than original Oracle Sqlplus is.

Most of the time I don’t either; however, since SQL*Plus has a fairly obvious hole in its functionality what I’m asking for is an option/checkbox/radio button/whatever (it can default to off) to have the Toad dbms_output window reflect the
buffer as I populated it.

That is, if I submit n lines, I get n lines.

Unfortunately, I can’t find my submission to the idea pond, I don’t know how much traction it got.

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.