dbms_output.put is not printing data

Question

I have a simple program in plsql. Basically the program has no errors but is not printing output when I use dbms_output.put.

declare
v_name varchar(30);
v_len number;
v_number number;
  begin
    v_name := '&name';
    v_number := length(v_name);
    while v_number > 0
    loop
      dbms_output.put(v_number||'::'||substr(v_name,v_number,1));
      v_number := v_number - 1;
    end loop;
  end;

The above program produces output when I use dbms_output.put_line but the output has new line after ever character and I would like to have the output in a single horizontal line. On using dbms_output.put it doesn't throw any error but it just gives me the message: "anonymous block completed"

Please let me know if am doing anything wrong.

Thanks, Dex.

brenners1302 · Accepted Answer · 2016-03-30T02:19:55.943

2

Try this: Just add dbms_output.put_line('') or dbms_output.new_line at the end of the loop.

declare
v_name varchar(30);
v_len number;
v_number number;
  BEGIN

    v_name := :name;
    v_number := length(v_name);
    while v_number > 0
    loop
      dbms_output.put(v_number||'::'||substr(v_name,v_number,1)|| ' ' );
      v_number := v_number - 1;
    END loop;

    dbms_output.new_line;
  end;

P.S. Please refrain from using '&' for substitution variable (&name) in pl/sql since its a feature supported by SQL*Plus. Instead, use bind variable like this :name

edited Mar 30 '16 at 02:19

answered Mar 30 '16 at 02:13

brenners1302

1,440
10
18

1

This will work, but isn't it better to just build a VARCHAR2 string in the loop, and place the whole string in the output buffer all at once, with a PUT_LINE call? – Mar 30 '16 at 03:10
1

thats a good suggestion and a better answer. But In my view, Im just giving him the fix for his code. If he had asked for a better solution or a suggestion, your answer should have been accepted if you have posted it. just saying. =) – brenners1302 Mar 30 '16 at 03:18

score 1 · Answer 2 · answered Mar 30 '16 at 02:17

DBMS_OUTPUT does not appear to flush the buffer until a new line is sent; you can either use DBMS_OUTPUT.PUT_LINE( NULL ) or DBMS_OUTPUT.NEW_LINE like this:

SET SERVEROUTPUT ON;
declare
v_name varchar(30);
v_len number;
v_number number;
begin
  v_name := '&name';
  v_number := length(v_name);
  while v_number > 0
  loop
    dbms_output.put(v_number||'::'||substr(v_name,v_number,1));
    v_number := v_number - 1;
  end loop;
  DBMS_OUTPUT.NEW_LINE;
end;
/

dbms_output.put is not printing data

2 Answers2

Linked