DBMS_OUTPUT is one of the most well-known and least understood SYS packages. If you have questions about how it works in general, you might want to read my previous post on the subject.

What I want to show you today is a ‘trick’ that will help you bypass the step of enabling polling for DBMS_OUTPUT for all of your connections.

I don’t want to have to click this button every single time I want to see my stuff!

I’m all about saving clicks, so let’s save you 3 clicks right now.

Instead of clicking the ‘Enable’ then selecting the connection then hitting ‘OK,’ we’re going to enable capture of the output be DEFAULT in SQL Developer.

We support SET SERVEROUTPUT

So try this. In your script use

SET SERVEROUTPUT ON
 
BEGIN
 Dbms_Output.Put_Line(Systimestamp);
END;
/

Now run the script using F5.

Let’s check the DBMS_OUTPUT panel.

Jeff, you lied to us!

Instead of checking the DBMS_OUTPUT panel, check the ‘Script Output’ page.

SET SERVEROUTPUT ON directs DBMS_OUTPUT to the Script Output Panel

But how do I get this to work by default?

  • Open a new worksheet.
  • Code this line
  • SET SERVEROUTPUT ON
  • Save to ‘startup.sql’
  • Open Tools – Preferences
  • Go to the Database page
  • On the ‘Filename for connection startup script’ – point to the ‘startup.sql’ file you just created.
  • Restart SQL Developer.
  • Open a connection and run your code.

You’ll see your DBMS_OUTPUT code with no more work!

An ad hoc example –

If the serveroutput is defined in your login script, this will be on for every Oracle session in SQL Developer.
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

59 Comments

  1. Leandro Martins de Lima Reply

    7 years old post, but this still requires manual activation. Why? Is there any side effect having SQL Dev automatically setting the output on for all connetions?
    If it’s not recommended, maybe adding an on/off option for such in the preferences? Easier to find, easier to activate (or deactivate, for whatever reasons one may have).

    Could we have it in the next release, mr. Jeff?

    • Not a bad question to be honest, why not always be polling for output. Consider it, ‘on the road map.’

  2. I am using sql dev 18.1 with Oracle 10g.
    I am not getting DBMS_OUTPUT.put_line in output.
    I have tried all suggestion.
    View DBMS_OUTPUT set to connected schema.
    SET SERVEROUTPUT ON;

    nothing is working.
    Please help

    • >>with Oracle 10g
      That’s your problem.

      Either upgrade your database (everyone wins), or downgrade your SQLDev (you win but mostly lose).

    • SANKALP RAI

      I am using oracle 10g with sql dev Version 17.2.0.188
      still i am not getting any output.
      please help
      even i used your method and also set serveroutput on still no output

    • It’s because your database is 10g. You’ll need to either use an older version of SQLDev or upgrade your database to as least 11.2.0.4

    • Same problema here but with sqldeveloper versiรณn is “Oracle IDE 17.2.0.188.1159”.

      Seems that Oracle doesn’t care about Backward compatibility.

    • Sure we care. But when a database goes out of support, we no longer run test cases for it. If your version of Oracle is 10 or 15 years behind, you’ve got bigger problems then dbms output not working…

    • Yes, you spend 1-5,000 dollars on a copy of Toad to help your old DB limp along…for a single db user.

      Or, you could spend some time and money on upgrading your database to 18c. Then everyone who uses the DB benefits.

  3. Hi, I set the path to startup.sql and i ran my plsql program

    set serveroutput on;
    declare
    n number;
    begin
    select count(*) into n from dual;
    dbms_output.put_line(n);
    end;
    /

    Still i was unable to see the output. I am getting only this in my script output.

    PL/SQL procedure successfully completed.

    • It’s login.sql,not startup.sql

      Use this to see if it’s set

      Show serveroutput,run with F5. Might want to try restarting the application too, some folks have seen ‘weird’ things happen.

  4. annoyed Dev Reply

    I have a question: WHY THE (bad words) IS THIS DISABLED BY DEFAULT?!?!? What’s wrong with people?
    Server output should be enabled by default, always and everywhere; to disable the command should be: “exec dbms_output.disable_server_output(I_AM_SURE=>1);” And Oracle’s response to this command could be one of two options:
    ORA-xxxxx: User requested disable of dbms_output; ignoring request
    or
    “DBMS output is now disabled, dba has been notified that something weird is going on”

    • DBMS_OUTPUT is something used for diagnostics, it’s generally never used for applications.

    • (less) annoyed Dev

      Wow, quick response ๐Ÿ™‚
      That’s exactly why I think it should always be enabled – production applications shouldn’t be generating dbms output at all – if we’re generating, it’s because we want it…

    • Having it on by default would encourage folks to use it, and everything has a cost.

      This decision was probably made 30 years ago. I can guess why it’s so today, but I’m thinking my guess is pretty close.

    • (less) annoyed Dev

      I guess you’re right…
      Anyway, your post resolved my problem; thanks!
      And thanks for the attention; your blog is awesome! ๐Ÿ™‚
      (and I’m getting used to SQL Developer – coming from SQL Navigator here; I miss the possibility to drag a column name into the script :P)

    • We support that too. Just expand the table in the tree to the column list, pick the column(s) you want, and drag and drop to where you want it place in your script.

      If you get a popup, pick ‘Object Name.’

      We even observe the click order.

    • Observing the click order on that is a real nice touch! ๐Ÿ™‚
      That’s not what I meant though; I work with PeopleSoft DB’s – they have over 30 thousand tables so I don’t usually open the table list in the IDE, too many to scroll through; I meant dragging the column names from the result sets (ex. select * from table, then drag the columns that I actually want).
      In SQL Developer I right-click and copy the column headers to the same effect; the two caveats are that it takes longer than just “drag, drag, drag” and that I lose my clipboard contents…
      I do really like the way you handled dragging from the definitions list! I will surely use this more often, thanks! One quick question; if I uncheck the “ask me every time” option, will I be stuck with my choice forever or until restart or…?

    • You’ll be stuck until you go into the preferences and change the behavior.

      For your column drive, let’s say you currently have:

      SELECT * FROM SALES:

      * – no bueno

      Put your cursor on SALES, hit SHIFT+F4

      This gives you the desc popup. on the columns page, select the columns you want from the column_name column in the report, then drag and drop to the worksheet. That will copy and paste the column list in a comma separated values list.

  5. Hi

    I am new to oracle.
    As I type select statement in worksheet and execute my query I am seeing table but nothing is align in that.
    Can you please tell me where I am wrong?
    Thanks.

  6. I had to exit out of and then get back into sqldeveloper (I’m on a Windows 10 box).

  7. Hi Jeff,
    i am getting the below error while trying to execute PL/SQL script in sql developer version 4.0.3.16.

    Error starting at line : 1 in command –
    DECLARE
    a number :=5;
    BEGIN
    DBMS_OUTPUT_PUT_LINE(a);
    END;
    Error report –
    ORA-06550: line 4, column 1:
    PLS-00201: identifier ‘DBMS_OUTPUT_PUT_LINE’ must be declared
    ORA-06550: line 4, column 1:
    PL/SQL: Statement ignored
    06550. 00000 – “line %s, column %s:\n%s”
    *Cause: Usually a PL/SQL compilation error.
    *Action:

    i am executing in the below env.
    hr hr@//localhost:1521/orcl.

    How to fix this?

    • Abhishek

      DBMS_OUTPUT_PUT_LINE is wrong syntax…correct syntax is DBMS_OUTPUT.PUT_LINE …

  8. PLS-00201: identifier ‘DBMS_OUTPUT_PUT_LINE’ must be declared
    ORA-06550: line 4, column 1:
    PL/SQL: Statement ignored
    06550. 00000 – “line %s, column %s:\n%s”
    *Cause: Usually a PL/SQL compilation error.
    *Action:

  9. I added the line SQL> SET SERVEROUTPUT ON to my script at the top and tried running the script with it included. I got the following error:
    Error starting at line : 1 in command –
    SQL> SET SERVEROUTPUT ON
    Error report –
    Unknown Command

    The above error is followed by: PL/SQL procedure successfully completed.
    So, I believe the script is running successfully.
    Any help would be appreciated.
    –Thank you

    • I found out where I went wrong. It is kind of a silly mistake.
      I have been giving the command:
      SQL> SET SERVEROUTPUT ON with ‘SQL>’ included, which led to the error.

      Thank you for your swift response.

    • Ron Nolan

      (I’m a new DBA)
      I set up STARTUP.SQL and saved it and restarted SQL Developer. (ver 1.5.5. Build MAIN-5969)

      3 lines are in my code window:
      CLEAR Screen
      SELECT table_name from dba_tables;
      DBMS_OUTPUT.PUT_LINE(‘Test1’);

      In the Script Output tab, the data from the select comes down fine, but for my DBMS_OUTPUT line I get “Error Report–unknown command”

      What am I still doing wrong to warrant the error?

      Thanks,

    • you can’t run PL/SQL like that

      you need

      begin
      dbms_output.put_line(‘hello world’);
      end;
      /

  10. My SERVEROUTPUT setting in the startup script was not working. I had it in lower case and with trailing line comment:

    `set serveroutput on — Controls whether to display the output (that is, DBMS_OUTPUT PUT_LINE) of stored procedures or PL/SQL blocks`

    Changing to `SET SERVEROUTPUT ON` fixed the issue.

    • what version?

      I just ran
      set serveroutput on

      and it worked A-OK…maybe the comment is what was the issue?

      what version are you running?

      you can use SHOW ALL to see what the settings are after you’re connected

  11. Larry Hopper Reply

    Thanks for the tip, Jeff.

    In paragraph 4, I am pretty sure you mean “Enable”, with 1 B, not “Enbable” with 2 Bs. Thanks for the chuckle, though. ๐Ÿ™‚

  12. Nothing happens. More specifically, Script Output says “Task completed in 0.016 seconds,” but there is nothing in either the Script Output window or the Dbms Output window (Dbms Output window has a tab with the conneciton name in it, but nothing in the content pane for that tab).

    • Click on ‘+’ Sign in Database output window and again select the database connection name that you are running the script in. It worked for me. try it

  13. I just tried this in SQL Developer 4.1.0.17 Build MAIN-17.29 and after restarting the Query Results / Script Results windows stopped to show after running any SQL code.
    I even deleted all settings that were in my AppData/Roaming folder, repeated it on a “clean” app and still the situation persists. Is this a bug of some sort ?

  14. Hi,
    When try to use sys.Dbms_Output.Put_Line(Systimestamp);
    I got an error message
    Error starting at line 1 in command:
    sys.Dbms_Output.Put_Line(Systimestamp)
    Error report:
    Unknown Command

  15. This only seems to work the first time you run a script which makes sense since it is only run once at startup

    • Hmmm, the session is shared, so that setting should persist throughout the connection. Let me go take a look and get back to you.

Reply To Lavanya Cancel Reply