How to get the output of an unnamed PL/SQL block in Oracle SQL Worksheet online? It's showing Unsupported Command for SET SERVEROUTPUT ON;

Question

I'm trying PL/SQL on online Oracle SQL Worksheet - Live Oracle SQL.

I'm unable to display the output of the block, in spite of adding SET SERVEROUTPUT ON;

This is my code

SET SERVEROUTPUT ON;

declare
    i number:=2;
    j number:=0;
    counter number:=0;
    flag number;
begin
    loop
        if (i=2) then
            counter:=counter+1;
            dbms_output.put(i ||' ');

        else
            j:=2;
            flag:=0;
            loop
                if(mod(i, j)=0) then
                    flag:=1;
                end if;
                exit when (i=j) or flag=1;
            end loop;
            if(flag=0) then
                counter:=counter+1;
                dbms_output.put(j ||' ');
            end if;
        end if;
    i:=i+1;
    exit when counter=10;
    end loop;
end;
/

This is the console message

Unsupported Command
Statement processed.

Any idea how to get it working?

Answer 1

I actually changed dbms_output.put() to dbms_output.put_line() and it worked. Any idea how to make dbms_output.put() work?

I want the output in a single line.

Answer 2

DBMS_OUTPUT will only output to the console when a full line of output has been produced. If you only use DBMS_OUTPUT.PUT and don't call DBMS_OUTPUT.PUT_LINE or DBMS_OUTPUT.NEW_LINE then the output will sit in a buffer somewhere but will never be written to the console.

From the Oracle [DBMS_OUTPUT.PUT] documentation:

Usage Notes:

• When you call PUT_LINE the item you specify is automatically followed by an end-of-line marker. If you make calls to PUT to build a line, then you must add your own end-of-line marker by calling NEW_LINE. GET_LINE and GET_LINES do not return lines that have not been terminated with a newline character.

You want to add DBMS_OUTPUT.NEW_LINE (or DBMS_OUTPUT.PUT_LINE( NULL )) at the end of your PL/SQL block.

A simplified version of your code (that only checks odd numbers) is:

DECLARE
  i       PLS_INTEGER :=1;
  j       PLS_INTEGER;
  counter PLS_INTEGER :=1;
BEGIN
  DBMS_OUTPUT.PUT('2 ');
  LOOP
    i:=i+2;
    j:=3;
    LOOP
      EXIT WHEN mod(i, j)=0;
      j:= j + 2;
    END LOOP;
    IF i = j THEN
      -- prime found
      DBMS_OUTPUT.PUT(i ||' ');
      counter:=counter+1;
      EXIT WHEN counter >= 10;
    END IF;
  END LOOP;
  DBMS_OUTPUT.NEW_LINE;
END;
/

Which outputs:

2 3 5 7 11 13 17 19 23 29 

Comment out that DBMS_OUTPUT.NEW_LINE; line and the procedure will not output anything as the buffer is never flushed to the console.

db<>fiddle here