1

I have a trivial question, I yet to understand the difference between dbms_output.put_line() and dbms_output.put()

set serveroutput on size 200000
Begin
dbms_output.put_line('A' || CHR(10) || 'B');
End;
/

exec dbms_output.put_line('A' || CHR(10) || 'B');

The above gives output A and B in two different lines. But 

exec dbms_output.put('A')
exec dbms_output.put('B')
exec dbms_output.new_line

prints nothing. I am using SQL*Plus: Release 11.2.0.1.0 Production

Ibrahim Quraish
  • 3,889
  • 2
  • 31
  • 39

2 Answers2

5

You always have to write full lines in each PL/SQL block. Wehn you write

Begin
  dbms_output.put_line('A' || CHR(10) || 'B');
End;
/

You write the two full lines "A" and "B" in one PL/SQL block. When you write 

exec dbms_output.put_line('A' || CHR(10) || 'B');

the same holds true.

But when you write 

exec dbms_output.put('A')
exec dbms_output.put('B')
exec dbms_output.new_line

you have three separate PL/SQL blocks. The first two write partial lines, which will be skipped by SqlPlus. The third block writes a full, but empty line.

If you write

begin
  dbms_output.put('A');
 dbms_output.put('B');
 dbms_output.new_line;
end;
/

everything works as expected.

The reason for this is how console output in SqlPlus works: PL/SQL writes the output in an intermediate buffer. SqlPlus fetches the content of this buffer at the end of each executed PL/SQL block. It then prints that content to the console, while only printing full lines. Hence it skips the last line in the buffer, when it is not terminated by a new line character.

Technically I suspect SqlPlus to also print lines without a new line character, but starightaway overwrite them in the next step. Technically SqlPlus indeed fetches only full lines from the internal buffer, using the DBMS_OUTPUT.GET_LINES PL/SQL function.

stefan.schwetschke
  • 8,862
  • 1
  • 26
  • 30
0

Assuming you are on 11g, you should be considering PUT procedure since it is Obsolete and no longer supported by Oracle. In previous versions, with PUT, the line is not yet completed. Since it doesn't have end of line marker unlike PUT_LINE which automatically is followed by an end-of-line marker.

So, if you add an end-of-line marker, you will get the output.

You can have a look at this old question Is dbms_output.put() being buffered differently from dbms_output.put_line()?

Community
  • 1
  • 1
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • Yes, of course it works. That's what I meant, PUT needs a end-of-line marker. and then NEW_LINE to let the buffered output printed. – Lalit Kumar B Sep 08 '14 at 10:54
  • From the docs: "The `PUT` procedure that takes a `NUMBER` is obsolete", not the version the OP is using. The same is true for `PUT_LINE`. – Alex Poole Sep 08 '14 at 13:20
  • Ok, I saw that in docs. Dan Morgan made a statement in his site http://psoug.org/reference/dbms_output.html – Lalit Kumar B Sep 08 '14 at 13:27