Enabling DBMS_OUTPUT by default in SQL Developer

thatjeffsmith SQL Developer 47 Comments

Tell Others About This Story:

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.


So try this. In your script use


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


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!

Related Posts

Tell Others About This Story:

Comments 47

  1. 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.

    nothing is working.
    Please help

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

    3. thatjeffsmith Post
    4. Same problema here but with sqldeveloper versiรณn is “Oracle IDE”.

      Seems that Oracle doesn’t care about Backward compatibility.

    5. thatjeffsmith Post

      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…

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

    set serveroutput on;
    n number;
    select count(*) into n from dual;

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

    PL/SQL procedure successfully completed.

    1. thatjeffsmith Post

      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.

  3. 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
    “DBMS output is now disabled, dba has been notified that something weird is going on”

    1. thatjeffsmith Post
    2. 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…

    3. thatjeffsmith Post

      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.

    4. 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)

    5. thatjeffsmith Post

      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.

    6. 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…?

    7. thatjeffsmith Post

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

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


      * – 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.

    1. thatjeffsmith Post
  4. 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?

    1. thatjeffsmith Post
  5. Hi Jeff,
    i am getting the below error while trying to execute PL/SQL script in sql developer version

    Error starting at line : 1 in command –
    a number :=5;
    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.

    i am executing in the below env.
    hr [email protected]//localhost:1521/orcl.

    How to fix this?

  6. 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.

  7. 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 –
    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

    1. thatjeffsmith Post
    2. 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.

    3. (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;

      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?


    4. thatjeffsmith Post
  8. 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.

    1. thatjeffsmith Post

      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

  9. 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. ๐Ÿ™‚

    1. thatjeffsmith Post
  10. 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).

    1. 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

  11. I just tried this in SQL Developer 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 ?

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

    1. thatjeffsmith Post
    1. thatjeffsmith Post

Leave a Reply

Your email address will not be published. Required fields are marked *