26

I am using SQL Developer and want to output the contents of a variable to the console using DBMS_OUTPUT.PUT_LINE(). I am running the following code that adds the numbers 1 through 5 inclusive but I'm not seeing any output. 

SET SERVEROUTPUT ON;
DECLARE 
n_counter NUMBER := 5; -- Substitute this variable
n_sum     NUMBER := 0;
BEGIN
  WHILE n_counter != 0
  LOOP
    n_sum := n_sum + n_counter;
    n_counter := n_counter -1;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(n_sum);
END;

Additionally, do you Know of better resources for troubleshooting issues than the incredibly dense Oracle PL/SQL documentation? [similar to Java SE7 API?]

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Do you reach the end of this routine? I.e. it's not an endless loop somehow, right? Doesn't appear to be. – Marc Apr 14 '12 at 01:14
  • Yeah, the routine completes successfully. –  Apr 14 '12 at 01:20
  • Can try with basic Loop instead of while because there seems to be no problem as such to get the output because ServerOutput is ON the only reason might be due to While Loop – Himanshu Nov 13 '18 at 20:13

1 Answers1

49

Since you are using SQL Developer, you have a couple of options.

In SQL Developer, go to View | DBMS Output to ensure that the DBMS Output window is visible. In the DBMS Output window, choose the "plus" icon and select the connection that you want to write data to the DBMS Output window. Then run the PL/SQL block in the SQL Worksheet window using the right arrow (Ctrl+Enter in Windows). You'll see the output appear in the DBMS Output window.

Alternately, you can put both the SQL*Plus SET SERVEROUTPUT ON command and the PL/SQL block in the SQL Worksheet and run it as a script (F5 in Windows). That will display the output immediately below the "anonymous block completed" message in the Script Output window.

Note: Dbms Output in Oracle Sql Developer doesn't show null in the output window. It moves to a new line, but until it will return something else than null, you'll not know all the previous nulls are there.

m.jerwan
  • 13
  • 4
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • I'm using SQL Developer, and receive the following notification: Anonymous block completed. The goal is to add the values 1-5 inclusive. –  Apr 14 '12 at 01:16
  • Does this work only when debugging? currently my debbuger does not work, i'm still trying to run pl/sql by callign it from a sql file – Zeus Jan 29 '15 at 19:48
  • @Zeus - `dbms_output` is separate from the debugger. The debugger lets you actually step through code as it executes. `dbms_output` is much more primitive and just lets you write code to output bits of information. – Justin Cave Jan 29 '15 at 19:49
  • Thanks for answering. What is the best way to run a function in a package and let it print in the dbms output? also could you please take a look at the question http://stackoverflow.com/questions/28222952/sqldeveloper-does-not-start-in-debug-mode here – Zeus Jan 29 '15 at 19:54